Determining which indexes require reorganization

Reorganizing indexes might improve performance. To determine which indexes to reorganize to potentially gain such a performance improvement, you can analyze certain data in the Db2 catalog. You can then reorganize these indexes by using the REORG INDEX utility.

Procedure

To determine which indexes require reorganization:

  1. Issue the following SQL statement to identify user-created indexes and Db2 catalog indexes to consider reorganizing with the REORG INDEX utility:
    Begin program-specific programming interface information.
    EXEC SQL 
SELECT IXNAME, IXCREATOR  
FROM SYSIBM.SYSINDEXPART    
WHERE LEAFDIST > 200 
ENDEXEC
    End program-specific programming interface information.

    Using a LEAFDIST value of more than 200 as an indicator of a disorganized index is merely a rough guideline for general cases. This guidance is not absolute. In some cases, 200 is an acceptable value for LEAFDIST. For example, with FREEPAGE 0 and index page splitting, the LEAFDIST value can climb sharply. In this case, a LEAFDIST value that exceeds 200 can be acceptable.

  2. Issue the following SQL statement to determine the average distance (multiplied by 100) between successive leaf pages during sequential access of the index.
    Begin program-specific programming interface information.
    EXEC SQL 
SELECT LEAFDIST   
FROM SYSIBM.SYSINDEXPART   
WHERE IXCREATOR = 'index_creator_name'   
AND IXNAME = 'index_name' 
ENDEXEC
    End program-specific programming interface information.

    An increase in the LEAFDIST value over time probably indicates that the index needs to be reorganized. The optimal value of the LEAFDIST catalog column is zero. However, immediately after you run the REORG and RUNSTATS utilities, LEAFDIST might be greater than zero as a result of empty pages for FREEPAGE and non-leaf pages.