Differences between REORG PLUS and the IBM Db2 REORG utility

Authorization

REORG PLUS does not run as part of the Db2 subsystem. Therefore, to use REORG PLUS, you must have system authorization similar to that required by Db2. An exception is if you are using the IBM Resource Access Control Facility (RACF) component of the z/OS Security Server and REORG PLUS is installed with OPNDB2ID=YES.

Multiple reorganizations

REORG PLUS allows only one REORG command in the input data set (SYSIN). Therefore, you must run separate job steps to execute multiple reorganizations on different table spaces.

UNLOAD ONLY option

Reorganization phases

The SORT and BUILD phases of the Db2 REORG utility are combined into the RELOAD phase in REORG PLUS (two-phase reorganization), or into the REORG phase (single-phase reorganization).

Indexes

To restore the clustering order of rows, REORG PLUS always sorts the unloaded rows (like the SORTDATA option of the Db2 REORG utility) and has no option to unload via the clustering index. This processing requires sufficient sort work space to sort not only the index keys, but all rows of the largest partition (or all rows of the table space if the table space is not partitioned).

Start/stop status

For SHRLEVEL NONE (the default), REORG PLUS stops the table space and index spaces that you are reorganizing at the beginning of unload processing. The space remains stopped throughout the job. For a partial reorganization, REORG PLUS stops and starts only partitions that you specified with the PART option of the REORG command. REORG PLUS stops and starts nonpartitioned indexes in their entirety.

For SHRLEVEL REFERENCE UNLOADONLY, REORG PLUS starts the table space and index spaces that you are reorganizing in RO status at the beginning of the UNLOAD phase. REORG PLUS later stops the spaces at the beginning of the RELOAD phase, and they remain stopped until the end of the job. For a partial reorganization, REORG PLUS stops and starts only partitions that you specified with the PART option of the REORG command. REORG PLUS stops and starts nonpartitioned indexes in their entirety.

For SHRLEVEL REFERENCE, REORG PLUS starts the table space and index spaces that you are reorganizing in RO status. The spaces remain in RO status until they are stopped at the beginning of the UTILTERM phase. For a partial reorganization, REORG PLUS stops and starts only those partitions that you specified with the PART option of the REORG command. REORG PLUS stops and starts nonpartitioned indexes in their entirety.

Multiple volumes

For multiple volume storage-group-defined table spaces and indexes, REORG PLUS attempts to reallocate the data set on the volume on which the data set currently resides if the volume is still defined in the storage group. After the current volume, the order of the volumes that REORG PLUS uses for allocating the Db2 VSAM data sets that you are reorganizing is unpredictable unless you use the DSRSEXIT user exit to specify a particular order.

EDITPROCs

REORG PLUS uses EDITPROCs to extract keys and update columns.

Recoverability and restartability

Partition rebalancing

When you specify the REBALANCE command option, REORG PLUS computes limit key breaks based on rebalancing at the record level. This enables REORG PLUS to redistribute the number of rows across partitions. The Db2 REORG utility computes limit key breaks based on rebalancing at the page level.

REORG PLUS enables you to rebalance partitions when running a partial reorganization.

Alternatively, you can use a DDLIN data set to specify ALTER statements with new limit key values. REORG PLUS uses these new limit key values to rebalance partitions.

REORG PLUS invalidates the SQL Statements in the Dynamic Statement Cache when the REORG utility is executed unless INVALIDATECACHE=NO is specified.

On Db2 version 12, the Db2 REORG utility invalidates the SQL Statements in the Dynamic Statement Cache when STATISTICS and INVALIDATECACHE YES are specified.

Performance

• Due to efficient processing in reorganization, log apply, and termination, REORG PLUS can successfully complete a SHRLEVEL CHANGE reorganization when the Db2 objects that you are reorganizing are under a heavier application load.
• REORG PLUS provides the ability to start the read-only phase of the reorganization at a specific time.

Log apply process

• You can specify the spill data set size. If insufficient memory exists to hold the RID translation maps or the log records, REORG PLUS allocates spill data sets on disk.
• REORG PLUS processing does not impact the Db2 buffer pools because REORG PLUS runs outside of Db2.

RID translation map

• The RID translation map that REORG PLUS creates is not a Db2 object and therefore does not use DB2 resources.
• The REORG PLUS RID map processing does not require log record sequence numbers (LRSNs) or relative byte addresses (RBAs) to be kept for each RID map entry, resulting in less storage for the RID map.
• You can control the amount of virtual storage that is allocated for the RID map by using an installation option or an option on the REORG command.
• REORG PLUS does not require a mapping table to be predefined and does not require the mapping table name to be specified on the REORG command.
• REORG PLUS does not require any Db2 sorting services to order the RID map records.

Image copy support

REORG PLUS can:

• Update full image copy data sets on DASD
• Create incremental image copies
• Create full inline image copies

When possible, REORG PLUS multitasks the creation of full and incremental image copies, reducing the amount of processing time.

Altering limit keys

REORG PLUS allows you to alter the limit keys to rebalance partitions during a SHRLEVEL CHANGE reorganization. You can perform the alter as part of the reorganization, so that the object is never put in REORG pending status (REORP).

Defining data sets

REORG PLUS provides additional syntax for more flexibility, including:

• Options that allow you to define memory size for the RID maps and log record buffers.
• Disk size for the spill data sets.
• You can also specify a data set name prefix to customize the spill data set names.

Altering execution

You can dynamically alter execution by using the Utility Monitor function of the XBM ISPF interface.

Displaying status

You can display the status of the online reorganization by sending the Display command to REORG PLUS through the interface provided by the XBM Utility Monitor.