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.
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.
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.
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.
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.
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.