Are my indexes used ?
Often I wonder out of all those 100s of indexes on my siebel database what all indexes are really used and what other indexes that Siebel (now Oracle) has graced us with that is simply sitting there wasting resources. There is no easy way out to see what all indexes are used by a certain ETL or during a batch window other than explaining each and every sql.  db2pd captures index scans on a database, so I wrote a small script identify unused indexes since the instance was started. Remember ! the stats reset every time the instance is started, so my advice would be to have the database run for a while before you run the script.
The script spits out two files, one file has the index ids that were scanned (used) and the other has the list that are not used. You look up the iids on the sysibm.sysindexes to see the actual index names.
I hope this script helps and let me know if you have any comments or find any issues with this :Â index_usage