One of our DB2 clients has a 600 GB database that contains two large tables (between 10 and 20 GB each) that needed to be reorged. Our options were limited because the table needs to stay online 24x7. Testing on the development system (same hardware, version of db2 and data) projected an INPLACE online reorg would take several weeks! I tested with an INPLACE reorg so the process could be started, stopped, paused and resumed as needed.
The row size of each table was near the 4k pagesize limit. So as a test, I moved these tables from a 4k pagesize tablespace to an 8k pagesize tablespace. This process included creating the new 8k bufferpool and tablespace, creating the clone tables with a slightly different name in the new tablespace, loading from cursor from the old table to the new table, dropping all constraints on the old table, renaming the old table to *_OLD, then renaming the new table to the correct name and recreating all the indexes, constraints and views. Finally a runstats was required on the tables and all indexes.
The INPLACE reorg now takes 10 minutes! This made the table reorgs simple to run at any time because they are never offline. The reorg helps performance and helps to limit disk space usage.
More Steampunk Fun
1 day ago