Q and A

Asked and Answered

Do you know of any way to detect unused indexes in DB2 (for z/OS and OS/390)? Are there tools or techniques to facilitate identification of such indexes?

Robert Catterall responds:

For static SQL statements, this isn't hard to do. You can check the SYSIBM.SYSPACKDEP table in the catalog to see information about indexes on which packages have a dependency (that is, indexes that are components of packages' data access paths). If you have an index that's not listed in SYSPACKDEP , it may be a candidate for elimination (my company has eliminated a pretty good number of indexes ourselves, using this approach). You might be able to generate a list of indexes that don't appear in SYSPACKDEP by way of a SELECT against SYSINDEXES with a NOT EXISTS subquery targeting the SYSPACKDEP table.

Note that even if an index isn't to be found in SYSPACKDEP , you should look before taking the DROP INDEX leap. Make sure that it's not there to guarantee uniqueness of a column value (or a value of concatenated columns). Also, make sure that it's not there to support a DB2-defined referential integrity constraint. Finally, you might want to check with some of your application developers — at least to give them a heads-up about an index that's about to go away.

Dynamic SQL is a trickier proposition. A DB2 monitor (IBM's DB2 Performance Monitor is one example, and several others are available from a variety of vendors) might be able to show you index usage for dynamic SQL statements, but that could involve the turning on of some fairly expensive traces, so you'd probably want to gather such information in relatively short bursts. A lower-overhead approach would involve the use of a DB2 buffer pool that is set aside solely for information-gathering purposes.

Suppose that buffer pool is BP20 (it could be any pool other than BP0, which should be used only for the catalog and directory objects). Give BP20 a decent number of buffers (at least a few thousand). If you suspect that index ABC isn't being used, find an opportunity to alter it so as to assign it to BP20 (which could be done during a maintenance window, or no such window is available, perhaps via a STOP DATABASE xxx SPACENAM xxx command with the AT COMMIT option specified to utilize drain locking. See the DB2 Command Reference for more details). Then, check with your DB2 monitor (or the DISPLAY BUFFERPOOL command) for a few days to see if there is any activity in BP20. If there is, the index is used by something. If there's no activity for the buffer pool, the index may be a candidate for dropping.

If there's activity and you want to keep the index, move it back to its old buffer pool so that you can use this technique for a different index.

See a complete archive of reader/author Q&As .