Package copies for plan management

When you rebind packages with the PLANMGMT(EXTENDED) or PLANMGMT(BASIC) plan management policies, Db2 retains package information for static SQL statements as package copies.

When you bind a package, Db2 stores relevant package information for static SQL statements as records in several catalog tables and in the directory. Db2 records the following types of information about static SQL statements when you bind a package:

  • Metadata, in the SYSIBM.SYSPACKAGE catalog table
  • Query text, in the SYSIBM.SYSPACKSTMT catalog table
  • Dependencies, in the SYSIBM.SYSPACKDEP catalog table
  • Authorizations
  • Access paths
  • Compiled run time structures, in the DSNDB01.SPT01 directory table space

When you rebind such package, Db2 replaces the records for the package with information from the new bind. However, you can specify that Db2 retains the previous records in package copies when you rebind packages. The default plan management policy is PLANMGT(EXTENDED), which means that Db2 saves as many as three copies for each package. When you specify PLANMGMT(EXTENDED), Db2 retains active, previous, and original copies of the package.

Each copy might contain different metadata and compiled run time structures. However, the following attributes are common to all copies in a corresponding set of active, previous, and original package copies:
  • Location
  • Collection
  • Package name
  • Version
  • Consistency token

You can use package copies with the following types of applications:

  • Regular packages
  • Stored procedures
  • Trigger packages
Start of change

Phase-in of rebound packages

FL 505 With rebind-phase in, Db2 can rebind a package concurrently with its execution. A rebind operation creates a new copy of the package. When the rebind operation finishes, new threads can use the new package copy immediately, and existing threads can continue to use the copy that was in use prior to the rebind (the phased-out copy) without disruption.

This capability also enables switching to previous access paths and runtime structures gradually (the switch to the previous access path is phased-in), which allows regression recovery without incurring an application outage.

For more information, see Phase-in of package rebinds.

End of change

Invalid package copies

Packages copies can become invalid when any object that the package depends on is altered or dropped. For example, a package can become invalid when an index or table is dropped, or when a table column is altered. The SYSIBM.SYSPACKDEP catalog table records package dependencies. Depending on the change, different copies of the packages can be affected differently. For a dropped table, all of the copies of the package would be invalidated. Whereas, in the case of a dropped index, only certain copies that depend on the dropped index might be invalidated.

Packages copies can also become invalid if they were last bound or rebound in a Db2 release that is too old. In Db2 12, package copies that were bound in releases prior to DB2® 10 are invalid.

When Db2 finds that the active copy for a package is invalid, it uses an automatic bind to replace the current copy. The automatic bind is not affected by invalid status on any previous or original copies, and the automatic bind replaces only the active copy.

Start of changeIf you issue a command to rebind a package with the SWITCH option and you specify an invalid package copy, the rebind operation fails and Db2 issues a DSNT330I message.End of change

Start of changeYou can issue a FREE PACKAGE command and specify the INVALIDONLY(YES) option to free inactive package copies that are invalid. End of change

End program-specific programming interface information.