Cleaning Out Obsolete Packages
Sometimes DBAs must clean house. For instance, the DB2 catalog can get cluttered with lots of old software packages. In the past when I've had to clean up packages, I'd follow this criteria:
1) Remove when the load module is missing.
2) Keep only three versions of a package.
3) Remove old releases of vendor software.
4) Remove invalid or inoperative packages.
Freeing the packages based on these criteria made management much easier. However, removing invalid or inoperative packages was always the trickiest item on my list. In this case, I'd produce a list of invalid or inoperative packages and route it to package owners so they could verify that the packages could be freed.
This SQL can be used to retrieve a list of inoperative or invalid packages:
SELECT SUBSTR(PK.COLLID,1,18) AS COLLID
, SUBSTR(PK.NAME,1,18) AS NAME
, SUBSTR(PK.VERSION,1,8) AS VERSION
, SUBSTR(PK.OWNER,1,8) AS OWNER
, PK.VALID AS VAL
, PK.OPERATIVE AS OP
, DATE(PK.BINDTIME) AS DATE
FROM SYSIBM.SYSPACKAGE PK
WHERE PK.VALID = 'N'
OR PK.OPERATIVE = 'N'
ORDER BY 1, 2, 3
WITH UR #
Here's sample output from this query:
COLLID NAME VERSION OWNER VAL OP DATE
DSNACC DSNACCDL UK03289 IBMUSER N Y 2009-02-12
DSNACC DSNACCDL UK07524 IBMUSER N Y 2009-02-12
DSNACC DSNACCDL V8R1 IBMUSER N Y 2009-02-12
DSNACC DSNACCDS V8R1 IBMUSER N Y 2009-02-12
DSNACC DSNACCJF IBMUSER N Y 2009-02-12
DSNACC DSNACCMD CSBP N Y 2009-02-12
DSNACC DSNACCMN CSBP N Y 2009-02-12
I'd e-mail my list to the different owners, who would verify which packages were obsolete. Then I'd free the obsolete packages. The only drawback with this is that occasionally I'd learn that a package I deleted was actually something I needed.
Recently though, I discovered an alternative to freeing packages without knowing if they're still needed. The new IBM Redbook, "DB2 9 for z/OS: Package Revisited," has a chapter on deprecating packages.
Instead of freeing the package, you can disable it. Once the package is disabled, it cannot be executed. Attempting to execute a disabled package results in SQL error -807. However, if you need to reactivate the package for execution, you can simply REBIND with the ENABLE parameter for the given environment in which the package will be executing.
Otherwise, after 60 or 90 days, just use the FREE PACKAGE command to remove it from the catalog.
The Redbook also notes that if you collect performance data, you can see when all packages were last executed. Then you can disable packages that haven't been used recently (say, in the last quarter). I've been supporting package management for many years, and I've used BIND parameter DISABLE for security -- specifically, to make sure a package designed for BATCH couldn't be executed in online CICS. However, I never realized that DISABLE could be used in this manner.
If you have other tips and techniques on managing obsolete packages, please post in Comments so others can learn from your experience.