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.

http://www.redbooks.ibm.com/abstracts/sg247688.html

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.

REBIND PACKAGE(location.collid.name)
     DISABLE(batch,cics,db2call,dlibatch,imsbmp,remote,rrsaf)

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.

Comments

[dav1mo@yahoo.com]

Troy,
One item I would add to the cleanup for a test environment. Don't just keep the most recent 3 versions. Most shops are set up with at least a unit test environment and a QA environment. I would suggest keeping the most recent 3 in the unit test environment and the most recent version that was bound from your QA and production environments. In fact, most places I have been I set it up to keep the current week's packages plus the most recent 3 and the upper testing and production environments.
Dave Nance

[Troy Coleman]

Hi Dave,
These are all good suggestions. The main purpose of the article was not to tell people really how many versions of packages to keep and in what environment. It was to tell people how to disable a package to verify that it is really obsolete and not used anymore. Maybe I should have added more on guidelines for version management. Thanks for the input.