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 reorganizationCharacteristic |
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 reorganizationTable 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.SYSVARIABLES
|
Yes |
No |
Notes: - 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.
- The range area of an RCT always remains clustered.
- Online reorganization can be run after append mode is disabled.
- 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.
- Online table reorganization does not reorganize
the LONG/LOB data, but reorganizes the other columns.
- Online reorganization of an ITC table is supported.
The reorganization is done with the existing RECLAIM EXTENTS table
clause of the REORG command.
- 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.
- 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".
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.