It has always been very difficult to identify unused file indexes or even determine all of the indexes we have. But as we all know, extra indexes can degrade database and overall system performance.

When RPG or COBOL programs use native I-O to open keyed physical files or logical files, the last-used date in the file description is updated, so that gives us a clue as to which indexes are being used recently. But when SQL functions like Embedded SQL, Query, STRSQL, or ODBC run against the indexes, the last-used date does not update, so this method isn't always reliable.

If you use iSeries Navigator in V5R3 or above, you have access to some great database statistics, including what indexes exist, when an index was last used to retrieve data, and how many times the index has been used.

To access this information using iSeries Navigator, use Database > Schemas > Tables. Right-click the table of interest and select Show Indexes.

For information on getting your index statistics, you can go to the topic at the IBM System i Information Center.

Here are some additional IBM resources for the health of your database:

Information on the Index Advisor

System i Database Health Center

Built-in Query Optimization tools