LI73161: Possible Poor Performance of Queries with FFNR/OFNR and DISTINCT

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• n rare circumstances a suboptimal query access plan may be
  selected for queries with all of the following properties:
  
  -         The query includes a FETCH FIRST N ROWS or OPTIMIZE
  FOR N ROWS clause
  
  -         The query does not include an ORDER BY or GROUP BY
  
  -         The query includes one DISTINCT clause
  
  -         The DISTINCT includes columns from multiple tables
  
  -         The join that links these tables is on at least one of
  these columns
  
  -         An index exists on one of the columns in the join and
  the DISTINCT.
  
  -         The order provided by the index is only useful in the
  join and in the DISTINCT
  
  -         On version 9.1 and below, the optimization level is 5
  or below. On version 9.5 and above, the optimization level is 7
  or below.
  

Local fix

• This APAR provides a configuration setting which fixes this
  issue. Due to the risks involved, customers who believe they
  have encountered the issue described above should engage DB2
  Support.
  

Problem summary

• Possible Poor Performance of Queries with FFNR/OFNR and DISTINCT
  

Problem conclusion

• First fixed in DB2 UDB Version 9.1, FixPak 5
  

Temporary fix

• This APAR provides a configuration setting which fixes this
  issue. Due to the risks involved, customers who believe they
  have encountered the issue described above should engage DB2
  Support.
  

Comments

APAR Information

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

  LI73160

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

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSY

     UP