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:
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
