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

Olivia notes the space reclaimed from the data portion of the table T1 after a batch delete. Olivia knows that there is some cleanup left to be done in the indexes for this table.

Reclaiming index space can recover space occupied by indexes while the same indexes are still available for use by users.

As with reclaiming space from the table, the space in question is reclaimed back to the table space for reuse by other objects.

Olivia uses the ADMIN_GET_INDEX_INFO function to see how much space can be reclaimed:

SELECT SUBSTR(INDNAME,1,10) AS INDNAME, IID, INDEX_OBJECT_L_SIZE, INDEX_OBJECT_P_SIZE, 
		RECLAIMABLE_SPACE FROM TABLE(SYSPROC.ADMIN_GET_INDEX_INFO('','OLIVIA','T1')) 
		AS INDEXINFO WHERE INDNAME='INX1'

        INDNAME    IID    INDEX_OBJECT_L_SIZE  INDEX_OBJECT_P_SIZE  RECLAIMABLE_SPACE   
        ---------- ------ -------------------- -------------------- --------------------
        INX1            3              1106752              1106752               846592

          1 record(s) selected.

Olivia uses the REORG command with the new parameters added for reclaiming index space to finish the cleanup in the index:

REORG INDEXES ALL FOR TABLE T1 ALLOW WRITE ACCESS CLEANUP ALL RECLAIM EXTENTS

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

SELECT SUBSTR(INDNAME,1,10) AS INDNAME, IID, INDEX_OBJECT_L_SIZE, INDEX_OBJECT_P_SIZE, 
		RECLAIMABLE_SPACE FROM TABLE(SYSPROC.ADMIN_GET_INDEX_INFO('','OLIVIA','T1')) 
		AS INDEXINFO WHERE INDNAME='INX1'

        INDNAME    IID    INDEX_OBJECT_L_SIZE  INDEX_OBJECT_P_SIZE  RECLAIMABLE_SPACE
        ---------- ------ -------------------- -------------------- --------------------
        INX1            3               259776               259776                    0

          1 record(s) selected.

The result is an estimated 846,592 KB of space reclaimed. If the physical size after space is reclaimed is subtracted from the original physical size, Olivia notes that the actual space reclaimed is 846,976 KB.