Technical Blog Post
Abstract
Problem of running index reorg immediately after table reorg
Body
During the reorg we are going through a list of tables that need to be reorged one by one.
For each table we are doing the table reorg first :
db2 "reorg table $MYUSER.${i} inplace" (where $MYUSER.${i} is each of the table name)
Then doing the index rerog :
db2 "reorg indexes all for table $MYUSER.${i} allow write access"
This might face problem. Even if we add the tablealt lock under the special register ("CURRENT LOCK TIMEOUT") it still might not work correctly.
The reason being,
Inplace table reorg does periodic internal commits during its processing to free up resources. This is due to it's design to take a back seat to other concurrently running applications.
Here, we are hoping to queue the online index reorg to start once the inplace table reorg has completed. Relying on lock waits will not accomplish this.
What will happen is this:
inplace table reorg starts, getting assorted locks
online index reorg requested, waits on tablealt lock
inplace table reorg finishes first phase (table partially reorged), does internal commit which releases locks
online index reorg gets lock and starts
inplace table reorg waits to start next phase
online index reorg completes, releases locks
inplace table reorg continues, reorgs remainder of the table (and in the process, fragments the indexes some more)
End result: nicely reorged table, somewhat reorged indexes. Which is not the objective of the whole reorg steps.
If we want to run the index reorg AFTER the table reorg to clean up the indexes, then we need to wait until the table reorg is completed.
db2pd -reorgs will be one way to check the reorg progress
Reorging all the tables first, and then doing all the indexes, may work as long as the table reorg has completed before its corresponding index reorg starts.
We might not be able to guarantee that as the table reorg is an asynchronous background process.
Following these guidelines, modified the script to run all tables reorg first, then wait for half hour to let all of them ‘Fully’ completed, using db2pd –reorgs to verify the completion. Then reorg all the indexes for each table afterwards.
UID
ibm11140706