Analyzing access path changes at bind or rebind

When you rebind packages, you can use access path comparison to learn which access paths have changed, and to verify whether access path reuse was successful.

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 11 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

With access path comparison Db2 verifies that a new access path matches the previous access path. The comparison examines a new access path and compares it to the existing access path. When Db2 detects mismatches between new and previous access paths, it writes information about the mismatches to the PLAN_TABLE.REMARKS column.

When the comparison is complete, Db2 issues a DSNT285I message that indicates how many access paths are changed and unchanged after the rebind operation.

You can specify either error or warning rules for comparison. Under warning rules, the bind operations complete regardless of whether reuse or comparison fails for statements in a package. Under error rules, the bind operations ends for a package after all statements are processed if any reuse or comparison error is encountered.

Db2 also uses access path comparison to validate the resulting access paths when you specify access path reuse.

Procedure

To assess the result of access path changes after bind or rebind:

  1. Specify one of the following bind options to activate access path comparison:
    • APREUSE(WARN) to try to enforce the reuse of access paths, accept access path changes when reuse cannot be applied, and find out when changes occur. This option might result in unacceptable access path changes. Therefore, the recommendation is to also use a plan management policy that enables you to switch to previous access paths.
    • APCOMPARE(WARN) to get new access paths and find out when changes occur. This option might result in unacceptable access path changes. Therefore, the recommendation is to also use a plan management policy that enables you to switch to previous access paths.
    • APCOMPARE(ERROR) to accept no access path changes, without trying to reuse.
    • APREUSE(ERROR) to try to enforce the reuse of access paths, accepting no reuse failures or access path changes
  2. Specify one of the following bind options so that Db2 creates EXPLAIN output for the bind or rebind operations:
    • EXPLAIN(ONLY) specifies that the bind and rebind operations are not completed. However, Db2 processes the access paths and populates PLAN_TABLE with access path comparison result information. Because the bind or rebind operation are never completed under this option, APCOMPARE(ERROR) and APCOMPARE(WARN) have the same meaning when EXPLAIN(ONLY) is specified.
  3. Issue the command to bind or rebind the packages.
    The REBIND PACKAGE command is preferred when access path changes are accepted because it enables the use of plan management policies.
  4. Examine the DSNT285I message to learn the numbers of access paths that are changed and unchanged by the bind or rebind operations.
    DSNT285I  -csect-name REBIND FOR PACKAGE = package-name,
               USE OF APCOMPARE RESULTS IN:
               75 STATEMENTS WHERE COMPARISON IS SUCCESSFUL
               5 STATEMENTS WHERE COMPARISON IS NOT SUCCESSFUL
               2 STATEMENTS WHERE COMPARISON COULD NOT BE PERFORMED.
    If you specified access path reuse, you can also examine the DSNT286I message which provides statistics about the success of reuse.
    DSNT286I  -csect-name REBIND FOR PACKAGE = package-name,
               USE OF APREUSE RESULTS IN:
               73 STATEMENTS WHERE APREUSE IS SUCCESSFUL
               7 STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL
                 OR PARTIALLY SUCCESSFUL
               2 STATEMENTS WHERE APREUSE COULD NOT BE PERFORMED
               0 STATEMENTS WHERE APREUSE WAS SUPPRESSED BY OTHER HINTS.
    Although Db2 also uses comparison when you specify access path reuse, the comparison and reuse are separate operations. Reuse fails when Db2 cannot apply a hint to enforce the previous access path. Comparison fails when the new access path does not match the previous access path. Notice, as shown in the preceding example, that even when reuse fails Db2 might sometimes select a new access path that matches the previous access path.
  5. Query the PLAN_TABLE to find rows that Db2 populated as a result of the comparison or reuse.

    When reuse succeeds, Db2 inserts the value 'APREUSE' into the PLAN_TABLE.HINT_USED column. When reuse or comparison fails, Db2 populates the PLAN_TABLE.REMARKS column with information about the failures:

    • When reuse fails, Db2 inserts a reason code that corresponds to the reason codes values from SQLCODE +395.
    • When comparison fails, Db2 inserts information about the mismatch. When corresponding rows for the new and previous access path do not match, Db2 identifies a column that contains the values that do not match:
      • For rows that exist in both the old and new access paths, Db2 identifies the mismatch in the REMARKS column for the corresponding row for the new access path.
      • For rows that exist only in the new access path, the unmatched row is indicated in the REMARKS column of the new nonmatching row.
      • For rows that exist only for the old access path, the unmatched row is identified in the REMARKS column of a different row for the new access path.

What to do next

You might take any of the following actions based on the results of the comparison:
  • When reuse fails, rebind packages that can tolerate changes with the APREUSE(WARN) bind option. You can use the message and PLAN_TABLE output to create a list of packages and statements that had access path changes. The list might be helpful for isolating problems when performance regressions occur.
  • When reuse fails for packages that cannot tolerate access path changes, do not rebind the packages. This option is the most cautious approach, but it might prevent your applications from taking advantage of performance improvements.