 |
Software > Information Management > DB2 Product Family >
|
|
|
|
Guarding against access path regressions when migrating to Version 8 DB2 for z/OS™
|
| | | Problem | | This document outlines the access path regressions that are experienced when migrating from Version 7 to Version 8. | | | | Cause | | The causes of the problem include changes to optimizer code, changes to catalog statistics, changes to z-parm or pool settings, and changes to CP speed. | | | | Solution | The recommended process for migrating plans or packages where the access path is critical is described below. Any of the three processes will provide better capability to fallback to the Version 7 access paths in case any degradations occur on Version 8. These processes are general to any DB2 version migration, and are not specific to Version 8. It is anticipated that the majority of access paths will improve in Version 8, or at least be equivalent to Version 7.
The goal is to at least maintain a copy of the Version 7 access paths in the PLAN_TABLE, a collection containing packages bound in Version 7, and a collection of packages bound in Version 8. A Version 7 collection is needed, in addition to the Version 8 collection, to allow fallback to the prior collection, and the PLAN_TABLE output is required if you plan to use OPTHINTS or simply to compare Version 7 with Version 8 access paths.
Option A:
1. Bind the affected plans/packages to a new Version 8 collection (using any collection name you wish), specifying EXPLAIN(YES) so that you can compare them with your current Version 7 access paths, that are in existence in the PLAN_TABLE.
2. In the package list, place the Version 8 collection before the Version 7 collection.
3. If you run into an access path problem, free that package from the Version 8 collection, allowing the next execution to pickup the Version 7 collection. This provides an immediate solution to the performance problem, and allows more time to analyze the access path regression.
Option B:
This approach is similar to Option A, though that it uses a proactive, rather than reactive approach to identify access path regressions. Keep two Version 8 collections, and only move these into the Version 8 collection that occurs first in the package list once you are certain that the query performance in Version 8 is the same or better than that in Version 7.
Option C:
If you do not have your existing Version 7 access paths saved in the PLAN_TABLE, then another alternative is to BIND (to a separate collection) or REBIND (the existing collection) in Version 7 with EXPLAIN(YES), so that Version 7 access paths are known. Note: There is no guarantee that a new Version 7 bind/rebind will choose the exact same path as the current production access path. A new bind (or rebind) in Version 8 should use the proactive (option B) or reactive (option A) approach to ensure that a fallback to the Version 7 collection is possible in the package list.
In general, OPTHINTs should only be used when DB2 does not choose the desired access path after all relevant statistics are made available via RUNSTATS. However, it is sometimes necessary to migrate a Version 7 hint to Version 8. In this situation, the user should be sure to have these Version 8 OPTHINT APARs applied:
PK02481
PK07750
PK10015
These fixes cover various OPTHINTs problems, some of which deal specifically with migrating hints from Version 7 to Version 8. | | | | | | | |
|
|
|
| IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml. |
|
|
|
|
| Please take a moment to complete this form to help us better serve you. |
|
 |
|
|
|
|
|
|
| Product categories: |
|
| | Software | |
| | Data Management | |
| | Data Servers (Database Management Systems) | |
| | DB2 for z/OS | |
| | Performance | |
|
| Operating system(s): |
| |
OS/390, z/OS
|
|
| Software version: |
| |
7.0, 8.0
|
|
| Reference #: |
| |
1231882
|
|
| IBM Group: |
| | Software Group |
|
| Modified date: |
| | 2006-03-06 |
|
|
