PK67301: V9 DGTT INCREASED ELAPSED TIME COMPARED TO V8 IN MULTIPLE INSERT/MASS DELETE SEQUENCES IN THE SAME COMMIT SCOPE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• V9 DECLARED GLOBAL TEMPORARY TABLE (DGTT) increased elapsed time
  compared to V8 was encountered, when there are multiple insert/
  mass delete sequences in the same COMMIT scope.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Declared Global Temporary Tables    *
  *                 (DGTT) doing repeated mass-deletes in the    *
  *                 same commit scope                            *
  ****************************************************************
  * PROBLEM DESCRIPTION: Increased elapsed time in V9 compared   *
  *                      to V8 when processing many insert/mass  *
  *                      delete sequences in the same commit     *
  *                      scope on Declared Global Temporary      *
  *                      Tables (DGTT)                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Applicable to all V9 Declared Global Temporary Tables (DGTT)
  regardless of the ON COMMIT option, in repeated sequences of
  inserts/mass-deletes in the same commit scope, logging and
  elapsed time in general were unnecessarily high, since already
  logged mass-deleted segments were being logged again on each
  subsequent mass-delete.
  
  Secondly, applicable to DGTT with ON COMMIT DELETE ROWS (the
  default), segments were not being deallocated at commit. This
  resulted in the space in workfile database being excessively
  used.
  
  It should be noted that this second problem indirectly added to
  the first problem described above (high logging and elapsed
  time on mass-deletes) in ON COMMIT DELETE ROWS tables, because
  the DGTT segment chain which remained allocated at commit would
  quickly get marked as mass-deleted on the very first mass-delete
  after the commit. A subsequent insert in the same commit scope
  as the mass-delete would have to allocate a brand new segment,
  growing the chain further, which added to the level of logging
  and the elapsed time.
  

Problem conclusion

• Code has been corrected to not write log records during DGTT
  mass-delete when traversing already logged mass-deleted
  segments.
  
  Also, code has been corrected to deallocate segments when all
  rows are deleted at commit for DGTT with ON COMMIT DELETE ROWS.
  
  Additional keyword: SQLDGTT
  

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:

  UK38947

Modules/Macros

• DSNDCT   DSNICMT2 DSNISEGD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK38947

     UP08/08/30 P F808

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.