Starting version DB2 9.7, you can use following query to detect unused indexes in DB2.
SELECT INDSCHEMA, INDNAME, TABNAME FROM SYSCAT.INDEXES WHERE LASTUSED = '01/01/0001';
The column LASTUSED tells when that index was last accessed in DB2. This is not per se a guaranteed solution that will show right value in LASTUSED column when you query it since DB2 will figure out when to update this column on its own. However, you can use db2pd tool to know definitely when an index was last used since database was activated.
This query will work only in DB2 V9.7 or higher but you can still use db2pd tool for other versions. The LASTUSED column has been added to SYSCAT.TABLES, SYSCAT.INDEXES and SYSCAT.PACKAGES. So, you will be able to figure out unused tables, indexes and packages starting DB2 9.7 using simple queries rather than interpreting output from the db2pd tool.
$ db2pd -d sample -tcbstats index
When you run db2pd tools for SAMPLE database, use tcbstats option and provide index argument to it. You will see a long output but you need to look for a column SCANS when you see TCB Index information. Again, you have to co-relate the Index ID (IID) to the index name through the catalog tables.
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:09:45 TCB Table Information: Address TbspaceID TableID PartID MasterTbs MasterTab TableName 0x7C6EF8A0 0 1 n/a 0 1 SYSBOOT 0x7A0AC6A0 2 -1 n/a 2 -1 INTERNAL TCB Table Stats: Address TableName Scans UDI RTSUDI 0x7C6EF8A0 SYSBOOT 1 0 0 0x7A0AC6A0 INTERNAL 0 0 0 TCB Index Information: Address InxTbspace ObjectID TbspaceID TableID MasterTbs 0x7A0ABDA8 0 5 0 5 0 0x7A0ABDA8 0 5 0 5 0 TCB Index Stats: Address TableName IID EmpPgDel RootSplits BndrySplts PseuEmptPg Scans 0x7A0ABDA8 SYSTABLES 9 0 0 0 0 0 0x7A0ABDA8 SYSTABLES 8 0 0 0 0 0
The output above is trimmed for brevity. So, relate IID to the index name and look for the indexes having Scans=0.
If your database has been active for a month, you can run the db2pd tool and find out the indexes which have not been scanned in last one month.
All of this information is only transient and available from the time your database was activated when you use db2pd tool. However, the LASTUSED column in SYSCAT.TABLES, SYSCAT.INDEXES and SYSCAT.PACKAGES will persist the information and it is meant to give you an idea when that object was last accessed. Please remember that DB2 on z/OS provided this capability for a long time and DB2 LUW is now providing same capability.