Rebinding packages when access path reuse fails

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. However, access path reuse under the APREUSE(ERROR) bind option is an all-or-nothing proposition at the package level.

Reuse sometimes fails when a hint can be applied only partially. The result might be an access path that matches neither the existing access path nor the new access path that DB2® chooses, when reuse is not applied. For that reason, reuse succeeds for all matching statements access paths, or the rebind option fails for the entire package.

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 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.
1. 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.
  Important: When reuse fails, the resulting PLAN_TABLE information reflects the access paths that result from the failed application of the underlying hints. Therefore, the values do not represent the previous access paths or the new access path that DB2 selects when reuse is not applied. To see the new access paths that DB2 uses for statements when no reuse is applied, you can specify APREUSE(NONE) and EXPLAIN(ONLY) to populate the PLAN_TABLE with values that describe the new access paths that result from clean optimizations.
2. Analyze the PLAN_TABLE records for the new access paths, and determine whether the new access paths are acceptable.
3. If the new access paths are acceptable, reissue the command to rebind the packages and specify APREUSE(NONE). DB2 generates new access paths for all statements in the packages.
Rebind the package and accept new access paths for the reuse failures, but create hints to enforce existing access paths for some statements in the package. This advanced approach might enable you to enforce the existing access paths for some statements when not all access paths can be reused. However, it requires a significant amount of effort for detailed analysis and implementation. Care must be taken to identify access paths that cannot be reused, and to exclude those statements when you create the hints:
1. Reissue the command to rebind the package and specify the APREUSE(ERROR) and EXPLAIN(ONLY) options. The rebind operation is not completed, but DB2 populates the PLAN_TABLE with information about the reuse failures.
2. Analyze the PLAN_TABLE data to identify the statements that encountered reuse failures, and determine whether access path changes can be tolerated for those statements. When reuse fails, the resulting PLAN_TABLE information reflects the access paths that result from the failed application of the underlying hints. Therefore, the values do not represent the previous access paths or the new access path that DB2 selects when reuse is not applied.
3. Reissue the command to rebind the packages and specify APCOMPARE(WARN) and EXPLAIN(ONLY) options. The rebind operation is not completed, but DB2 populates the PLAN_TABLE with information about the new access paths.
4. Analyze the PLAN_TABLE data to evaluate the new access paths for the statements that encountered reuse failures. You might even find that DB2 chooses the same access path for a statement through normal optimization, even though reuse could not be enforced.
5. Use the PLAN_TABLE information to create hints for the access paths that can be reused in the package.
  Important: Do not create hints to try to enforce access paths for statements that encounter reuse failures. Such hints are likely to be only partially applied, if at all. They are likely to result in access paths that do not match either the existing access path or the new access path that DB2 chooses.
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.