Choosing a table reorganization method

There are four approaches to table reorganization: CLASSIC reorganization (offline), INPLACE reorganization with FULL to recluster or reclaim space (online), INPLACE reorganization with CLEANUP OVERFLOWS to only cleanup overflows (online), and RECLAIM EXTENTS (online).

Offline, or CLASSIC reorganization is the default behavior. To specify an online reorganization operation, use the INPLACE with FULL, INPLACE with CLEANUP OVERFLOWS, or RECLAIM EXTENTS table clause of the REORG command.

Each approach has its advantages and drawbacks, which are summarized in the following sections. When you choose a reorganization method, consider which approach offers advantages that align with your priorities. For example, if you want to minimize the duration that the affected object is unavailable, online reorganization might be preferable. If your priority is the duration that is required for the reorganization operation, offline reorganization would be preferable.

Advantages of CLASSIC reorganization

This approach offers:
  • The fastest table reorganization operations, especially if large object (LOB) or long field data is not included.
  • Perfectly clustered tables and indexes upon completion.
  • Indexes that are automatically rebuilt after a table is reorganized; there is no separate step for rebuilding indexes.
  • The use of a temporary table space for building a shadow copy. The use of a shadow copy reduces the space requirements for the table space that contains the target table or index.
  • The ability to use an index other than the existing clustering index to recluster the data.

Disadvantages of CLASSIC reorganization

This approach is characterized by:
  • Limited table access; read access only during the sort and build phase of a REORG operation.
  • A large space requirement for the shadow copy of the table that is being reorganized.
  • Less control over the REORG process; an offline REORG operation cannot be paused and restarted.
  • A large active log might be required since the entire operation is handled in a single unit of work.

Advantages of INPLACE reorganization with the FULL option

This approach offers:
  • Full table access, except during the truncation phase of a REORG operation.
  • More control over the REORG process, which runs asynchronously in the background, and can be paused, resumed, or stopped. For example, you can pause an in-progress REORG operation if many updates or deletes are running against the table.
  • A process that can be resumed in the event of a failure.
  • A reduced requirement for working storage because a table is processed incrementally.
  • Immediate benefits of reorganization, even before a REORG operation completes.

Disadvantages of INPLACE reorganization with the FULL option

This approach is characterized by:
  • Imperfect data or index clustering, depending on the type of transactions that accesses the table during a REORG operation.
  • Poorer performance than an offline REORG operation.
  • Potentially high logging requirements. These requirements depend on the number of rows that are moved, the number of indexes that are defined on the table, and the size of those indexes.
  • A potential need for subsequent index reorganization because indexes are maintained, not rebuilt.
  • Incomplete space reclamation, because online reorganization cannot move internal records.

Advantages of INPLACE reorganization with the CLEANUP OVERFLOWS option

This approach offers:
  • Full table access.
  • More control over the REORG process, which runs asynchronously in the background, and can be paused, resumed, or stopped. For example, you can pause an in-progress REORG operation if many updates or deletes are running against the table.
  • A process that can be resumed in the event of a failure.
  • Fixes all pointer and overflow pairs that exist in the table that improves the performance characteristics of SQL access on the table.
  • A reduced requirement for working storage because a table is processed incrementally.
  • Immediate benefits of reorganization, even before a REORG operation completes.
  • Less overall logging and impact than an INPLACE reorganization with the FULL option.

Disadvantages of INPLACE reorganization with the CLEANUP OVERFLOWS option

This approach is characterized by:
  • No benefit other than resolving pointer and overflow pairs. Use this mode only if your table has many pointer and overflow pairs and these pairs are causing performance issues.

Advantages of RECLAIM EXTENTS

This approach offers:
  • Full table access.
  • A process that can be resumed in the event of a failure. The work that is done up until the point of failure is not lost. The operation is resumed from the point of failure and through to completion.
  • Lightweight operation.
  • Frees space back to the table space that can then be used by any table space consumer.
  • A reduced requirement for working storage because a table is processed incrementally.

Disadvantages of RECLAIM EXTENTS

This approach is characterized by:
  • Does not recluster data.
  • Does not fix all pointer and overflow pairs that exist in the table.
  • Does not convert all existing rows to the current table schema.
  • A potential need for subsequent index reorganization because indexes are maintained, not rebuilt.
Table 1. Comparison of online and offline reorganization
Characteristic CLASSIC reorganization INPLACE reorganization with FULL INPLACE reorganization with CLEANUP OVERFLOWS RECLAIM EXTENTS
Performance Fast Slow Fast Fast
Clustering factor of data at completion Good Not perfectly clustered No clustering is done No clustering is done
Concurrency (access to the table) Ranges from no access to read-only Ranges from read-only to full access Ranges from read-only to full access Ranges from no access to full access
Data storage space requirement Significant Not significant Not significant Not significant
Logging storage space requirement Not significant Might be significant Might be significant Might be significant
User control (ability to pause, restart process) Less control More control More control Less control as you cannot restart or pause
Recoverability Recoverable, but might take more time than an online reorganization. Recoverable Recoverable Recoverable
Index rebuilding Done Not done Not done Not done
Supported for all types of tables Yes No No No
Ability to specify an index other than the clustering index Yes No No No
Use of a temporary table space Yes No No No
Table 2. Table types that are supported for online and offline reorganization
Table type Support offline reorganization Support online reorganization
Multidimensional clustering tables (MDC) Yes1 Yes8
Insert time clustering tables (ITC) Yes1, 7 Yes6, 7
Range-clustered tables (RCT) No2 No
Append mode tables Yes No3
Tables with long field or large object (LOB) data Yes5 Yes5
System catalog tables:
  • SYSIBM.SYSCODEPROPERTIES
  • SYSIBM.SYSDATATYPES
  • SYSIBM.SYSNODEGROUPS
  • SYSIBM.SYSROUTINES
  • SYSIBM.SYSSEQUENCES
  • SYSIBM.SYSTABLES
  • SYSIBM.SYSTABLESPACES
  • SYSIBM.SYSVARIABLES
 Yes No
Notes:
  1. Because clustering is automatically maintained through MDC block indexes, reorganization of an MDC table involves space reclamation only. No indexes can be specified. Similarly, for ITC tables, you cannot specify a reorganization with a clustering index.
  2. The range area of an RCT always remains clustered.
  3. Online reorganization can be run after append mode is disabled.
  4. Reorganizing long field or large object (LOB) data can take a significant amount of time, and does not improve query performance. Reorganization is done only for space reclamation purposes.
  5. Online table reorganization does not reorganize the LONG/LOB data, but reorganizes the other columns.
  6. Online reorganization of an ITC table is supported. The reorganization is done with the existing RECLAIM EXTENTS table clause of the REORG command.
  7. The RECLAIM EXTENTS table clause of the REORG command consolidates sparse extents implicitly. This consolidation leads to more space reclamation, but a longer duration for utility execution when compared to Db2® version 10.1.
  8. Not supported when RECLAIM EXTENTS is used.
Note:

You can use the online table move stored procedure as an alternative approach to INPLACE reorganization. See Moving tables online by using the ADMIN_MOVE_TABLE procedure.

For tables with XML columns, classic table reorganization may take a lot of time to finish because system generated XML indexes will need to be re-built. This requires traversal of every document in the table. During this time, the table cannot be accessed. Therefore, in place table reorganization is generally recommended because the system generated XML indexes will not need to be re-built and the table remains accessible during the reorganization (except during the truncation phase).

Monitoring the progress of table reorganization

Information about the progress of a current table REORG operation is written to the history file. The history file contains a record for each reorganization event. To view this file, run the LIST HISTORY command against the database that contains the table that is reorganized.

You can also use table snapshots to monitor the progress of table REORG operations. Table reorganization monitoring data is recorded, regardless of the setting for the database system monitor table switch.

If an error occurs, an SQLCA message is written to the history file. If an INPLACE table REORG operation, the status is recorded as PAUSED.

If no tables have been reorganized, no monitor data will be returned by monitor utilities.