Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Unanswered: When does SP / package become invalid?

    v9.7 FP7.

    I got db2look from serverA and run it on serverB. SQL SP didn't get created because it references an object that doesn't exist.

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0204N "XXX.YYY" is an undefined name. LINE NUMBER=246.
    SQLSTATE=42704


    I verified that object XXX.YYY doesn't exist on serverA. But to my surprise, SP and its associated package are still valid. I even tried rebinding the package and it completed successfully.

    When should SP/package get marked as invalid?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A package is marked as invalid when a dependent object is missing. The first time that someone tries to execute an invalid package, DB2 will do an automatic rebind, and if the dependent objects are now available, it will create a valid package on the fly.

    If the first rebind of an invalid package fails, it will be marked as inoperative, and will no longer be automatically rebound at first execution, and must be explicitly rebound to get back to valid state (when all dependent objects are now available).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    What I don't understand is why SP/package is still valid in my example. SP is dependent on an object that doesn't exist (double checked).

    Do I have to call SP in order for db2 to realize that a dependent object is missing and SP/package should be marked as invalid? I didn't write SP code so don't want to call it and cause any potential problems. I was hoping that rebind would detect a missing object and at least mark the package as invalid, but it completed successfully.


    Code:
    > db2 "select substr(routineschema,1,10),substr(routinename,1,20),routinetype,valid from syscat.routines where routinename = 'BBBBBBBBBBBBBBB'"
    
    1          2                    ROUTINETYPE VALID
    ---------- -------------------- ----------- -----
    AAAAAAAA   BBBBBBBBBBBBBBB    P           Y
    
    
    
    > db2 "select bname from syscat.routinedep where btype = 'K' and routinename = 'BBBBBBBBBBBBBBB'"
    
    BNAME
    --------------------------------------------------------------------------------------------------------------------------------
    P4094787
    
    
    
    > db2 list packages for all | head -3; db2 list packages for all | grep P4094787
    
                                      Bound     Total                          Isolation
    Package     Schema    Version     by        sections      Valid   Format   level     Blocking
    P4094787    AAAAAAAA              DB2INST1             38 Y       0        CS        U
    
    
    
    >  db2 rebind package AAAAAAAA.P4094787
    DB20000I  The REBIND PACKAGE command completed successfully.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Package will be marked as invalid as soon as dependent object is dropped. But if missing object is something like an index or MQT, then DB2 may choose a different object during rebind and still work. Not all dependent objects are actually required dependencies.

    There is a slight difference to how this works if the package is bound with VALIDATE RUN parms. See BIND command for details.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •