DB2 Version 9.7 for Linux, UNIX, and Windows

Choosing a table reorganization method

There are two approaches to table reorganization: classic reorganization (offline) and inplace reorganization (online).

Offline reorganization is the default behavior. To specify an online reorganization operation, use the INPLACE option on the REORG TABLE command.

An alternative approach to inplace reorganization, using online table move stored procedures, is also available. See "Moving tables online by using the ADMIN_MOVE_TABLE procedure".

Each approach has its advantages and drawbacks, which are summarized below. When choosing a reorganization method, consider which approach offers advantages that align with your priorities. For example, if recoverability in case of failure is more important than performance, online reorganization might be preferable.

Advantages of offline 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 has been reorganized; there is no separate step for rebuilding indexes
  • The use of a temporary table space for building a shadow copy; this reduces the space requirements for the table space that contains the target table or index
  • The use of an index other than the clustering index to re-cluster the data

Disadvantages of offline 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

Advantages of online reorganization

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 which can be paused, resumed, or stopped; for example, you can pause an in-progress reorg operation if a large number of update or delete operations are running against the table
  • A recoverable process 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 online reorganization

This approach is characterized by:
  • Imperfect data or index clustering, depending on the type of transactions that access the table during a reorg operation
  • Poorer performance than an offline reorg operation
  • Potentially high logging requirements, depending on the number of rows being 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
Table 1. Comparison of online and offline reorganization
Characteristic Offline reorganization Online reorganization
Performance Fast Slow
Clustering factor of data at completion Good Not perfectly clustered
Concurrency (access to the table) Ranges from no access to read-only Ranges from read-only to full access
Data storage space requirement Significant Not significant
Logging storage space requirement Not significant Could be significant
User control (ability to pause, restart process) Less control More control
Recoverability Not recoverable Recoverable
Index rebuilding Done Not done
Supported for all types of tables Yes No
Ability to specify an index other than the clustering index Yes No
Use of a temporary table space Yes No
Table 2. Table types that are supported for online and offline reorganization
Table type Offline reorganization supported Online reorganization supported
Multidimensional clustering tables (MDC) Yes1 No
Range-clustered tables (RCT) No2 No
Append mode tables Yes No3
Tables with long field or large object (LOB) data Yes4 Yes5
System catalog tables:
  • SYSIBM.SYSCODEPROPERTIES
  • SYSIBM.SYSDATATYPES
  • SYSIBM.SYSNODEGROUPS
  • SYSIBM.SYSROUTINES
  • SYSIBM.SYSSEQUENCES
  • SYSIBM.SYSTABLES
  • 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.
  2. The range area of an RCT always remains clustered.
  3. Online reorganization can be performed 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; it should only be done for space reclamation.
  5. Online table reorganization does not reorganize the LONG/LOB data, but reorganizes the other columns.

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, execute the LIST HISTORY command against the database that contains the table being 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. In the case of an inplace table reorg operation, the status is recorded as PAUSED.