Blog
DB2utor

Categories

June 23, 2009

Health Check for Packages

I've written extensive DB2utor blog entries about packages over the past few years. (See "IBM Redpaper: Considerations on Small and Large Packages", "Cleaning Out Obsolete Packages" and "Reducing the Impact of Long Package Lists.")

Despite my attention to this subject, I haven't provided much introductory material for those who are new to DB2 and development in programs written in languages like COBOL. A good place to start is the IBM Redbooks publication, "DB2 9 for z/OS: Packages Revisited." The beginning provides an overview of packages and details the benefits of using them.  As far as health checks, I refer to information you can gather from the DB2 catalog to:

  • Create a list of invalid or inoperative packages (and then fix problems and/or remove obsolete packages).
  • Create a list of PLAN statements that contain a DBRM. (This is important because an upcoming DB2 release requires that PLANs only contain packages.

When I write queries, I usually use SPUFI and I assume that I have short object names. Because of this, I like to substring the names to get more information on a screen.

Invalid or Inoperative
What follows is sample SQL listing all packages that are marked either invalid or inoperative. The package is marked invalid if a dependent object (like a table) is dropped. If a package is executed before the dependent object is recreated, DB2 marks it inoperative. Once a package is marked inoperative, it must be rebound manually using the REBIND package command:

SELECT SUBSTR(PK.COLLID,1,18) AS COLLECTION
                        , SUBSTR(PK.NAME,1,8)     AS PACKAGE
                        , HEX (PK.CONTOKEN)     AS CONTOKEN
                        , SUBSTR(PK.OWNER,1,8)     AS OWNER
                        , SUBSTR(PK.CREATOR,1,8)     AS CREATOR
                        , PK.VALID    AS VALID
                        , PK.OPERATIVE     AS OPERATIVE
                        , PK.EXPLAIN     AS EXPLAIN
                        , SUBSTR(PK.VERSION,1,26)     AS VERSION
                        , SUBSTR(PK.PDSNAME,1,44)    AS PDSNAME
FROM SYSIBM.SYSPACKAGE PK
WHERE (PK.VALID = 'N' OR PK.OPERATIVE = 'N')
ORDER BY PK.COLLECTION, PK.PACKAGE, PK.VERSION
WITH UR;

PLAN Contains DBRM
In a future DB2 release, the support of DBRMs in a PLAN is deprecated. Simply put, the PLAN can only contain packages. To help plan for the conversion, you can run the following SQL, which will list your existing PLANs that contain a DBRM. You'll want to convert the DBRM into a package and REBIND the PLAN with a package instead of a DBRM.

SELECT SUBSTR(D.PLNAME,1,8)          AS PLAN_NAME
                    , SUBSTR(D.NAME,1,8)     AS DBRM_NAME
                    , SUBSTR(D.PLCREATOR,1,8)      AS CREATOR
                    , SUBSTR(D.VERSION,1,26)         AS VERSION
                    , SUBSTR(D.PDSNAME,1,44)      AS PDSNAME
FROM SYSIBM.SYSDBRM D
ORDER BY D.PLNAME, D.NAME, D.VERSION
WITH UR;

This type of information and much more is available from the DB2 catalog. A list of the catalog tables can be found in the SQL Reference Guide. Here's a list of the DB2 manuals.