DB2 Plan Management was introduced in DB2 9 for z/OS as an elegant way of providing a quick fall-back option in case of access path degradation during REBIND.
With Plan Management DB2 saves old copies of packages at REBIND time, so you may SWITCH back to previous copies in case of problems. Plan Management can be used in two modes: BASIC, where you keep the current and the previous copies of the package, and EXTENDED, where you keep 3 copies: the current, the previous, and the original one of the package. REBIND SWITCH allows you to decide to which copy to fall-back to.

The default in DB2 10 is EXTENDED. This figure is a quick overview of how this option works:

With DB2 Plan Management Extended you can fall-back to the previous or to the original copy of a package.
Each one of the three packages are maintained independently. Dropping an object on which package copies depends on will impact each copy in consequence and based on the extend of the object-package relationship, one or more may become non-operative. This is important to understand before dropping indexes or views.
In part because of above, you have to check the operability and validity of a previous package copy before attempting a REBIND SWITCH to a previous copy. This can be a complex task in DB2 9 for z/OS.

Fortunately, DB2 Plan Management has been improved with DB2 10 for z/OS. One of the changes that I like the most is the fact that it is now possible to get information about previous and original copies of packages without the need of performing SWITCH.
DB2 10 introduces the new catalog table SYSIBM.SYSPACKCOPY. This table contains SYSPACKAGE like information about previous and original copies of packages. Using this table you can verify the status and characteristics of previous packages copies before doing a REBIND SWITCH.

The column COPYID identifies the copy version of the package:

  • 1: The previous copy of the package
  • 2: The original copy of the package

The column PLANMGMT identifies the PLANMGMT option at BIND/REBIND time:

  • B: PLANMGMT BASIC
  • E: PLANMGMT EXTENDED —> the default in DB2 10
  • F: PLANMGMT OFF
  • O: PLANMGMT ON

This query is an example of combining the SYSPACKAGE and SYSPACKCOPY information to get a view of all the copies available for a package:

SELECT
SUBSTR(COLLID,1,10) AS COLLID ,SUBSTR(NAME,1,10) AS NAME ,LASTUSED
,VALID ,OPERATIVE ,COPYID ,PLANMGMT ,APRETAINDUP
FROM SYSIBM.SYSPACKAGE
WHERE COLLID = 'CRISCOLL' AND NAME = 'DSN8CLTC'
UNION ALL
SELECT
SUBSTR(COLLID,1,10) AS COLLID ,SUBSTR(NAME,1,10) AS NAME ,LASTUSED
,VALID ,OPERATIVE ,COPYID ,PLANMGMT ,APRETAINDUP
FROM SYSIBM.SYSPACKCOPY
WHERE COLLID = 'CRISCOLL' AND NAME = 'DSN8CLTC'
WITH UR;

This is an output example of the execution of this query:

---------+---------+---------+---------+---------+---------+---------+----
COLLID   NAME     LASTUSED   VALID OPERATIVE COPYID PLANMGMT APRETAINDUP
---------+---------+---------+---------+---------+---------+---------+----
CRISCOLL DSN8CLTC 2012-12-28 Y     Y         0      E        Y
CRISCOLL DSN8CLTC 2012-07-27 Y     Y         1      E        Y
CRISCOLL DSN8CLTC 2012-03-01 Y     Y         2      E        Y
DSNE610I NUMBER OF ROWS DISPLAYED IS 3

The LASTUSED column of the SYSIBM.SYSINDEXSPACESTATS table allows you to identify an index that has not been used for a long time. But before deciding to remove an index, you should think about the possibility of making a package’s old copy non-operative if there is a dependency on that index.

Fortunately, you can relay on the SYSIBM.SYSPACKDEP table for this: the DTYPE column allows you to identify the objects on which old copies of packages have a dependency.
The possible values of DTYPE are:

  • F: Non-inline SQL scalar function
  • N: Native SQL routine package
  • O: Original copy of a package
  • P: Previous copy of a package
  • R: Reserved for IBM use
  • T: Trigger package
  • Blank: Not a trigger package or a native SQL routine package

Summary:
DB2 Plan Management gets better DB2 Catalog support in DB2 10 for z/OS. It is now easier to check the status of packages before executing a REBIND SWITCH command using the SYSIBM.SYSPACKCOPY table; and you can check old copies dependencies using the SYSPACKDEP table.