DB2 LUW Invalid Packages - detection, Investigation, and Resolution

I was not able to find what I considered a good, clear reference for this when I recently encountered it at a customer installation.

I found that there were quite a few invalid packages that needed to be dealt with.
The worst, of course, were those with a value of 'X' in the VALID indicator. (VALID = 'X').

So, here are some simple steps I followed to investigate and resolve these issues.

You, of course, may want to investigate these items further.

Get the packages that have issues:

db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID <> 'Y'" > invalid_packages.txt

To get the ones that require manual intervention, use

db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID = 'X'"

Figure out if those packages contain your SQL:

db2 "select pkgschema,pkgname,unique_id, TEXT from syscat.statements where UNIQUE_ID = x'414141414157465A' AND PKGNAME = 'STADMG02'"

NOTE on the above: The TEXT field will show you the query associated with your package, but it is defined as a CLOB, and is therefore quite large. If you need to see it, redirect to a file. Otherwise it might be best to omit it.

Do something about those packages :)

db2 rebind package NULLID.STADME01 RESOLVE ANY REOPT ONCE

Where NULLID is the schema used by the jdbc driver, and STADME01 is the package name returned in the PKGNAME field. The schema name and the package name are retreived from your first query at top.

EDIT: To auto-generate a script that rebinds everything:

db2 -x "select 'rebind package ' || pkgschema || '.' || pkgname || ' RESOLVE ANY REOPT ONCE ;' from syscat.packages where VALID <> 'Y'" > rebind_other_packages.sql

Here's an example of me doing this in our internal environment:

/home/db2xbox >db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID = 'X'"

PKGSCHEMA                                                                                                                        PKGNAME                                                                                                                          UNIQUE_ID             VALID

-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------- -----

NULLID                                                                                                                           STADMG02                                                                                                                         x'414141414157465A'   X

NULLID                                                                                                                           STADMG01                                                                                                                         x'4141414141494157'   X

NULLID                                                                                                                           STADME01                                                                                                                         x'41414141414B4B30'   X

  3 record(s) selected.

/home/db2xbox >db2 "select pkgschema,pkgname,unique_id, TEXT from syscat.statements where UNIQUE_ID = x'414141414157465A' AND PKGNAME = 'STADMG02'"

NOTE: I did note run the above ↑↑↑↑↑↑ because the output is quite large due to he "TEXT" field which is defined as a CLOB.

/home/db2xbox >db2 rebind package NULLID.STADMG02 RESOLVE ANY REOPT ONCE

DB20000I  The REBIND PACKAGE command completed successfully.

/home/db2xbox >

/home/db2xbox >db2 describe table syscat.statements

                                Data type                     Column

Column name                     schema    Data type name      Length     Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

PKGSCHEMA                       SYSIBM    VARCHAR                    128     0 No

PKGNAME                         SYSIBM    VARCHAR                    128     0 No

STMTNO                          SYSIBM    INTEGER                      4     0 No

SECTNO                          SYSIBM    SMALLINT                     2     0 No

SEQNO                           SYSIBM    INTEGER                      4     0 No

TEXT                            SYSIBM    CLOB                   2097152     0 No

UNIQUE_ID                       SYSIBM    CHARACTER                    8     0 No

VERSION                         SYSIBM    VARCHAR                     64     0 Yes

References:
syscat.pakcages :         IBM Information Center link to syscat.packages catalog view
syscat.statements         IBM Information Center link to syscat.statements catalog view
db2 rebind command:  IBM Information Center link to db2 rebind command