Classic table reorganization uses a shadow copy approach,
building a full copy of the table that is being reorganized.
There are four phases in a classic or offline table reorganization
operation:
- SORT - During this phase, if an index was specified on the REORG
TABLE command, or a clustering index was defined on the
table, the rows of the table are first sorted according to that index.
If the INDEXSCAN option is specified, an index scan is used to sort
the table; otherwise, a table scan sort is used. This phase applies
only to a clustering table reorg operation. Space reclaiming reorg
operations begin at the build phase.
- BUILD - During this phase, a reorganized copy of the entire table
is built, either in its table space or in a temporary table space
that was specified on the REORG TABLE command.
- REPLACE - During this phase, the original table object is replaced
by a copy from the temporary table space, or a pointer is created
to the newly built object within the table space of the table that
is being reorganized.
- RECREATE ALL INDEXES - During this phase, all indexes that were
defined on the table are recreated.
You can monitor the progress of the table reorg operation and identify
the current phase using the snapshot monitor or snapshot administrative
views.
The locking conditions are more restrictive in offline mode than
in online mode. Read access to the table is available while the copy
is being built. However, exclusive access to the table is required
when the original table is being replaced by the reorganized copy,
or when indexes are being rebuilt.
An IX table space lock is required during the entire table reorg
process. During the build phase, a U lock is acquired and held on
the table. A U lock allows the lock owner to update the data in the
table. Although no other application can update the data, read access
is permitted. The U lock is upgraded to a Z lock after the replace
phase starts. During this phase, no other applications can access
the data. This lock is held until the table reorg operation completes.
A number of files are created by the offline reorganization process.
These files are stored in your database directory. Their names are
prefixed with the table space and object IDs; for example, 0030002.ROR is
the state file for a table reorg operation whose table space ID is
3 and table ID is 2.
The following list shows the temporary files that are created in
a system managed space (SMS) table space during an offline table reorg
operation:
- .DTR - Data shadow copy file
- .LFR - Long field file
- .LAR - Long field allocation file
- .RLB - LOB data file
- .RBA - LOB allocation file
- .BMR - Block object file for multidimensional clustering (MDC)
tables
The following temporary file is created during an index reorg operation:
The following list shows the temporary files that are created in
the system temporary table space during the sort phase:
- .TDA - Data file
- .TIX - Index file
- .TLF - Long field file
- .TLA - Long field allocation file
- .TLB - LOB file
- .TBA - LOB allocation file
- .TBM - Block object file
The files that are associated with the reorganization process should
not be manually removed from your system.
DB2 Version 9.7 for Linux, UNIX, and Windows