Db2

 View Only

  • 1.  ADMIN_REVALIDATE_DB_OBJECTS inconsistent with SYSCAT.INVALIDOBJECTS

    Posted Thu April 08, 2021 09:46 AM

    Hi,

    I find much regrettable that SYSCAT.INVALIDOBJECTS has 'F' for objectype, meaning "routine", when ADMIN_REVALIDATE_DB_OBJECTS has object_type 'Function' or 'Procedure'.

    Can't you be simple and support both 'F' and 'P' so that we would logically call in scripts ADMIN_REVALIDATE_DB_OBJECTS with correct object_type, or support 'Routine' in this procedure ?

    Best regards, JMB

     

    db2 -tvf testinvalid

    create table jmb (f1 int)
    DB20000I  The SQL command completed successfully.

    create procedure pjmb
    begin
      delete from jmb where f1=1;--
    end
    DB20000I  The SQL command completed successfully.

     

    drop table jmb
    DB20000I  The SQL command completed successfully.

    select objectname, objecttype, routinename from syscat.invalidobjects where routinename='PJMB'

    OBJECTNAME                                                                                                                       OBJECTTYPE ROUTINENAME                                                                                      
    -------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
    SQL171024092809939                                                                                                               F          PJMB                                                                                             

      1 record(s) selected.


    call pjmb
    SQL0727N  An error occurred during implicit system action type "3".
    Information returned for the error includes SQLCODE "-204", SQLSTATE "42704"
    and message tokens "PPSSYS.JMB".  LINE NUMBER=3.  SQLSTATE=56098

    select objectname, objecttype, routinename from syscat.invalidobjects where routinename='PJMB'

    OBJECTNAME                                                                                                                       OBJECTTYPE ROUTINENAME                                                                                      
    -------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
    SQL171024092809939                                                                                                               F          PJMB                                                                                             

      1 record(s) selected.


    CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('function', 'PPSSYS', NULL)

      Return Status = 0

    CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('procedure', 'PPSSYS', NULL)
    SQL20482N  Revalidation failed for all objects that were specified to be
    revalidated. One object, "PPSSYS  .SQL171024092809939", could not be
    revalidated because it references object "PPSSYS.JMB".  SQLSTATE=429C4

    select objectname, objecttype, routinename from syscat.invalidobjects where routinename='PJMB'

    OBJECTNAME                                                                                                                       OBJECTTYPE ROUTINENAME                                                                                      
    -------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
    SQL171024092809939                                                                                                               F          PJMB                                                                                             

      1 record(s) selected.


    #Db2


  • 2.  RE: ADMIN_REVALIDATE_DB_OBJECTS inconsistent with SYSCAT.INVALIDOBJECTS

    Posted Mon March 30, 2020 01:18 PM
    No comment on this one ?
    Original Message


  • 3.  RE: ADMIN_REVALIDATE_DB_OBJECTS inconsistent with SYSCAT.INVALIDOBJECTS

    Posted Thu April 08, 2021 09:46 AM

    Hi Jean-Marc,

     

    I looked into this and I believe it is the unfortunate result of multiple object types being mapped onto the same representative value in SYSCAT.INVALIDOBJECTS. All routines (e.g. methods, procedures, functions) that have their own independent identity (and catalogs) in the product get mapped to the generic 'F' for Routines in this catalog.

     

    As a result, when you try to move backwards from the INVALIDOBJECTS catalog to revalidate the objects, you are required to unwind the mapping and get the right object type when calling ADMIN_REVALIDATE_DB_OBJECTS. As you point out, there isn't an equivalent to the hand-waving "routine" value used in SYSCAT.INVALIDOBJECTS which would cover all the things mapped to the F value.

     

    One option today is to pass in null for the object type which means that all of the object types are considered. This would cover everything but would cover more than "routines", it would basically look for everything with the same schema and name as the input.

     

    I don't see us changing anything here in the short term given our other priorities since this has been this way for a while. If you want to suggest an option like "routines" which would limit the set to just function, procedures, and methods, you could do so using the RFE process.

     

    Hope this helps,

    Paul.


    Original Message


  • 4.  RE: ADMIN_REVALIDATE_DB_OBJECTS inconsistent with SYSCAT.INVALIDOBJECTS

    Posted Mon March 30, 2020 01:19 PM

    Hi Paul,

    Thanks for your comments. It was more a comment / question about why you can end up with such a design with different flags and be non consistent ...

    Best regards,

    JMB


    Original Message