You are currently on IBM Systems Media’s archival website. Click here to view our new website.

Bookmark and Share

Recent Posts

DB2 12 REORG Enhancements

January 31, 2017

The REORG utility is one of the most important tools in the DBA toolbox. Traditionally, the REORG table space or index utility is used to reorganize the data to improve access performance as well as reclaim fragmented space.

The reason I used the word "traditionally" in the previous sentence is because the uses of REORG are evolving. While the overall need to run REORGs continues to diminish, it is becoming more common to run REORG to support online schema changes -- which involve altering a DB2 object but deferring changes until an online REORG is completed.

If you're wondering about changes to REORG in the new DB2 12 release, check out this document from The DB2 12 Knowledge Center:

With DB2 12, IBM continues to emphasize improved performance and availability. There's additional zIIP offload as well as availability with improved partition level PBG REORGS and avoidance of copy pending with LOB table spaces of new PBG partitions.

Here's another key enhancement that I just learned about: REORG is 100 percent zIIP eligible in DB2 12. The REORG RELOAD phase is now supported, meaning all processes within REORG can be utilized, regardless of REORG type or the parameters used. This affords customers potentially huge savings in CPU, which can dramatically reduce the cost of running a REORG.

What follows is taken from the Knowledge Center doc referenced above:

Improved FlashCopy management
  • DB2 11 REORG had an issue when FlashCopy failed the REORG job would end with a return code of zero “RC=0” even when the FlashCopy failed. All objects being reorganized would be left in COPY pending status.  
  • DB2 12 fixed this issue by returning “RC=8” when the FlashCopy Imagecopy failed.  The status of the objects are left in RW status and the system is left in a state as if the utility never ran. No switch, no COPY or UT status, no TERM UTIL required.
This applies to the REORG when using SHRLEVEL REFERENCE or CHANGE.

Improved partition-level partition-by-growth (PBG) REORGs
  • DB2 11 REORG; When reorganization a range of partitions such as REORG PART 3:4, would fail with ABEND04E RC00E40318 when the data would not fit in partition 3 and 4.  
  • DB2 12 REORG will allocate a new partition to hold the overflow of data. The job will end with RC=0, and a new partition is allocated to hold the overflow of data. When the job is completed, partition 3 and 4 will be reorganized and data that would not fit in these partitions will overflow to new partition 5.
Prevention of COPY-pending on a LOB table space during REORG of PBGs
  • DB2 11 REORG is unable to create a sequential image copy for a LOB table space created due to PBG growth.
  • DB2 12 REORG can support creating a sequential image copy for the newly created LOB table space.
REORG-level management of delete of PBG partitions
  • DB2 11 REORG; The PBG is currently using 5 partitions. After the REORG the first two partitions have data and partitions 3, 4 and 5 are empty. The partitions will stay allocated and space is wasted with empty partitions.
  • DB2 12 REORG: When REORG is run at the table space level for all partitions and using the parameter DROP_PART YES, partition 3, 4 and 5 which are empty after the REORG will be dropped to reclaim space. When the DROP_PART parameter is not provided the behavior defaults to the subsystem parameter REORG_DROP_PBG_PARTS (DISABLE, ENABLE) with default value “DISABLE.”
Support for the new COMPRESSRATIO catalog column
  • DB2 12 introduced a new column COMPRESSRATIO for catalog tables SYSIBM.SYSTABLESPACE and SYSIBM.SYSTABLEPART. The purpose of this column is to improve the accuracy for calculating sort work data sets.
  • COMPRESSRATIO gets the average percentage of bytes saved by compression on each compressed data record in the table space when the table space is defined with the COMPRESS YES attribute. The value is based on an average row length and varies depending on the actual length of the data rows.
  • REORG, LOAD REPLACE, and RUNSTATS will automatically update this new column.
Additional offload to zIIP
  • The RELOAD phase is now zIIP eligible.
  • The objective of the enhancement is to reduce the cost of running REORG by offloading CPU time to the zIIP processor. The phases of the utilities have been enabled over time:
    • V8 Index build in LOAD, REORG and REBUILD
    • V10 RUNSTATS
    • V11 Inline stats
New mapping table format support
  • DB2 12 requires a new format in the mapping table to support the new 7-byte RID which is required for partition-by-range (PBR) relative-page-number (RPN).
REORG against RO pagesets
  • Prior to DB2 12, the REORG utility failed when executed against a read-only table space or index space. With the increase of online schema evolution brings the need to alter and REORG these objects in read-only mode.
  • DB2 12 allows REORG utility execution against read-only objects.
Display of claimer information
  • Up to DB2 11, REORG was not able to display the blocking claimers information on drain failures except on the last drain attempt. It caused REORG termination with no possible action taken.
  • DB2 12 displays the blocking claimers on each drain attempt failure in the utility job output for REORG SHRLEVEL REFERENCE or CHANGE.
  • The display information can provide time to react and possible terminate the agent which is blocking the drain to avoid having the REORG fail.

Posted January 31, 2017| Permalink