DB2, from version 9.7, allows to know easily what indexes are not being used in a Database. The query is also for tables and packages.

This is a useful tool for tuning indexes and detecting problems in their use.

After version 9.7 DB2 includes a new LASTUSED field in the SYSCAT.INDEXES, SYSCAT.TABLES and SYSCAT.PACKAGES tables.

This field indicates the date of last use of indexes, tables or packages.

For example, to query unused indexes since 1/1/2019, you could use this simple query:

SELECT INDSCHEMA, INDNAME, TABNAME
FROM SYSCAT.INDEXES
WHERE LASTUSED = '01/01/2019';

Very useful to drop nonsense indexes.

This mini tip show you how to know when was the last time you restart a DB2 server on a UNIX / LINUX.

> db2 get snapshot for dbm | grep Start

Start Database Manager timestamp = 04/02/2011 13:45:20.516473

Simple, isn't it?