PI53036: FETCH FIRST n ROWS IS INCORRECTLY PROCESSED WHEN A SORT IS INVOKED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When the data row size for a sort is larger than a 4K page,
  DB2 is incorrectly processing the output workfile and
  not honoring FETCH FIRST n ROWS clause. Therefore, overhead
  is incurred with the additional rows being sorted.
  
  Additional Keywords:
  SQLPERFORMANCE
  PERFM
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of queries that    *
  *                 contain the FETCH FIRST n ROWS clause and    *
  *                 the data row size for SORT exceeds 4000      *
  *                 bytes.                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using the FETCH FIRST n ROWS       *
  *                      clause, if the data row size for        *
  *                      sort processing exceeds 4000 bytes,     *
  *                      DB2 sort incorrectly processes 'more    *
  *                      rows than needed' and does not honor    *
  *                      the FETCH FIRST n ROWS clause.          *
  *                      Therefore, extra CPU overhead is        *
  *                      incurred during sort processing because *
  *                      additional rows are processed.          *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When the data row size for sort exceeds 4000 bytes, DB2 sort
  (via TAG SORT) incorrectly processes 'more rows than needed' and
  does not honor the FETCH FIRST n ROWS clause.  As a result,
  because additional processing overhead is incurred during sort,
  a given query's performance can be less than desired.
  
  The problem only occurs during the input phase of sort when
  determining it is able to process such a large data row within
  memory.
  

Problem conclusion

• DB2 has been modified to honor the FETCH FIRST n ROWS when sort
  can process this large data row in memory during the input
  phase.
  
  Additional Keywords: DB2SORT SQLPERFORMANCE PERFM
  

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:

  UI34329

Modules/Macros

• DSNXSTSE
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI34329

     UP16/01/23 P F601

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.