REORG

The REORG utility reorganizes a table space or index. Over time, inserts, updates and deletes scatter rows out of clustering order and leave wasted free space. REORG puts the rows back into clustering sequence and reclaims the space, which keeps SQL fast.

What is REORG

• REORG rebuilds a table space so rows are stored in clustering index order again.
• It reclaims space left by deleted rows and compresses the data pages.
• After REORG, the optimizer's statistics about clustering are accurate again.
• You can reorganize a whole table space, one partition, or an index.
• REORG also resets the real-time statistics used by DB2 to recommend maintenance.

When to run REORG

• After mass deletes or updates that left many empty or half-empty pages.
• When the clustering ratio reported by RUNSTATS has dropped and queries are slower.
• When the table space is running out of space even though row counts are stable.
• When DB2 sets REORG pending status, which blocks some SQL until you reorganize.
• On a regular schedule for high-activity tables, as decided with the DBA.

SHRLEVEL options

• SHRLEVEL NONE takes the table space offline - fastest, but no application access during the REORG.
• SHRLEVEL REFERENCE allows read-only access while REORG runs.
• SHRLEVEL CHANGE allows full read and write access; DB2 logs the changes and applies them at the end.
• SHRLEVEL CHANGE needs a mapping table to track row movements between the old and new copies.
• Online REORGs (REFERENCE and CHANGE) take longer and use more log space than offline REORG.

Example of a REORG control statement:-