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.