Start of change

Reusing and comparing access paths at bind and rebind

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

Begin program-specific programming interface information.
The following prerequisites have been met:
  • The package that contains the SQL statements was created in DB2 9 or later.

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.

Start of changeHowever, 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. End of change

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(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. Start of changeUnder 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.End of change
  • Specify the APCOMPARE(WARN) option. Start of changeThis 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) 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.
    End of change

    However, when the bind operation is complete, DB2 issues a DSNT285I message that indicates how many access paths are changed after the rebind operation.

  • Start of changeSpecify the EXPLAIN(YES) option in combination with APREUSE(ERROR) and APCOMPARE(ERROR) or (WARN).End of change You can use EXPLAIN output to analyze the access paths in more detail.
    Important: Start of changeWhen 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.End of change
    DB2 inserts information about reuse and comparison failures into the PLAN_TABLE.REMARKS column.
  • Start of changeSpecify the EXPLAIN(ONLY) option in combination with the APREUSE(ERROR) or an APCOMPARE option.End of change This approach enables you to learn whether access path changes occur before you actually bind or rebind the packages.
    Important: Start of changeWhen 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.End of change
    DB2 processes all statements in the packages, and inserts information about any failures in the PLAN_TABLE.REMARKS column. However, the bind or rebind operation is not completed. Therefore, under EXPLAIN(ONLY), both APCOMPARE(ERROR) and APCOMPARE(WARN) have the same effect, because the new packages are not created.

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
Important: Start of changeWhen 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.End of change
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 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.
End program-specific programming interface information.
End of change