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