Saving access path information for static SQL statements

You can use package copies to automatically save pertinent catalog table and directory records for static SQL statements when you bind a package or rebind an existing package.

About this task

Begin program-specific programming interface information.When a performance regression occurs after you rebind a package, you can use the saved historical information to switch back to the older copy of the package and regain the old access paths. You can also use the historical information in conjunction with the APREUSE bind option to specify that Db2 tries to reuse the existing active access paths for static SQL statements when you rebind packages.

Procedure

To save access path information for static SQL statements, use the following approaches:

  • Specify the PLANMGMT bind option when you issue REBIND PACKAGE or REBIND TRIGGER PACKAGE command.
    You can specify the following values:
    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 in PLANMGMT bind option.

    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 in PLANMGMT bind option.

    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 in PLANMGMT bind option.

    End program-specific programming interface information.
  • Specify the default PLANMGMT option for REBIND PACKAGE and REBIND TRIGGER commands by setting the PLAMGMT subsystem parameter value.
    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 in PLANMGMT bind option.