resume and time Options for Reorganizing Large Tables

Use the resume and time options of the reorg command when reorganizing an entire table would take too long and interfere with other database activities.

time allows you to run a reorg for a specified length of time. resume allows you to start a reorg at the point in a table where the previous reorg finished. In combination, the two options allow you to reorganize a large table by running a series of partial reorganizations (for example, during offhours).

resume and time are not available with reorg rebuild.

Specifying no_of_minutes in the time Option

The no_of_minutes argument in the time option refers to elapsed time, not CPU time.

For example, to run reorg compact for 30 minutes, beginning where a previous reorg compact finished, enter:For example, to run reorg compact for 30 minutes, beginning where a previous reorg compact finished, enter:

reorg compact tablename with resume, time=30

If the reorg process goes to sleep during any part of the 30 minutes, it still counts as part of the elapsed time and does not add to the duration of the reorg.

When the amount of time specified has passed, reorg saves statistics about the portion of the table or index that was processed in the systabstats table. This information is used as the restart point for a reorg with the resume option. The restart points for each of the three parameters that take resume and time options are maintained separately. You cannot, for example, start with reorg reclaim_space and then resume the process using reorg compact.

If you specify no_of_minutes, and reorg arrives at the end of a table or an index before the time is up, it returns to the beginning of the object and continues until it reaches its time limit.

Note: resume and time allow you to reorganize an entire table or index over multiple runs. However, if there are updates between reorg runs, some pages may be processed twice and some pages may not be processed at all.