DB2 10.5 for Linux, UNIX, and Windows

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.

There are four main phases in an online table REORG operation:

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 (a "new 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

During an online table REORG operation, an .OLR state file is created for each database partition. This binary file has a name whose format is xxxxyyyy.OLR, where xxxx is the table space ID and yyyy is the object ID in hexadecimal format. This file contains the following information that is required to resume an online REORG operation from the paused state:

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.