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