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

Obtain the fix for this APAR.

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 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.