IBM Support

PK51734: SUBOPTIMAL ACCESS PATH CHOSEN WHEN INDEX ONLY ACCESS IS AVAILABLE

A fix is available

 

APAR status

  • Closed as program error.

Error description

  • SUBOPTIMAL ACCESS PATH CHOSEN WHEN INDEX ONLY ACCESS IS
    AVAILABLE
    .
    SQLPERFORMANCE SQLACCESSPATH

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: For all DB2 for z/OS users who have indexes *
    * that can do index-only on some queries. *
    ****************************************************************
    * PROBLEM DESCRIPTION: An index which can do index-only and *
    * has better overall filtering can lose *
    * to an index which needs to access data *
    * during access path selection. *
    ****************************************************************
    * RECOMMENDATION: *
    ****************************************************************
    An index which can do index-only and has better overall
    filtering can lose to an index which needs to access data
    during access path selection. As a result, poor query
    performance can occur.

    The primary cause when this happens is that the 'good' index
    has more levels than the 'bad' index. Now, index preference
    logic is added to favor the 'good' index.

    Additional Keywords:
    SQLACCESSPATH SQLINDEX INDEXONLY

Problem conclusion

  • Index preference logic is introduced under zparm OPTIXOPREF
    to give a greater preference towards the 'good' index over
    the 'bad' index in the situation described above. Zparm
    OPTIXOPREF must be turned ON to enable these changes.

Temporary fix

Comments

APAR Information

  • APAR number

    PK51734

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    810

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-08-24

  • Closed date

    2007-11-29

  • Last modified date

    2008-01-02

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK31769 UK31770

Modules/Macros
DSN@ZP DSNDQWPZ DSNDSPRM DSNWZP DSNXMOPC DSNXOCSC
DSNXOMPS DSN6SPRM        

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R810 PSY UK31769

       UP07/12/22 P F712

  • R910 PSY UK31770

       UP07/12/22 P F712

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.

Document information

More support for: DB2 for z/OS

Software version: 810

Reference #: PK51734

Modified date: 02 January 2008


Translate this page: