Inplace (online) table reorganization
Inplace table reorganization reorganizes a table and allows full access to data in the table. The cost of this uninterrupted access to the data is a slower table REORG operation.
Starting inDb2® Cancun Release 10.5.0.4, inplace table reorganization is supported in Db2 pureScale® environments.
During an inplace or online table REORG operation, portions of a table are reorganized sequentially. Data is not copied to a temporary table space; instead, rows are moved within the existing table object to reestablish clustering, reclaim free space, and eliminate overflow rows.
- SELECT n pages
During this phase, the database manager selects a range of n pages, where n is the size of an extent with a minimum of 32 sequential pages for REORG processing.
- Vacate the range
The REORG utility moves all rows within this range to free pages in the table. Each row that is moved leaves behind a REORG table pointer (RP) record that contains the record ID (RID) of the row's new location. The row is placed on a free page in the table as a REORG table overflow (RO) record that contains the data. After the utility finishes moving a set of rows, it waits until all applications that are accessing data in the table are finished. These
old scanners
use old RIDs when table data is accessed. Any table access that starts during this waiting period (anew scanner
) uses new RIDs to access the data. After all of the old scanners are complete, the REORG utility cleans up the moved rows, deleting RP records and converting RO records into regular records. - Fill the range
After all rows in a specific range are vacated, they are written back in a reorganized format, they are sorted according to any indexes that were used, and obeying any PCTFREE restrictions that were defined. When all of the pages in the range are rewritten, the next n sequential pages in the table are selected, and the process is repeated.
- Truncate the table
By default, when all pages in the table are reorganized, the table is truncated to reclaim space. If the NOTRUNCATE option is specified, the reorganized table is not truncated.
Files created during an online table REORG operation
- The type of REORG operation
- The life log sequence number (LSN) of the table that is reorganized
- The next range to be vacated
- Whether the REORG operation is clustering the data or just reclaiming space
- The ID of the index that is being used to cluster the data
A checksum is run on the .OLR file. If the file becomes corrupted, causing checksum errors, or if the table LSN does not match the life LSN, a new REORG operation is initiated, and a new state file is created.
If the .OLR state file is deleted, the REORG process cannot resume, SQL2219N is returned, and a new REORG operation must be initiated.
The files that are associated with the reorganization process must not be manually removed from your system.