PI22266: RESTORE SORT AVOIDANCE COST REDUCTION FOR OPTIMIZE FOR 1 ROW

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This apar addresses the problem of sort avoidance being excluded
  from a query which has the following charcateristics:
  * the query uses OPTIMIZE FOR 1 ROW and the composite table is
  very small
  * the table is in a PBR universal tablespace
  * the table has a clustered DPSI index defined to it whose
  column order matches the column order of the ORDER BY statement
  in the query
  The query highlites 2 problems:
  * the reduction logic for OPTIMIZE FOR 1 ROW with a small
  composite cardinality that existed prior to V11 has been removed
  and must be reestablsihed
  * the cost calculation for the DPSI merge is overstimated due to
  the estimated number of qualified partitions being inaccurate
  

Local fix

• Have customer set OPT1ROWBLOCKSORT=ENABLE in DSNZPARMs
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users whose SQL contains *
  *                 FETCH FIRST 1 ROW ONLY or OPTIMIZ FOR 1 ROW  *
  *                 clause.                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: Inefficient access path with sort was   *
  *                      chosen when the query or query block    *
  *                      contains FETCH FIRST 1 ROW ONLY         *
  *                      OPTIMIZE FOR 1 ROW clause.              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 may choose an inefficient access path with sort for a
  query or query block with FETCH FIRST 1 ROW ONLY or OPTIMIZE
  FOR 1 ROW clause.
  
  Additional keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLOF1R SQLFF1R
  

Problem conclusion

• DB2 has been modified to choose an access path without sort if
  the query or query block contains FETCH FIRST 1 ROW ONLY or
  OPTIMIZE FOR 1 ROW clause.
  

Temporary fix

Comments

APAR Information

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

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

  UI22577

Modules/Macros

• DSNXOGP  DSNXOPNR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI22577

     UP14/11/13 P F411

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.