Hello,

We are using db2 V8.1 LUW on aix 5.3

After lot of searching i derived a query to find unused indexes in a database. Can you please confirm if this is right query (right way)?

db2 "select NAME, CREATOR from SYSIBM.sysindexes where NAME not in (select distinct sysibm.sysplandep.bname from sysibm.sysplandep, syscat.PACKAGEDEP, syscat.indexes where sysibm.sysplandep.bname=syscat.PACKAGEDEP.bname and syscat.PACKAGEDEP.bname=syscat.indexes.INDNAME)"


subquery in blue will find indexes whose entry is in tables syscat.PACKAGEDEP and sysibm.sysplandep. Finally it will exclude this result set from sysibm.sysindexes to get unused indexces.

regards,
Prashant

That query may be correct (I have not looked at it in detail) for indexes used in static SQL (inlcuding SQL stored procedures) but will not include any dynamic SQL that is typically submitted from a java program.

Also, be aware that (1) index statistics might give the optimizer lots of information, even if that index won't be used, and (2) cluster indexes play an important performance-related role, even when not used.
(But if a cluster index turns out to be never used in access paths, it's maybe time to choose an other index as cluster index...)

db2pd is the best tool to hunt for unused indexes:Look for the "TCB Index Stats:" header and the "Scans" column.
This will tell you how many times DB2 used that index. If the counter is ZERO, DB2 did not need that index at all so.......
Be ware:
1 - when a database stops and starts again all the counters are reset to ZERO
2 - beware of FK contraints. The index might be required as FK.

AFAIK, an index is never required to support FKs.
An index on a FK column can of course be helpful for join queries where the join condition happens to be "PK = FK" and where there is either a strong condition on the PK column, or the FK table is the inner table of a nested loop join.

Peter is right. In an FK-PK relationship, the only requirement is that a unique constraint exists on the PK columns, and in DB2 such a unique constraint is enforced with the help of a (unique) index.

I know, I know. What I mean is:
Suppose there is a function in the application to DELETE a code from a table. This code is used as a FK in many other tables and because it's an online/realtime function in your application, the developer made sure that all FK's have an index.
Now we, as "know-it-all" DBA run some analysis, identify unsed indexes and throw them away.... We all know that disaster strikes when the code-deletion application is executed.
So I mean that you'll have to check with the developers.

... and they probably don't know either. ;-)

Thank you Markus, stolze & peter for your valuable comments.

And Thanks detez, for your suggestion. I was not aware of such a use of "db2pd".

Thank you ALL , I really got more than what i expected.

That's why this is my favorite forum