Hi my db is V8.1 fixpak12.i have question
is there any way in db2 to recognize the unused indexes present on the tables?.
i think one way is to use the eventmonitor i guess.other than this is there any tool which shows the unused indexes if present bunch of SQL select queries.
in oracle there is a command
ALTER INDEX indexname MONITORING USAGE
i think this command let us know whether or not an index is used.
is there any similar thing present in db2?
to what extent db2pd -tcbstats will be helpful in this scenario?.
any help is appreciated.
thanks.

You can use the DB2 Design Advisor to get recommendations on additional indexes that would benefit the given workload - or to remove indexes that are not used by the workload.

I don't see how, given a set of existing indexes, the Index Advisor will tell you which existing indexes are not being used. Maybe this is just my ignorance.

if all sql would be static, you could find out the not used indexes, but most of the time, the dynamic sql is not known and unused indexes are difficult to find..
dynamic statement cache is only a snapshot at specific moment in time and could be different from other moments in time

The Design Advisor only operates on the given workload. If the workload does not use some of the existing indexes, the advisor will say so. Of course, if the workload you provided is only part of the real workload, it would be foolish to simply drop all indexes that are not used by the provided workload because other SQL statements may make good use of those indexes.

One way to approach this would be to collect all dynamic SQL statements issued by the application over a representative interval (e.g. a week) using snapshots, then explain all of them and query the explain_object (I think that's the right table) for index names that were used.

Obviously, this method is limited by the fact that you won't be able to collect static SQL data.

It seems to me that for this to work, every index suggested by the index advisor must exist in the exact specification as the index advisor recommends, which in my opinion and experience is a very bad situation to be in.

One method I've been looking at is db2pd -tcbstats. To see the index stats I run "db2pd -tcbstats index <tablespace_id> <table_id>". It gives a bunch of info, the problem is figuring out what the information means. Specifically, there is a column in the output called scans, which I would hope means how many times an index is scanned. I was hoping in my environment that this value would be 0 for some, meaning that I could probably do without those indexes. Unfortunately, that was not the case. Does anyone know how to figure out what the other output colums from that command mean? I've asked our IBM tech rep and I've logged a PMR but haven't found any good documentation yet.

There are some other potential aspects of index "use" that the Design Advisor will never catch:
- a unique index has, as everybody knows, some interesting side effects even if not used in an access path
- the mere existence of an index can give the optimizer certain information which it would otherwise not have, e.g. index-based statistics. Dropping such an index will potentially change access paths (in a potentially negative way) unless one is aware of the fact and generates non-index-based statistics that carry the same information (if at all possible).

(I can give more details for DB2 on z/OS for those of you interested in it.)

Bottom line: there is no 100% foolproof automatic way to do this; one has to know & understand the database design and the applications/queries typically used against it.