PI38798: INDEX SELECTION PROBLEM MAY OCCUR WHEN QUERY USES FETCH FIRST FOR N ROWS OR OPTIMIZE FOR N ROWS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When query contains FETCH FIRST N ROWS or OPTIMIZE FOR N ROWS,
  an index with less matching columns or non matching index scan
  may be used even when there's other index provides more matching
  column.
  

Local fix

• OPTHINT
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 11 for z/OS users of queries with the    *
  *                 OPTIMIZE FOR N ROWS, FETCH FIRST N ROWS      *
  *                 ONLY clauses or EXISTS subqueries.           *
  ****************************************************************
  * PROBLEM DESCRIPTION: An inefficient index may be selected    *
  *                      for a query with OPTIMIZE FOR N ROWS,   *
  *                      FETCH FIRST N ROWS ONLY clause or an    *
  *                      EXISTS subquery.                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An inefficient index may be selected for a query with OPTIMIZE
  FOR N ROWS, FETCH FIRST N ROWS ONLY clause or an EXISTS
  subquery while there is another index which could provide better
  performance.
  
  This problem may occur because the DB2 optimizer incorrectly
  favors the inefficient index when the inefficient index is
  created before the efficient index.
  
  Additional Keywords:
  SQLACCESSPATH  SQLPERFORMANCE  SQLOFNR  SQLFFNR  SQLEXISTS
  

Problem conclusion

• DB2 code has been modified to correctly select the index whose
  cost is cheaper.
  

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:

  UI28502

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI28502

     UP15/07/03 P F507

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.