Managing access path changes for periodic maintenance

You can detect and evaluate access path changes when you rebind after organizing your data and collecting statistics, and when you apply routine maintenance. You can also specify that Db2 tries to reuse existing access paths when you rebind. You can also save information about existing access paths when you rebind, and revert to previous access paths when performance regressions occur.

About this task

Reorganizing your data and capturing accurate statistics are important activities for maintaining good performance. Generally new access paths offer improvements because the organization and statistics for the data have changed.

Certain other maintenance activities (such as applying PTFs) might also require that you rebind your applications, before you can take advantages of the fixes that are delivered. Again, it is possible that new access paths after the rebind provide better performance that the existing access paths.

However, unwanted access path changes sometimes result from these changes. You can investigate the access path changes before rebinding, or specify that Db2 tries to reuse the existing access paths.

Procedure

To prevent access path regression when you apply Db2 maintenance, use one of the following approaches:

  • Retain the existing access paths and rebuild the run time structures:
    1. 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.
    2. Issue a REBIND PACKAGE command, and specify the following options:
      • PLANMGMT(EXTENDED) to specify that Db2 saves information about the existing access paths.
      • APREUSE(ERROR) to specify that Db2 actively tries to reuse access paths or APCOMPARE(ERROR) to specify that Db2 accepts only unchanged access paths.

      Optionally, you can the specify EXPLAIN(ONLY) option to investigate access path changes without completing the specified bind operation.

  • Retain existing access paths when possible, but accept except new access paths when necessary and rebuild run time structures:
    1. 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.
    2. Issue a REBIND PACKAGE command, and specify the following options:
      • PLANMGMT(EXTENDED) to specify that Db2 saves information about the existing access paths.
      • APREUSE(WARN) to specify that Db2 actively tries to reuse access paths but accepts new access paths when required.

      Optionally, you can the specify EXPLAIN(YES) option to investigate the access path changes that occur as a result of the bind operations.

    3. When undesirable access path changes occur, rebind again and use the SWITCH option to revert to the previous access paths.
  • Consider new access paths, but detect access path changes:
    1. 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.
    2. Issue a REBIND PACKAGE command, and specify the following options:
      • PLANMGMT(EXTENDED) to specify that Db2 saves information about the existing access paths. The BIND command with the ACTION(REPLACE) option does not support this capability.
      • APCOMPARE(WARN) to specify that Db2 identifies access paths that change after the rebind operation.
    3. When undesirable access path changes occur, rebind again and use the SWITCH option to revert to the previous access paths.

What to do next

It is best to issue FREE PACKAGE commands to free inactive package copies when the application of maintenance is complete. By doing so you can reclaim the disk space that was used by the inactive copies.