50 DB2 Nuggets #38: Tech Tip - Rebind inoperative DB2 packages.

Body

Inoperative DB2 Package:

Packages in DB2, can be dependent on certain types of database objects such as tables, views, aliases, indexes, triggers, referential constraints and table check constraints. 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. If the object that is dropped is an UDF, the package is placed into an inoperative state.

For an inoperative package, in the SYSCAT.PACKAGES system catalog view, the VALID column shows: X

X = Package is inoperative because some function instance on which it depends has been dropped; explicit rebind is needed.

Steps to recreate packages:

db2rbind command can be used to rebind only Invalid packages not the Inoperative packages.

You need to run REBIND command for each inoperative package to recreate it and make it valid.

If you have mulitple inoperative packages, to find and rebind all of them, run the following query:

db2 "select 'rebind package ' || rtrim(pkgschema) || '.' || rtrim(pkgname) || ';'" as statement from syscat.packages where valid = 'X'"  > outfile
 

Now run the rebind commands saved in the output file as a script:

db2 -tvf outfile

Refer:

REBIND

SYSCAT.PACKAGES