APAR UPDATE – INSERT performance in critical DB2 9 for z/OS tables

Are you trying to improve INSERT performance in your critical DB2 9 for z/OS tables?

Many DB2 experts have given excellent presentations on this important subject, and there are several good techniques you can use to reduce the elapsed time for high-volume concurrent INSERT operations. One of the more popular in a data sharing environment is to specify PCTFREE = 0 and FREEPAGE = 0 on a MEMBER CLUSTER tablespace (also known as MC00). This optimisation was originally introduced in V8 (and DB2 V7 via APAR PQ87381) and caused DB2 to bypass the normal exhaustive space search and append rows directly to the end of the tablespace.

The advent of the APPEND table attribute in DB2 9 for z/OS provided an alternative option, performing a quick space search right at the end of the tablespace while also having the major advantage of being easy to switch on and off (via ALTER TABLE). Since DB2 9 became available both MC00 (MEMBER CLUSTER with PCTFREE = 0 and FREEPAGE = 0) and MCAPPEND (MEMBER CLUSTER with APPEND) caused DB2 to behave in the same way. However, some users still saw high GETPAGE activity and CPU utilisation, because DB2 was still performing an exhaustive space search before physically extending the tablespace. A relatively new fix (APAR PK81471) has changed the APPEND algorithm and causes DB2  to once again favour physical extend processing and abandon the exhaustive search. If you’re relying on APPEND for performance and don’t have this fix applied, it’s definitely worth taking a look.