The performance of an offline table reorganization is largely
determined by the characteristics of the database environment.
There is almost no difference in performance between a reorg operation
that is running in ALLOW NO ACCESS mode and one that is running in
ALLOW READ ACCESS mode. The difference is that during a reorg operation
in ALLOW READ ACCESS mode, the utility might have to wait for other
applications to complete their scans and release their locks before
replacing the table. The table is unavailable during the index rebuild
phase of a reorg operation that is running in either mode.
Tips for improving performance
- If there is enough space to do so, use the same table space for
both the original table and the reorganized copy of the table, instead
of using a temporary table space. This saves the time that is needed
to copy the reorganized table from the temporary table space.
- Consider dropping unnecessary indexes before reorganizing a table
so that fewer indexes need to be maintained during the reorg operation.
- Ensure that the prefetch size of the table spaces on which the
reorganized table resides is set properly.
- Tune the sortheap and sheapthres database
configuration parameters to control the space that is available for
sorts. Because each processor will perform a private sort, the value
of sheapthres should be at least sortheap x number-of-processors.
- Adjust the number of page cleaners to ensure that dirty index
pages in the buffer pool are cleaned as soon as possible.
DB2 Version 9.7 for Linux, UNIX, and Windows