Mastering DB2 Indexes: Scan Types, Design Principles, and Deep Cleanup Strategies

Deep Index Cleanup

DB2 V9.5 and Earlier

Run the command: db2pd -d <DBName> -tcb index[all] Examine the “TCB Index Stats” block, focusing on the Scans and IxOnlyScns columns. An index with values orders of magnitude lower than other indexes on the same table is likely rarely used and can be considered for removal. Note that db2pd reports statistics accumulated since the database was activated; a long uptime that covers typical business cycles yields more reliable data.

DB2 V9.7 and Later

Query the system view SYSCAT.INDEXES.LASTUSED to obtain the last usage date of each index.

SELECT INDNAME, LASTUSED, CREATE_TIME
FROM SYSCAT.INDEXES
WHERE TABNAME = '<TABLE>';

If LASTUSED = '0001-01-01', the index has never been used since creation. Combine this with CREATE_TIME and the business cycle to decide whether to keep it.

For indexes with a non‑default LASTUSED, also consider the scan counts from db2pd to distinguish infrequently used indexes from those used only occasionally.

Assess index clustering efficiency via SYSCAT.INDEXES.CLUSTERRATIO or by comparing FULLKEYCARD with SYSCAT.TABLES.CARD. A low clustering ratio indicates poor data locality and higher maintenance cost, suggesting the index may be a candidate for removal.

Before dropping any index, ensure that statistics are up‑to‑date; stale statistics can lead to mis‑identification. Treat primary (type P) and unique (type U) indexes with extra caution, as they often enforce business constraints.