PH58491: QUERY WITH SELECT DISTINCT AND OPTIMIZE FOR 1 ROW/FETCH FIRST 1 ROW CLAUSE,DB2 MAY FAVOR 'O' ACCESS WHICH CAUSE POOR PERFORMANCE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• For query with SELECT DISTINCT and OPTIMIZE FOR 1 ROW / FETCH
  FIRST 1 ROW clause, Db2 may favor 'O' access on a table
  expression incorrectly when no index is available to avoid sort
  to process SELECT DISTINCT. This may cause performance
  degradation.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 and Db2 13 for z/OS                      *
  * running queries (1) with OPTIMIZE FOR N                      *
  * ROW or FETCH FIRST N ROW clause, and                         *
  * (2) with GROUP BY or ORDER BY                                *
  * or SELECT DINSTINCT clause, and                              *
  * (3) with the FROM clause referencing                         *
  * views or table expressions.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Sometimes Db2 may choose PLAN_TABLE.                         *
  * ACCESSTYPE='O' on a view or a table                          *
  * expression when the following                                *
  * conditions are met.                                          *
  * (1) The query contains OPTIMIZE FOR                          *
  * N ROW or FETCH FIRST N ROW clause,                           *
  * and                                                          *
  * (2) The query contains GROUP BY or                           *
  * ORDER BY or SELECT DISTINCT clause,                          *
  * and                                                          *
  * (3) The FROM clause references                               *
  * views or table expressions, and                              *
  * (4) The previous join result set                             *
  * is huge, and                                                 *
  * (5) The sort for (2) can't be avoided                        *
  * by using any indexes.                                        *
  * When the conditions above are met,                           *
  * PLAN_TABLE.ACCESSTYPE='R' may provide                        *
  * better performance.                                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Sometimes Db2 may choose PLAN_TABLE.ACCESSTYPE='O' on a view
  or a table expression when the following conditions are met.
  (1) The query contains OPTIMIZE FOR N ROW or FETCH FIRST N
  ROW clause, and
  (2) The query contains GROUP BY or ORDER BY or SELECT
  DISTINCT clause, and
  (3) The FROM clause references views or table expressions,
  and
  (4) The previous join result set is huge, and
  (5) The sort for (2) can't be avoided by using any
  indexes.
  When the conditions above are met, PLAN_TABLE.ACCESSTYPE='R'
  may provide better performance. PLAN_TABLE.ACCESSTYPE='O' is
  chosen because Db2 may apply cost reduction due to the
  OPTIMIZE FOR N ROW or FETCH FIRST N ROW clause sometimes.
  

Problem conclusion

• PH58491 will detect some scenarios when no indexes can be used
  to avoid the sort access plan for the GROUP BY or ORDER
  BY or SELECT DISTINCT clause processing. When the scenarios
  are detected, no cost reduction will be applied due to the
  OPTIMIZE FOR N ROW or FETCH FIRST N ROW clause.
  SQLWHERE SQLORDERBY SQLGROUPBY SQLDISTINCT SQLCORRELATION
  SQLOFNR SQLFFNR SQLVIEW SQLTABLEEXPR SQLPERFORMANCE
  SQLACCESSPATH
  

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:

  UI95667 UI95668

Modules/Macros

• DSNXOCS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI95667

     UP24/02/21 P F402

• RD10 PSY UI95668

     UP24/02/21 P F402

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.