Scenario: ExampleBANK reclaiming table and index space - General index maintenance

Olivia notes that, for some indexes and tables, space consumption and behavior are not closely tracked. Periodically, a script can check whether any space in the affected table spaces can be cleaned up and reclaimed.

Olivia uses the REORGCHK command to determine whether an index cleanup would be beneficial:

REORGCHK ON TABLE USER1.TBL1;

Doing RUNSTATS ....


Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA.NAME                     CARD     OV     NP     FP ACTBLK    TSIZE  F1  F2  F3 REORG
----------------------------------------------------------------------------------------
Table: USER1.TBL1
                                3400      0    201    295      -   775200   0  67  70 -**
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > 
		MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / 
		Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

SCHEMA.NAME                 INDCARD  LEAF ELEAF LVLS  NDEL    KEYS LEAF_RECSIZE 
--------------------------------------------------------------------------------
Table: USER1.TBL1
Index: USER1.TBL1_INX1
                               3400   244     0    3  1595    2882          205
--------------------------------------------------------------------------------
(continued)
NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD  PCT_PAGES_SAVED  F4  F5  F6 
----------------------------------------------------------------------------------


           205                132                 132                0 100  64   9
----------------------------------------------------------------------------------
(continued)
F7  F8 REORG
------------


31   0 ---*-
------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG.  Specify the most important index for REORG sequencing.

Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.

Formula F7 in the output shows that, for the TBL1_INX1 index, an index cleanup using the REORG command would be beneficial. Olivia issues the command to clean up the indexes:

REORG INDEXES ALL FOR TABLE USER1.TBL1 ALLOW WRITE ACCESS CLEANUP;

To determine how much space can be reclaimed now that the REORG INDEXES CLEANUP command freed up space, Olivia uses the ADMIN_GET_INDEX_INFO routine:

SELECT RECLAIMABLE_SPACE 
		FROM TABLE(sysproc.admin_get_index_info('T','USER1', 'TBL1')) 
		AS t";

RECLAIMABLE_SPACE
------------------
             14736

1 record(s) selected.

If Olivia considers this value, in KB, to be significant, she can run the REORG INDEX RECLAIM EXTENTS command:

REORG INDEXES ALL FOR TABLE USER1.TBL1 ALLOW WRITE ACCESS RECLAIM EXTENTS;

Olivia can schedule this work at regular intervals to ensure that the indexes in question do not hold more space than required. This regularly scheduled work does not prohibit others from using the indexes in question.