One way to make your DB2 database use less storage and speed up Inserts and Updates is to drop the indexes that are never used. As the application changes and the database workload changes, you may find many indexes are no longer needed. Dropping these will free up tablespace pages, not to mention speed up database backups and general database maintenance.
Starting with DB2 version 9.7 there is a great table function called MON_GET_INDEX. You can see all kinds of index monitoring elements including the INDEX_SCANS. Once you find indexes that are never scanned, you can drop them
Here is a good query for finding indexes with zero scans.
SELECT substr(T.tabschema,1,10) as TABSCHEMA,substr(T.tabname,1, 20) AS TABNAME,substr(S.INDSCHEMA,1,10) as INDSCHEMA,substr(S.INDNAME,1,20) AS IND_NAME,indextype,T.INDEX_SCANS as INDEX_SCANS FROM TABLE(MON_GET_INDEX('','', -1)) as T, SYSCAT.INDEXES AS S WHERE T.TABSCHEMA = S.TABSCHEMA AND T.TABNAME = S.TABNAME AND T.IID = S.IID and T.INDEX_SCANS = 0;
Once you identify indexes you would like to drop, generate the DDL needed to recreate the index using your favorite method (ie. db2look) and save it to a file. Then drop the index and execute RUNSTATS on the table and it’s remaining indexes. Now enjoy a slimmer and faster DB2 database!