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:
- 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
- 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.
- 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.
- 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.
- 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.