CLASSIC (offline) table reorganization

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:
  1. 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.
  2. 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.
  3. 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.
  4. RECREATE ALL INDEXES - During this phase, all indexes that were defined on the table are re-created.

You can monitor the progress of the table REORG operation and identify the current phase with 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 available. 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) and insert time clustering (ITC) tables
The following temporary file is created during an index REORG operation:
  • .IN1 - Shadow copy file
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 must not be manually removed from your system.
Note: If the REORG TABLE command is being issued interactively, press Ctrl-C on the keyboard. Otherwise, the application running the REORG can be forced using the FORCE APPLICATIONS command. You can retrieve the application handle for the application that is running the REORG operation by using the MON_GET_UTILITY table function.