How can I find indexes that are never or seldom used in dynamic SQL?


Solution
In the z/OS® and OS/390® environments, you can find DB2® indexes that are never or seldom used by analyzing IFCIDs 22 with DB2 PM:

IFCID 22 is cut at the end of PREPARE and shows the access path chosen by the optimizer. The contents are similar to the PLAN_TABLE of EXPLAIN.
EXPLAIN does not list any referential integrity (RI) considerations.
DB2 PM provides a facility to put IFCID 22 into a DB2 table. If SYSINDEXES is outer-joined to this DB2 table, the mismatches show the indexes that are never used.

This technique can use a considerable amount of disk space, depending on the period examined.


P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.

With kind regards
Michael Dewert, Software GroupTechnical Sales DB2