A fix is available
APAR status
Closed as new function.
Error description
Provide an option to adjust the CPU/IO cost balance for use by optimizer. . SQLACCESSPATH SQLPERFORMANCE
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: For all DB2 for z/OS users who are * * experiencing slow query performance * * because DB2 has overestimated I/O costs * * for the query, especially those users * * who have faster processor speeds. * **************************************************************** * PROBLEM DESCRIPTION: Cost estimation for I/O cost of a query * * has become inaccurate as processor * * speeds have increased. * **************************************************************** * RECOMMENDATION: * **************************************************************** DB2 for z/OS has been using a constant unit of time for I/O cost estimation since V2. At the same time, the CPU cost estimation is based on the CPU MIPS ratio. When DB2 estimates the cost of a query, it assumes no data has been cached into the buffer pool yet, so the I/O cost is overweighted. Consequently, DB2 tends to choose a more CPU-intensive access path. The I/O and CPU cost imbalance issue has been exposed more with the improvement of the System z processor speed. Additional Keywords: SQLACCESSPATH SQLPERFORMANCE
Problem conclusion
Temporary fix
Comments
A new zparm OPTIOWGT is added to change the cost balance in DB2 when selecting an access path. OPTIOWGT is a switch. When the switch is turned on, DB2 uses new weights in cost estimation to better reflect the effects of improved I/O response and DASD caching; otherwise, DB2 keeps the cost balance unchanged. See the HOLD text for information on how to set or change the OPTIOWGT parameter.
APAR Information
APAR number
PK61277
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
910
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-02-19
Closed date
2008-08-21
Last modified date
2008-10-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK39140
Modules/Macros
DSN@ZP DSNDQWPZ DSNDSPRF DSNDSPRM DSNTIDXA DSNTIJUZ DSNTINST DSNT1RSP DSNWZP DSNXMOPC DSNXOMPS DSN6SPRC DSN6SPRM
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
R910 PSY UK39140
UP08/09/06 P F809
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 October 2008