75 ways to demystify DB2 #64: Techtip : How to resolve an invalid DB2 package problem?

Body

I have recently dealt with an invalid DB2 package problem, ofcourse in the past too :-), I now take this opportunity to share the steps used to diagnose and resolve it. Hope it helps.

A bit background from my understanding, when a package can become invalid. Below are some situations:

- If a package is dependent on a database object (such as a table, view, trigger, and so on), and that object is dropped, the package is placed into an invalid state.
- During database upgrade, all packages stored in the database will be invalidated by the UPGRADE DATABASE
- Invalid packages will be automatically (or implicitly) rebound by the database manager when they are executed. however, if a user executing the package/routine does not have the appropriate privileges to do a bind on the database, then the automatic revalidation might not succeed, hence some of the package might still remain invalid, you would need an explicit rebind.

More details can be found in our Knowledge Center:
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.apsg/src/tpc/db2z_changesinvalidateplanspkgs.html

Here are some of the steps I have taken to resolve the invalid package issue:

1) Queried syscat.packages to find the invalid package, there is 1 invalid package as shown below.

C:\Program Files\IBM\SQLLIB\BIN>db2 "select PKGSCHEMA, pkgname,valid from syscat.packages where valid = 'N'" > pakcage.txt

PKGSCHEMA PKGNAME VALID
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -----
DB2ADMIN P1974733917 N

1 record(s) selected.

2) Chased down the dependent object of the package by querying syscat.packagedep:

C:\Program Files\IBM\SQLLIB\BIN>db2 "select PKGSCHEMA, pkgname, btype,bname from syscat.packagedep where pkgname='P1974733917' "

PKGSCHEMA PKGNAME BTYPE BNAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -----
DB2ADMIN P1974733917 T EMPLOYEE
DB2ADMIN P1974733917 I XEMP2
DB2ADMIN P1974733917 I PK_EMPLOYEE

3 record(s) selected.

BTYPE
T=> Table
I => Index

3) Verified that the EMPLOYEE table exists

db2 "list of tables for schema "DB2ADMIN"",

Table/View Schema Type Creation time
EMPLOYEE DB2ADMIN T 2015-08-19-15.47.21.140008

4) Then looked up if indexes exist, they seem to be present too.

C:\Program Files\IBM\SQLLIB\BIN>db2 "describe indexes for table employee"

Index Index Unique Number of Index Index
schema name rule columns type partitioning
------------------------------- ------------------- -------------- -------------- --------------------------- --------------
DB2ADMIN PK_EMPLOYEE P 1 RELATIONAL DATA -
DB2ADMIN XEMP2 D 1 RELATIONAL DATA -

2 record(s) selected.

5) Now, we know the objects associated with the package exist, but we still see invalid package. How do you fix it? Yes, you guessed it right, do the rebind!!!

C:\Program Files\IBM\SQLLIB\BIN>db2rbind sample -l log.out all

Rebind done successfully for database 'SAMPLE'.

During explicit rebind:
- The user issuing the db2rbind command will need the proper authority to perform the bind otherwise you could receive authorization errors
- If the dependencies for the package were still dropped then the db2rbind command will show errors when it is trying to rebind certain packages.
Once object is back (assuming it was dropped), you can do rebind, the rebind would succeed.

6) Bingo! The package that was previously invalid now shows valid.

C:\Program Files\IBM\SQLLIB\BIN>db2 "select PKGSCHEMA, pkgname, btype,bname from syscat.packagedep where pkgname='P1974733917' "

PKGSCHEMA PKGNAME VALID

-------------------------------------------------------------------------------------------------------------------------------- -----
DB2ADMIN P1974733917 Y

1 record(s) selected.

References:
syscat.packages: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001053.html
syscat.packagedep: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001052.html?cp=SSEPGG_9.7.0%2F2-10-7-61&lang=en
db2rbind: https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001995.html

Thank you for reading the blog!

Please leave a comment if you have a question or you wish you provide feedback.

Thanks
Mary Kassey