Friday, December 3, 2010

DB2 table reorg performance - Problem Solved

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.

1 comment:

  1. Hi, during this process what happens if the old table gets new data? I mean when you change old table to _old and new table to current table, during this process, if some new records are inserted? how will you method work? do you lock the table before starting the cursor load?

    ReplyDelete

US-CERT Technical Cyber Security Alerts