Are my indexes used ? – Part II
Last year, I wrote an article and a script that helps to identify unused indexes in DB2 UDB.  Its unfortunate, IBM doesn’t provide an easy way of identifying them. In DB2 Z/os, you can look up SMF records or use DB2PM or BMC to find them pretty easily. Oracle and Microsoft provides native tools to identify them easily, but not in UDB, unless you buy Query Patrol.  Of course like I mentioned last year, you can run my script to identify those unused indexes, but there is a bit of  translation work that you had to do. Well now I have done the translation part for you.  Earlier the script only showed the index id (IID), you had to manually look up the numbers on syscat.indexes or sysibm.indexes to identify the actual index, now my script will  list out the actual index name , column names and so on.  The script will also load the data into a table on the database and will run a MERGE script to populate the details.  The index_usage.zip contains three files :
1. unused_indexes.ddl
2. index_usage.sh
3. update_unused_indexes.sql
As always, do due diligence and review my scripts before you execute it. Â Â If you find any issues or questions please let me know