Reusing and comparing access paths at bind and rebind

You can specify that Db2 tries to reuse previous access paths for SQL statements whenever possible, and compares the new and previous access paths.

Before you begin

Db2 ignores the APREUSE bind option for any package or package copy that was bound before DB2® 9. You must bind or rebind the package in Db2 12 and accept the access path changes, before you can begin reusing access paths. The RELBOUND column of the SYSIBM.SYSPACKAGE or SYSIBM.SYSPACKCOPY catalog tables indicates the Db2 release in which the package or package copy was last bound or rebound.

About this task

In many cases, you must rebind packages before your applications can benefit from performance improvements from the latest version. Similarly, you must bind packages again after modifying your applications to take advantage of functions from a new version of Db2. However, a common complaint is that the necessary bind or rebind operations often result in access path changes, and these changes sometimes result in unacceptable performance regressions.

However, you can reduce the risk that is associated with rebinding packages by using a reuse or comparison strategy when you rebind your packages. With access path reuse, Db2 automatically specifies internal statement-level access paths to try to enforce previous access paths. When a BIND command is used to bind a package that contains both statements that existed before and new statements, Db2 tries to reuse only for the pre-existing statements.

Important: Access path reuse is not guaranteed to succeed in all cases. For example an access path that relies on objects (such as indexes) that no longer exist cannot be reused. Version incompatibilities might also prevent access paths from being reused. Some access paths cannot be reused because of ambiguity in the underlying access path information. For example: an access path specifies to the type of join to use and the number of matching columns, but the names of the matching columns are not available.

With access path comparison Db2 verifies that a new access path matches the previous access path.

Db2 uses the comparison when you specify reuse to validate that the access path that results from successful reuse actually matches the previous access path. However, you can also use access comparison separately when you want to know how access paths have changed without preventing those changes.

Procedure

To prevent and assess changes to access paths changes at bind or rebind, you can use one or a combination of the following approaches:

  • Specify the APREUSE(WARN) bind option.
    This approach means that you want Db2 to reuse existing access paths whenever possible. However, you are willing to accept new access paths when Db2 cannot reuse the previous access path. Under this option, Db2 selects a new access path and continues processing the package when a previous access path cannot be reused. When processing completes for each package, Db2 issues a DSNT286I message to report the numbers of access paths that were reused and were not reused.

    If you use the APREUSE(WARN) option in conjunction with the APCOMPARE(WARN) option, it is possible for comparison to succeed even when reuse fails. This situation might happen if the specified access path cannot be applied but Db2 chooses the same access path again through normal access path selection.

  • Specify the APREUSE(ERROR) bind option.
    Under this option, which is the most cautious approach for preventing access path changes, you accept rebinds only when all access paths for a package can be reused.
    Under the APREUSE(ERROR) bind option, the rebind operation fails for a package after all of the statements are processed whenever any access paths cannot be reused. Db2 begins processing the next package, if additional packages are specified. Because comparison is also implied by reuse, processing for a package might end because a comparison fails. When all processing is complete, Db2 issues a DSNT286I message to report the numbers of access paths that could and could not be reused.
  • Specify the APCOMPARE(WARN) option.
    This approach means that you do not want Db2 to try to reuse old access paths, but that you do want to identify access path changes. Some access paths might change after the rebind operation, but you are willing to accept all changes.

    Consequently, the following recommendations apply to this approach:

    • Use a REBIND PACKAGE command and specify a plan management policy that enables you to switch to previous access paths if unacceptable changes occur.
    • Examine the quantity and types of changes to look for potential problems. You can specify the EXPLAIN(YES) or EXPLAIN(ONLY) option capture information about the selected access paths.
    • Maintain a list of packages with changed access paths to aid in problem isolation when a regression occurs.

    For more information about comparing access path changes see Analyzing access path changes at bind or rebind.

    However, when the bind operation is complete, Db2 issues a DSNT285I message that indicates how many access paths are changed after the rebind operation.

  • Specify the EXPLAIN(YES) or EXPLAIN(ONLY) options in combination with APREUSE(ERROR) and APCOMPARE(ERROR) or (WARN).
    When you specify these options, Db2 inserts information about reuse or comparison failures in the PLAN_TABLE.REMARKS column. For more information, see Analyzing access path changes at bind or rebind.
  • Start of change Specify the appropriate APREUSE bind option with APREUSESOURCE(PREVIOUS) or APREUSESOURCE(ORIGINAL) to reuse the access path from a previous or original package copy.
    You can use this approach when you want to use the access path from a previous or original package copy that is not eligible for the SWTICH bind option because it is invalid, or because is was bound in a release prior to DB2 10.
    End of change

What to do next

Examine DSNT285I and DSNT286I messages. For more detailed analysis, query the following PLAN_TABLE columns to identify the access path changes that resulted from the bind or rebind:

  • REMARKS
  • HINT_USED
  • BIND_EXPLAIN_ONLY

You can use the information in these columns to identify the access paths that cannot be reused. You can also examine the PLAN_TABLE rows for these statements to analyze the access paths that Db2 selects when reuse cannot be applied.

When you have completed your analysis, you must decide the approach to take for any packages that failed because APREUSE(ERROR) was specified. For example, you might take any of the following approaches:
  • Rebind the packages and specify APREUSE(WARN). This approach enables your packages to take advantage of new functions, but it might expose some statements in the packages to access path changes.
  • Rebind the remaining packages and specify no reuse. This approach enables your packages to take advantage of new functions, but it exposes every statement in the packages to possible access path changes.
  • Do not rebind the packages.
End program-specific programming interface information.