Checking for invalid packages

You can create a query to check for plans and packages that have become invalid or inoperative.

Procedure

To check for invalid packages:

Begin general-use programming interface information.

Issue the following SQL statement:

SELECT COLLID, NAME, VERSION, VALIDATE, ISOLATION, VALID, OPERATIVE
  FROM SYSIBM.SYSPACKAGE
  WHERE VALIDATE = 'R' OR ISOLATION = 'R'
    OR VALID = 'N' OR OPERATIVE = 'N';

End general-use programming interface information.

These statements identify packages that meet the following criteria:

Might redo validity checks at run time; if an invalid object or missing authority is found, DB2® issues a warning and checks again for the object or authorization at run time.
Use repeatable read isolation.
Are invalid (must be rebound before use), for example, deleting an index or revoking authority can render a package invalid.
Are inoperative (require an explicit BIND or REBIND before use). A package can be marked inoperative after an unsuccessful REBIND operation.