PI40370: PERFORMANCE ISSUES FOR SQL INSERTS ON TABLES WITH APPEND=YES.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Performance issues for tables defined with APPEND=YES on the
  insert into the clustering index. The Index Lookaside processing
  is not used for SQL Insert because the code lost track of the
  index pages held.
  DB2 Index Manager processing will for an insert into the same
  index page do a Buffer Manager release page and getpage for
  each insert into the clustering index when APPEND=YES is set.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS and DB2 11 for z/OS      *
  *                 users of INSERT on the tables with APPEND    *
  *                 YES.                                         *
  ****************************************************************
  * PROBLEM DESCRIPTION: Users may experience slow performance   *
  *                      of INSERT on the tables with APPEND     *
  *                      YES that have clustering index.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Users may experience slow performance of INSERT on the tables
  with APPEND YES that have clustering index due to excessive
  getpages. It is because that DB2 performs get page on the
  clustering index page for every insert transaction even if
  the clustering keys are resides in the same index page. The
  optimization of clustering index look aside was not applied
  to the table defined with APPEND YES attributes.
  

Problem conclusion

• DB2 code has been modified to apply clustering key optimization
  process to avoid extra get pages for the current insert, if it
  is in the same index page.
  

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:

  UI31088 UI31089

Modules/Macros

• DSNISRTI
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI31088

     UP15/09/29 P F509

• RB10 PSY UI31089

     UP15/09/29 P F509

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.