Managing and preventing access path change
You can prevent unwanted
access path changes for critical applications when you rebind applications
that contain static SQL statements and when DB2® prepares
dynamic SQL statements.
About this task
Query optimization depends on many factors, and even minor changes in the database environment might cause significant changes to access paths. DB2 uses the statistics that are stored in the DB2 catalog to determine the most efficient access paths during the bind process. When you reorganize your data, collect statistics, and rebind your packages or plans, DB2 can choose the most efficient access paths for your queries.
Because DB2 must often rely on incomplete information, such as statistics, suboptimal access paths are possible. Reoptimization sometimes yields access paths that cause performance regressions, including unnecessary I/O operations and excessive processor consumption, and even application outages.
DB2 also considers the following system and subsystem attributes during access path selection:
- Central processor model
- The number of central processors (for determining the appropriate degree of parallelism.)
- Buffer pool size, and other statistics
- RID pool size
These factors can change access paths for a statement from one system to another, even if all the catalog statistics are identical. So, you must account for these factors when you model your production systems on test systems, and when you model new applications. Mixed central processor models in a data sharing group might also affect access path selection.
- Managing access path change at migration from DB2 9
You can specify that DB2 tries to reuse existing access paths, and detect when access paths have changed, when you rebind your applications at migration. - Managing access paths at migration from DB2 Version 8
You can specify that DB2 retains the access paths from the previous version in case an access path change results in performance regression. - Managing access path changes for periodic maintenance
You
can detect and evaluate access path changes when you rebind after
organizing your data and collecting statistics, and when you apply
routine maintenance. You can also specify that DB2 tries to reuse existing access paths when
you rebind. You can also save information about existing access paths
when you rebind, and revert to previous access paths when performance
regressions occur. - 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. - Switching to previous access paths
You can save information about the previous access paths when you use a REBIND command to rebind your packages. When a new access path results in a performance regression, you can switch back to the previous access path.