Inplace/Online Reorg phases

Online or inplace table reorganization allows the user to reorganize a table while permitting full access to that table. While inplace REORG provides uninterrupted user access to the data, the performance of inplace REORG is slower than classic or offline REORG.

During an inplace table reorganization, the entire table is not reorganized at once. Instead, portions of the table are reorganized incrementally. Data is not copied out to a temporary table space: rows are moved within the existing table object to re-establish clustering, reclaim free spaces, and eliminate overflow rows.

There are four primary phases for inplace table REORG:

1. SELECT N pages
   During this phase, DB2 selects N pages, where N is the extent size with a minimum of 32 sequential pages for REORG processing.
2. Vacate the range
   With the N pages selected, inplace table REORG moves all rows within this range to free pages within the table. Each row that is moved leaves behind an RP (REORG pointer) record that contains the RID of the row’s new location. The row is inserted to free pages in the table as a RO (REORG overflow) record that contains the data.
   Once REORG finishes moving a set of rows, it waits for all existing data accesses that are occurring in the table (for example, by currently executing applications) to complete. These existing accesses, called old scanners, use old the RIDs when accessing the table data. Any accesses that start during this waiting period, called new scanners, use the new RIDs to access the data. Once all of the old scanners have completed, REORG cleans up the moved rows, deleting the RP records and converting the RO records to normal records.
3. Fill the range
   After all rows have been vacated, the rows are written back, in a reorganized format, sorted according to any indexes used, and obeying any PCTFREE restrictions defined. When all the pages in the range are filled, the next N sequential pages are selected in the table, and the process begins again.
4. Truncate the table
   When all pages in the table have been reorganized, the table will be truncated to reclaim space by default. If the NOTRUNCATE option is specified, the reorganized table is not truncated.