Managing access path change at migration

You can specify that Db2 tries to reuse existing access paths, and detect when access paths have changed, when you rebind your applications at migration.

About this task

How you manage your access paths at migration depends on the amount of change that your applications can tolerate. The most cautious approach is to try to enforce the previous access paths. However, this approach might also prevent your applications from using new access paths that might indeed provide improved performance.

When you want to reuse access paths for existing applications, use of the REBIND command is preferred over use of the BIND command with the REPLACE option. The REBIND command provides more reliable plan management techniques that cannot be used with the BIND command.

To manage access path changes at migration, use one of the following approaches:

• Retain existing access whenever possible, but accept new access paths when necessary:
  1. Specify the EXTENDED plan management policy to retain existing access paths at rebind.
     Db2 saves information about the existing access paths.
  2. Issue a FREE PACKAGE command to free inactive copies of the package.
     Otherwise, the existing original copy remains and the current active copy might be lost at a subsequent rebind.
  3. Issue REBIND commands to rebind the applications, and specify the APREUSE (WARN) and APCOMPARE (WARN) options.
     Db2 tries to use a hints to enforce each previous access path. For any access path that cannot be reused, Db2 selects a new access path for the statement. When the rebind of the packages is complete, Db2 issues a message that contains the numbers of access paths that were reused and were not reused.

     If you specify the EXPLAIN(YES), Db2 populates PLAN_TABLE rows with information about the access paths that changed as a result of the rebind operation. You can use the PLAN_TABLE data to identify and analyze the changed access paths.

• Retain the existing access paths from the previous version, or reject the rebind:
  1. Specify the EXTENDED plan management policy to retain existing access paths at rebind.
     Db2 saves information about the existing access paths.
  2. Issue a FREE PACKAGE command to free inactive copies of the package.
     Otherwise, the existing original copy remains and the current active copy might be lost at a subsequent rebind.
  3. Issue REBIND commands to rebind the applications, and specify the APREUSE (ERROR) and APCOMPARE (ERROR) options.
     Db2 tries to use hints to enforce the previous access paths. If any hints cannot be applied, or access path that results from the hint does not match the previous access path, processing ends for the package and Db2 issues messages.
• Consider new access paths, but detect access path changes:
  1. Specify the EXTENDED plan management policy to retain existing access paths at rebind.
     Db2 populates the access path repository with information about the existing access paths.
  2. Issue a FREE PACKAGE command to free inactive copies of the package.
     Otherwise, the existing original copy remains and the current active copy might be lost at a subsequent rebind.
  3. Issue REBIND commands to rebind the applications and specify the APCOMPARE(WARN) option.
     Db2 does not try to reuse existing access paths and issues messages when access paths change.
• Detect whether to expect access paths change before rebinding applications:
  1. Issue REBIND commands, but specify the EXPLAIN(ONLY) and APCOMPARE(WARN) bind options.
     The rebind action is not completed. Instead, Db2 issues messages for access paths that cannot be reused after a rebind.You can use the PLAN_TABLE data to identify and analyze the changed access paths.
  2. Issue REBIND commands to rebind only applications that can successfully reuse all access paths, or require only acceptable access path changes.