When you rebind packages and encounter reuse failures,
you must next decide what to do about the remaining packages.
Before you begin
Capture and analyze EXPLAIN information for packages that
cannot be rebound with the APREUSE(ERROR) option.
About this task
Certain limitations apply to access path reuse, and not
all access paths can be reused successfully. So, successful reuse
is not guaranteed for all statements. Therefore, access path reuse under the
APREUSE(ERROR) bind option is an all-or-nothing proposition at the
package level. However, you can also use the APREUSE(WARN) bind option
to apply access path reuse wherever possible at a statement level.
The remaining statements in the package are optimized as if no reuse
was specified.
Some of the approaches described here include
detailed analysis of individual packages and access paths for specific
statements. You might find that the costs of such activities are prohibitive.
Procedure
To handle packages that encounter reuse failures,
use one or a combination of the following approaches:
-
Rebind the packages and specify reuse whenever possible.
This approach prevents most access path changes, enables your
applications to take advantage of new functions at migration. It also
enables Db2 to choose new access
paths, where necessary, based on the latest statistics. However, because
some access paths in the package are subject to change, care must
be taken to evaluate whether the applications can tolerate the potential
changes.
- Reissue the command to rebind the packages and specify
the APREUSE(WARN), APCOMPARE(WARN), and EXPLAIN(ONLY) bind options.
The rebind operation is not completed, but Db2 populates the PLAN_TABLE with information
about the new access paths.
- Analyze the PLAN_TABLE records for the access paths
that cannot be reused, and determine whether the new access paths
are acceptable.
- If the new access
paths are acceptable, reissue the command to rebind the packages and
specify the APREUSE(WARN) option.
Db2 reuses access paths whenever possible for
all statements in the packages. When reuse fails for certain statements
in a package, Db2 generates
new access paths for those statements.
- Rebind the packages and specify no reuse.
This
approach enables your applications to take advantage of new functions
at migration. It also enables Db2 to
choose new access paths based on the latest statistics. However, because
all access paths in the package are subject to change, care must be
taken to evaluate whether the applications can tolerate the potential
changes.
- Reissue
the command to rebind the packages and specify the APREUSE(NONE),
APCOMPARE(WARN), and EXPLAIN(ONLY) bind options.
The rebind
operation is not completed, but Db2 populates
the PLAN_TABLE with information about the new access paths.
- Analyze the PLAN_TABLE records for the new access paths,
and determine whether the new access paths are acceptable.
- Do not rebind the packages that encounter reuse failures.
This approach is the most conservative approach in terms of
preventing access paths changes, and it requires the least effort
for detailed analysis of your packages. However, taking this approach
at migration is likely to prevent your applications from taking advantage
of enhancements that are delivered by the new version. So, the recommendation for most cases
is to rebind the packages and specify the APREUSE(WARN) option instead.