PLANMGMT bind option

The PLANMGMT option retains, during a rebind operation, all relevant package information (such as metadata, query text, dependencies, authorizations, and access paths) in catalog tables and in the directory.

Command option Option values Used with
PLANMGMT
  • (EXTENDED)
  • (BASIC)
  • (OFF)

If performance regression occurs, you can direct Db2 to fall back to the older copy of a package. Over time, a package can have multiple copies that exist on disk storage, but only one of those copies is the active or current copy. All other copies are inactive.

Option descriptions for PLANMGMT

PLANMGMT(EXTENDED)
Discard the previous copy of a package. The current copy becomes the previous copy, and the original copy is managed as follows:
  • If no original copy exists, the current copy is cloned to become the original.
  • If an original copy exists, it is retained as the original.

In each case, the incoming copy of a package becomes the new current copy.

Db2 issues an error message if the REBIND command specifies PLANMGMT(EXTENDED) option and it also specifies changes to certain bind options. For more information, see Package bind option changes that affect use of PLANMGMT

PLANMGMT(BASIC)
Discard the previous copy of a package. The current copy becomes the previous copy, and the incoming copy becomes the current copy. If an original copy of a package exists, it remains available.

Db2 issues an error message if the same rebind command specifies PLANMGMT(BASIC) option and it also specifies changes to certain bind options. For more information, see Package bind option changes that affect use of PLANMGMT

PLANMGMT(OFF)

The current access path is removed and replaced with the incoming copy, and any previous or original copies are unaffected.

However, all package copies (current, previous, and original) are purged if the command also specifies changes certain bind options. For more information, see Package bind option changes that affect use of PLANMGMT

Pattern-matching characters for PLANMGMT

PLANMGMT settings remain valid when pattern-matching characters (*) are used in the REBIND syntax. When you use REBIND PACKAGE to rebind more than one package, Db2 retains previous and original copies for each package separately.

Defaults for PLANMGMT

Process Default value
BIND PLAN N/A
BIND PACKAGE N/A
REBIND PLAN N/A
REBIND PACKAGE PLANMGMT subsystem parameter 1
REBIND TRIGGER PACKAGE PLANMGMT subsystem parameter 1
Note:
  1. The PLANMGMT subsystem parameter is ignored if the REBIND command changes certain bind options for the package. For more information, see Package bind option changes that affect use of PLANMGMT

Package bind option changes that affect use of PLANMGMT

If a REBIND PACKAGE or REBIND TRIGGER PACKAGE command changes any of the following bind options for a package, the changes affect use of the PLANMGMT option for the package.

  • Start of changeOWNER, unless EXPLAIN(ONLY) is specifiedEnd of change
  • Start of changeOWNERTYPE, unless EXPLAIN(ONLY) is specifiedEnd of change
  • QUALIFIER
  • ENABLE
  • DISABLE
  • PATH
  • PATHDEFAULT
  • IMMEDWRITE
  • BUSTIMESENSITIVE
  • SYSTIMESENSITIVE
  • ARCHIVESENSITIVE

That is, Db2 returns an error if PLANMGMT(EXTENDED) or PLANMGMT(BASIC) is also specified when any of the options in the preceding list are changed. If PLANMGMT(OFF) is specified, Db2 purges all saved packages copies.

If the PLANMGMT setting for the package is inherited from the value of the PLANMGMT subsystem parameter, and the value of any of the option is preceding list is changed, Db2 internally disables the PLANMGMT option. As a result, Db2 purges all saved package copies.

However, if the previously listed options are specified without changing the existing values, Db2 processes the command as if the option were not specified. In that case, an error message is not issued, and the specified PLANMGMT option is accepted or the PLANMGMT subsystem parameter takes effect.

Catalog records for PLANMGMT

The PLANMGMT column value is blank in the SYSIBM.SYSPACKAGE row for a new package that was created from a BIND PACKAGE command.

If the package is created by a CREATE statement for a native SQL routine, the PLANMGMT column value in the SYSIBM.SYSPACKAGE row is the value of the PLANMGMT subsystem parameter.

See the PLANMGMT columns in SYSPACKAGE catalog table and SYSPACKCOPY catalog table.