PH41952: DB2 MAY CHOOSE DIRECT INDEX ACCESS OVER INDEX PLAN WITH SORT WITH LOWER COST 22/01/05 PTF PECHANGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Db2 may choose direct index access over index plan with sort
  with lower cost when the query meets the following conditions:
  (1) It's more than 2 tables join,
  (2) The query contains FETCH FIRST N ROWS clause or OPTIMIZE FOR
  N ROWS,
  (3) The query has GROUPBY or ORDERBY clause.
  
  ADDITIONAL SYMPTOMS:
  SQLOFNR SQLFFNR SQLACCESSPATH SQLPERFORMANCE SQLGROUPBY
  SQLORDERBY SQLJOIN SQLDYNSTMTCACHE
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * 1. All users of Db2 12 for z/OS who have                     *
  * queries with GROUPBY or ORDERBY                              *
  * and FETCH FIRST N ROWS or OPTIMIZE FOR                       *
  * N ROWS clause.                                               *
  * 2. Have PH33128/UI73918 applied                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may choose direct index access                           *
  * over index plan with sort with lower                         *
  * cost when the query meets the                                *
  * following conditions:                                        *
  * (1) It's more than 2 tables join.                            *
  * (2) The query contains FETCH FIRST N                         *
  * ROWS clause or OPTIMIZE FOR N ROWS.                          *
  * (3) The query has GROUPBY or ORDERBY                         *
  * clause.                                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  If a query contains two or more tables with GROUP BY/ORDER BY
  and FETCH FIRST N ROWS/OPTIMIZE FOR N ROWS is involved in the
  query, Db2 may select an index that can support GROUP BY/ORDER
  BY while an index with sort can provide better performance.
  Example:
  SELECT * FROM T1, T2
  WHERE T1.C1=?
  AND T1.C3=T2.C3
  ORDER BY T1.C2
  FETCH FIRST 10 ROWS ONLY
  ;
  There are two indexes IX1(C1), IX2(C2) on table T1. The index
  IX2(C2) can support the ORDER BY. Db2 may access T1 by index
  IX2(C2) then join T2. But accessing T1 by index access IX1(C1)
  can provide better performance.
  

Problem conclusion

• Additional keywords:
  SQLACCESSPATH SQLPERFORMANCE SQLORDERBY SQLGROUPBY SQLOFNR
  SQLFFNR SQLJOIN
  

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:

  UI78851

Modules/Macros

• DSNXOPCO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI78851

     UP22/01/15 P F201 {

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.