DB2 Version 9.7 for Linux, UNIX, and Windows

Inplace (online) table reorganization

Inplace table reorganization enables you to reorganize a table while you have full access to its data. The cost of this uninterrupted access to the data is a slower table reorg operation.

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

  2. 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 has finished 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 accessing the table data. 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 have completed, the reorg utility cleans up the moved rows, deleting RP records and converting RO records into regular records.

  3. Fill the range

    After all rows in a specific range have been vacated, they are written back in a reorganized format, sorted according to any indexes that were used, and obeying any PCTFREE restrictions that were defined. When all of the pages in the range have been rewritten, the next n sequential pages in the table are selected, and the process is repeated.

  4. Truncate the table

    By default, when all pages in the table have been reorganized, the table is truncated to reclaim space. If the NOTRUNCATE option has been 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 being 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 performed 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 should not be manually removed from your system.