PM56845: PROVIDE OPTION FOR OPTIMIZE FOR 1 ROW TO ALLOW SOME SORT ACCESS PATHS

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • In all versions of DB2, the OPTIMIZE FOR 1 ROW clause
    requests DB2 to choose an access path that avoids a sort.  In
    DB2 versions prior to 10, there is a possibility to obtain an
    access path with a sort even though that path is strongly
    discouraged.  In DB2 10, DB2 will not compete access paths with
    sorts and will instead choose the lowest cost access path that
    does not require a sort.
    
    This APAR provides an option to return to the previous version
    OPTIMIZE FOR 1 ROW behavior.  As such, it does not eliminate
    the risk of an inefficient access path being chosen with
    OPTIMIZE FOR 1 ROW when the efficient access requires a sort.
    However, it does limit that exposure to what already existed in
    DB2 prior to DB2 10.
    
    For queries that need sorts, the recommended solution is to
    avoid coding the OPTIMIZE FOR 1 ROW clause.  Without the
    OPTIMIZE FOR 1 ROW clause, DB2 will choose access paths based on
    cost and will not make an effort to avoid sorts.
    

Local fix

  • For queries that need sorts for efficient access, the solution
    is to avoid coding the OPTIMIZE FOR 1 ROW clause.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 10 for z/OS users whose queries utilize  *
    *                 the OPTIMIZE FOR 1 ROW clause.               *
    ****************************************************************
    * PROBLEM DESCRIPTION: DB2 may choose an inappropriate access  *
    *                      path when the OPTIMIZE FOR 1 ROW clause *
    *                      is used.                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    In all versions of DB2, the OPTIMIZE FOR 1 ROW clause requests
    DB2 to choose an access path that avoids a sort if one exists.
    In DB2 versions prior to DB2 10, there is a possibility to
    obtain an access path with a sort even though that access path
    choice is strongly discouraged.  In DB2 10, when an access path
    exists that does not require a sort, DB2 will not compete
    access paths with sorts.  Instead DB2 will choose the lowest
    cost access path that does not require a sort.
    
    This APAR provides an option to return to the previous
    OPTIMIZE FOR 1 ROW behavior prior to DB2 10.  As such, it does
    not eliminate the risk of an inefficient access path being
    chosen with OPTIMIZE FOR 1 ROW when the efficient access path
    requires a sort.  However, it does limit that exposure to what
    already existed in DB2 prior to DB2 10.
    
    This APAR introduces ZPARM OPT1ROWBLOCKSORT to control the
    behavior of the OPTIMIZE FOR 1 ROW clause.  When determining
    how to set this ZPARM consider the following...
    
      * OPT1ROWBLOCKSORT=ENABLE guarantees that an access path
        without a sort will be chosen if one exists.  As such,
        if an index exists to support the requested order, DB2
        may use this index to avoid a sort even if this means
        scanning all the data.  In some cases, this may yield an
        inefficient access path.
    
      * OPT1ROWBLOCKSORT=DISABLE allows DB2 to consider access
        paths that require a sort.  However, these access path
        choices will be strongly discouraged.  This is the
        behavior present in DB2 9 and previous releases.  If the
        goal of coding the OPTIMIZE FOR 1 ROW clause is to disable
        a sort or if the best access path requires a sort, this may
        yield an inefficient access path.
    
      * With either zparm setting, DB2 will continue to prefer
        access paths without sorts for queries with the OPTIMIZE
        FOR 1 ROW clause.  If this is not the desired behavior, the
        application should either remove the OPTIMIZE FOR 1 ROW
        clause or change it to OPTIMIZE FOR n ROWS where n is
        greater than 1.
    
      * The OPTIMIZE FOR 1 ROW clause will avoid access paths
        containing list prefetch and multi-index access.  This
        behavior is not changed with either zparm setting.
    
      * Sometimes materialization and sorts are unavoidable.  In
        these cases, the OPTIMIZE FOR 1 ROW clause can be ignored.
        This behavior is not changed with either zparm setting.
    
    For queries that need sorts, the recommended solution is to
    avoid coding the OPTIMIZE FOR 1 ROW clause.  Without the
    OPTIMIZE FOR 1 ROW clause, DB2 will choose access paths based on
    cost and will not make an effort to avoid sorts.
    
    Additional Keywords:
    SQLACCESSPATH SQLPERFORMANCE SQLOFNR
    

Problem conclusion

  • Code is changed to provide an option to revert to the behavior
    of DB2 9 and earlier versions of DB2 for OPTIMIZE FOR 1 ROW.
    As with any change to DB2 query optimization that can drive a
    different access path, there is some risk of query performance
    regression.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM56845

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-01-27

  • Closed date

    2012-03-30

  • Last modified date

    2012-05-02

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

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

    UK77500

Modules/Macros

  •    DSN@XAZP DSNDQWPZ DSNDSPRM DSNTIDXA DSNTIDXB
    DSNTIJUZ DSNTINST DSNTXAZP DSNWZIFA DSNXOGP  DSNXOMPS DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK77500

       UP12/04/17 P F204

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM56845

Modified date:

2012-05-02

Translate my page

Machine Translation

Content navigation