Package is the logical grouping for the resource that require to execute the routine . It is also known as “Access plan” . Rebind package is the same concept as recompile in other DBMS ( MSSQL, Sybase and Oracle).
- List all the packages
- Rebind the package
- Rebind all package
- PKGCACHE procedure - generate a summary report of package cache metrics
List all the packages
--- ALl packages: db2 list package for all
select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
valid, format, isolation, blocking
from syscat.packages
order by pkgschema, pkgname, pkgversion;
--- Package own by NULLID schema: db2 list package for system
select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
valid, format, isolation, blocking
from syscat.packages
where pkgschema = 'NULLID'
order by pkgschema, pkgname, pkgversion;
-- Packages own by user: db2 list package
select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
valid, format, isolation, blocking
from syscat.packages
where pkgschema = USER
order by pkgschema, pkgname, pkgversion;


REOPTVAR: Indicates whether the access path is determined again at execution time using input variable values.
- A = Access path is re optimized for every OPEN or EXECUTE request
- N = Access path is determined at bind time
- O = Access path is re optimized only at the first OPEN or EXECUTE request; it is subsequently cached
Rebind the package
- VALID column indicate whether the package still valid and if it is N, that means the package is not valid and needs to be rebind.
- If there is the current user session is using the package, the bind would not complete until the user’s unit of the work complete. the bind would need to hold the exclusive lock in the package in the syscat.package table.
SELECT PKGNAME, PKGSCHEMA, PKGVERSION, UNIQUE_ID, BOUNDBY, REOPTVAR,
TOTAL_SECT, CREATE_TIME, EXPLICIT_BIND_TIME, LAST_BIND_TIME,
VALID, FORMAT, ISOLATION, BLOCKING
FROM SYSCAT.PACKAGES
WHERE PKGNAME = 'P5582207';

db2 rebind P5582207 reopt once


Another way to rebind the package is to use SYSPROC.REBIND_ROUTINE_PACKAGE
However, the SYSPROC.REBIND_ROUTINE_PACKAGE will need to use the routine name ( store procedure name ).
To Find the routine name from package name we have to join through the SYSCAT.ROUTINEDEP
SELECT DISTINCT A.ROUTINENAME, B.BNAME FROM SYSCAT.ROUTINES A,
SYSCAT.ROUTINEDEP B, SYSCAT.PACKAGES C WHERE
B.ROUTINENAME = A.SPECIFICNAME AND B.BNAME = C.PKGNAME AND C.PKGNAME = 'P5582207'

CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
'P','BONUS_INCREASE','RESOLVE ANY REOPT ONCE APREUSE YES')

Rebind all package
Below command will rebind the invalid package only.
db2rbind sample -l /tmp/rebindall.log

use all parameter would bind all packages.
db2rbind sample -l /tmp/rebindall.log all

PKGCACHE procedure - generate a summary report of package cache metrics
Generate the report for what is in the package cache
db2 "call monreport.pkgcache"
-- Last 30 mins
db2 “call monreport.pkgcache(30)”

Reference