PM31938: BAD PERFORMANCE AND TABLE SPACE GROWTH FOR SQL REFRESH STATEMENT WHEN USING PARTITION BY GROWTH WITH DSSIZE=2G/

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Performance degradation and table space growth when using a
  Partition By Growth (PBG) table space with DSSIZE=2G. If the
  DSSIZE is less than 64G then after the SQL Refresh does a mass
  delete the subsequent SQL Inserts skip reusing some of the
  table space partitions. This cause extra scans to find space and
  also uncontrol growth of the PBG table space. All partitions
  should be available for reuse when doing an SQL Refresh.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS users of partition-by-    *
  *                 growth table spaces (PBG) who tend to        *
  *                 perform mass deletes followed by inserts in  *
  *                 the same commit scope (or even SQL REFRESH   *
  *                 if the PBG houses a materialized query table *
  *                 (MQT))                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: Poor performance and excessive space    *
  *                      growth on PBG table space during        *
  *                      applications that perform mass delete   *
  *                      and insert in the same commit scope     *
  *                      (or SQL REFRESH if the table is an      *
  *                      MQT) and then repeat such sequences     *
  *                      over several commits                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  During mass delete on PBG, segment blocks were not being marked
  in a way that ensured their reuse after the commit. The reason
  for this was that the code was using a wrong internal flag, one
  which cannot be used to reflect mass delete at partition level.
  
  As a result, once mass delete/insert were being repeated after
  commit, DB2 skipped some of the free segments, not detecting
  that they were available for allocation. New partitions were
  being grown when they should not have been.
  

Problem conclusion

• Code is changed so that DB2 uses internal flags that track
  mass delete at partition level, which will ensure that segment
  blocks are properly marked and thus reused during insert after
  commit.
  
  Additional keywords: SQLPBG SQLMQT SQLDELETE SQLINSERT
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK65102

Modules/Macros

• DSNISEGD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK65102

     UP11/03/09 P F103

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.