IBM Support

PK61277: PROVIDE AN OPTION TO ADJUST THE CPU/IO COST BALANCE FOR USE BY OPTIMIZER

A fix is available

Subscribe

You can track all active APARs for this component.

 

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