When a task performs a delete operation or an update that shortens row length, the empty space is preserved in case the transaction is rolled back. If a table is subject to frequent deletes and row-shortening updates, unreclaimed space may accumulate to the point that it impairs performance.
reorg reclaim_space reclaims unused space left by deletes and updates. On each page that has space resulting from committed deletes or row-shortening updates, reorg reclaim_space rewrites the remaining rows contiguously, leaving all the unused space at the end of the page. If all rows have been deleted and there are no remaining rows, reorg reclaim_space deallocates the page.
You can get statistics on the number of unreclaimed row deletions in a table from the systabstats table and by using the optdiag utility. There is no direct measure of how much unused space there is as a result of row-shortening updates.
The syntax for reorg reclaim_space is:
reorg reclaim_space tablename [indexname]
[with {resume, time = no_of_minutes}]
If you specify only a table name, only the table’s data pages are reorganized to reclaim unused space; in other words, indexes are not affected. If you specify an index name, only the pages of the index are reorganized.
For information about the resume and time options, see “resume and time options for reorganizing large tables”.
