Scenario: ExampleBANK reclaiming table and index space - Evaluating the effectiveness of reclaiming space from a table

Time passes and normal operations are run on the ITC table.

At some point a batch delete is run on this table, and large portions of the object become empty. Olivia wants to make this trapped space available to other objects in TABLESPACE1. Olivia can evaluate the effectiveness of space reclaim by using the ADMIN_GET_TAB_INFO function. This function can collect information about the physical space that is occupied by the data portion of the table T1.

Olivia collects the number of extents in the table T1 that are candidates for cleanup:

SELECT T.SPARSE_BLOCKS, T.RECLAIMABLE_SPACE FROM TABLE 
       (SYSPROC.ADMIN_GET_TAB_INFO('OLIVIA,','T1')) T

SPARSE_BLOCKS        RECLAIMABLE_SPACE 
-------------------- ----------------- 
				
7834                 14826781647 

  1 record(s) selected.

Olivia notices that there are a significant number of blocks in this table that are sparsely populated with data. A significant amount of space is available to be reclaimed. By running a reorganization on this table, Olivia consolidates the remaining data in these blocks into a smaller group of blocks. A reorganization also releases any fully empty blocks back to the table space. Using the REORG command, Olivia then releases the reclaimable space now empty after the batch delete process back to the system:

REORG TABLE T1 RECLAIM EXTENTS

Note: The table remains fully available to all users while the REORG command is processed.

Olivia then repeats the command to determine how much space was released to the table space:

SELECT T.SPARSE_BLOCKS, T.RECLAIMABLE_SPACE FROM TABLE 
       (SYSPROC.ADMIN_GET_TAB_INFO('OLIVIA,','T1')) T

SPARSE_BLOCKS        RECLAIMABLE_SPACE 
-------------------- ----------------- 
				
1                    30433 

  1 record(s) selected.

The result is 14,826,751,224 KB of space that is formerly occupied by data is reclaimed. Since the RECLAIM EXTENTS operation is an online operation, Olivia notes that the sparse blocks and reclaimable space are not zero when the operation is complete. Other activity on the table occurred while the RECLAIM EXTENTS operation completed.