Why DB2 LUW online reorg is getting paused on it's own ?
Biswarup(Bis)Mukherjee 120000HKTY Visits (7910)
A common question comes about why an online reorg is getting paused even if nobody manually tried to pause it.
There is a manual way to pause and resume online reorg with the help of commands,
reorg table <tab-name> inplace pause
reorg table <tab-name> inplace resume
When an online table reorg operation is paused, one cannot run a new reorganization of that table. One must either resume or stop the paused operation before beginning a new reorganization process
There are situations when an online reorg could be paused on it's own.
If command "db2pd -reorgs -db <db-name>" is run it might show tables in "Online Paused" instead of "Online Started"
Sometimes, it's thought to be paused as part of auto-maintenance window limit reached. But, that is not usually the case as the data/index reorg happens during offline windows. Only index reorgs are run during online windows.
So the reason of reorg pause is due to some errors or, similar inconveniences, if experienced by the reorg.
One common example is lock timeout.
Checking db2diag.log might show following kind of messages,
It's an SQL0911 rc=68 which is a lock timeout.
Consider increasing the lock timeout value in the database if it's too low provided increasing lock timeout time is permitted.
Otherwise, try to reduce concurrency during the run of the online reorg.
If paused, just resume manually.