IBM Support

Packages that are either inoperative or invalid state after database upgrade

Question & Answer


Question

How to fix packages that are either inoperative or invalid state after database upgrade

Cause

During database upgrade, all packages for user applications and routines are marked as invalid.
Package is inoperative because some function instance on which it depends has been dropped.
The package status can be verified by running the command below:
db2 "SELECT PKGSCHEMA, PKGNAME, OWNER, VALID FROM SYSCAT.PACKAGES"

Answer

Invalid packages will be implicitly rebound the first time that an application uses them after you upgrade your database. To eliminate this overhead, you can explicitly rebind the packages.
Inoperative packages needs an explicit rebind.

To rebind the packages that are marked as invalid follow the steps below:

- logon as an user with DBADM authority
- db2rbind database-name -l logfile all -u userid -p password


To rebind the packages that are marked as inoperative follow the steps below:

-db2 connect to <databasename>
-db2 "select 'REBIND PACKAGE ' || rtrim(pkgschema) || '.' || rtrim(pkgname) || ';' as command from syscat.packages where valid = 'X'" > rebind.sql
-db2 -tvf rebind.sql
-db2 terminate

Related Information

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP & UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21684218