DB2 reorg table command: Subtle syntax has huge effect on how reorg is performed

Body

The primary reason to perform a reorg of a table is to release unused space from a table back to the tablespace.   This applies to both SMS and DMS tablespaces.  With a SMS tablespace the free space will be returned to the filesystem.  With a DMS tablespace the free space will be returned to the tablespace as unused extents.  

There are 3 main types of reorgs

1) offline reorg:  

db2 reorg table t1

Summary:  During an offline reorg all non deleted rows will be copied to a shadow table in the same tablespace as the original table.   This shadow table will coexist with the original table until the completion of the reorg.   For SMS tablespacses this means allocating a new file in the filesystem and allocating space for it.  For DMS tablespaces this means allocating new extents for the shadow table. This may increase the high water mark for used extents in the tablespace.  After the copy is complete the shadow table inherits the attributes of the original table and the original table is dropped.

Logging impact:  Minimal transaction logs generated.  Bulk of transaction log records deal with allocating and freeing extents.

2) offline reorg using system temporary tablespace:  

db2 reorg table t1 use TEMPSPACE1

Summary:  During an offline reorg that uses a system temporary tablespace all non deleted rows will be copied to a shadow table that is created in the system temporary tablespace.   The original table is then logically emptied.  The rows are then copied back from the shadow table into the original table.  For SMS tablespaces the original table is then resized to the nearest page and unused pages are returned to the filesystem.   For DMS tablespaces the original table is resized to the nearest extent and the extra extents are returned to the tablespace as free extents.  For a DMS tablespace,  this type of reorg reuses the extents already allocated to the original table. The shadow table is dropped when the reorg operation is complete.

Logging impact:  Minimal transaction logs generated.  Bulk of transaction log records deal with allocating and freeing extents.

3) online reorg:  

db2 reorg table t1 inplace

Summary:  During an online reorg the last undeleted row in the table is moved to the first page in the table with enough free space to store the row.   This process is repeated until there is no more free space in the table.   

Logging impact:  Significant transaction logs generated.  One log record to insert the row at the new location, one log record to remove the row from the old location, and one log record to modify each index that exists on the table.

INDEXES

For offline reorgs all indexes will be marked as invalid and be rebuilt after the copy phase is completed.   For online reorgs the indexes are updated while every row is moved so there is no need to recreate indexes.

LOBS

By default reorg will not modify any lob data.  It will be copied 'as is' into the new copy of the table.  If the objective is to reclaim lob storage then the 'LONGLOBDATA use <system temporary tablespace>' clause must be specified.  This will cause the lob data to be copied from the original lob storage to new lob storage.