How to identify unused indexes on DB2

usinf db2pd -tcbstats

The db2pd utility is generally used for problem determination and monitoring purposes, but you can also use it to query information about database activity. One piece of information that you can obtain with the db2pd utility is the number of times indexes have been accessed since the database was activated. Listing 7 shows how to use the db2pd utility to get this information for all indexes on all tables in a database.

Listing 7. Query table and index metrics with the db2pd utility

db2pd -db sample -tcbstats all -file db2pd_tab_all.txt

The options on the above call to the db2pd utility have the following meanings:

• -db Database name
• -tcbstats all Show all table and index metrics
• -file Output file

If you want to restrict the output of the db2pd utility to show metrics for only a certain table and its indexes, use the -tcbstats option to specify the tablespace ID and table ID. To do this, you first need to determine the tablespace ID and table ID of the table by executing a SELECT statement on the SYSCAT.TABLES catalog view, as shown in Listing 8.

Listing 8. Query database catalog to determine tablespace ID and table ID of a table

db2 "SELECT TBSPACEID, TABLEID FROM SYSCAT.TABLES WHERE TABSCHEMA = 'FECHNER' AND TABNAME = 'DEPARTMENT'"

Listing 9 shows a sample result set from a tablespace ID and table ID query such as the one above.

Listing 9. Result set of tablespace ID and table ID query

TBSPACEID TABLEID --------- ------- 2 5 1 record(s) selected.

After determining the tablespace ID and table ID of the table you want to query, you can restrict the db2pd output to just that table. You do this by including the tbspaceid and tableid suboptions following the -tcbstats option, as shown in Listing 10.

Listing 10. Restrict db2pd -tcbstats output to a specific table

db2pd -db sample -tcbstats all tbspaceid=2 tableid=5 -file db2pd_tab_dept.txt

Listing 11 shows a sample result set from a db2pd query such as the one above.

Listing 11. Table and index metrics as shown by the db2pd utility to DB2 9.7

Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:20:34 TCB Table Information: Address TbspaceID TableID PartID MasterTbs MasterTab TableName SchemaNm ObjClass DataSize LfSize LobSize XMLSize 0x797DF2B8 2 5 n/a 2 5 DEPARTMENT FECHNER Perm 1 0 0 0 TCB Table Stats: Address TableName SchemaNm Scans UDI RTSUDI PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads OvFlCrtes RowsComp RowsUncomp CCLogReads StoreBytes BytesSaved 0x797DF2B8 DEPARTMENT FECHNER 0 0 0 0 0 951 0 0 0 0 0 0 0 0 0 - - TCB Index Information: Address InxTbspace ObjectID PartID TbspaceID TableID MasterTbs MasterTab TableName SchemaNm IID IndexObjSize 0x797E0330 2 5 n/a 2 5 2 5 DEPARTMENT FECHNER 3 8 0x797E0330 2 5 n/a 2 5 2 5 DEPARTMENT FECHNER 2 8 0x797E0330 2 5 n/a 2 5 2 5 DEPARTMENT FECHNER 1 8 TCB Index Stats: Address TableName IID PartID EmpPgDel RootSplits BndrySplts PseuEmptPg EmPgMkdUsd Scans IxOnlyScns KeyUpdates InclUpdats NonBndSpts PgAllocs Merges PseuDels DelClean IntNodSpl 0x797E0330 DEPARTMENT 3 n/a 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0x797E0330 DEPARTMENT 2 n/a 0 0 0 0 0 678 0 0 0 0 1 0 0 0 0 0x797E0330 DEPARTMENT 1 n/a 0 0 0 0 0 245 0 0 0 0 1 0 0 0 0

The part of the result set that concerns index usage is in the TCB Index Stats section. The sample output above shows that the DEPARTMENT table has three indexes. The indexes are shown by their index IDs (the IID column), not by their names. The Scans column shows how many times the indexes were accessed since the database was activated:

• The index with ID 1 was accessed 245 times.
• The index with ID 2 678 times.
• The index with ID 3 was never accessed.

Because the index with ID 3 has not been accessed, you can conclude that it is not used, or at least that it is seldom used. To get the name of the index with ID 3, query the SYSCAT.INDEXES catalog view using a SELECT statement, as shown in Listing 12.

Listing 12. Query database catalog to determine an index name based on its index ID (IID)

db2 "SELECT INDSCHEMA, INDNAME FROM SYSCAT.INDEXES WHERE TABSCHEMA = 'FECHNER' AND TABNAME = 'DEPARTMENT' AND IID = 3"

The output of the above SELECT statement is shown in Listing 13.

Listing 13. Result set of index name query

INDSCHEMA INDNAME ------------------------------ ------------------------------ FECHNER XDEPT3 1 record(s) selected.

Listing 14. Result set of index details

db2 describe indexes for table FECHNER.DEPARTMENT show detail

In addition to the Scans column, the TCB Index Stats section of the db2pd utility results has a IxOnlyScns column that shows the number of index only scans. An Index only scan is an index access that is not followed by a table access because the index itself contains all the requested data. So if the IxOnlyScns counter is not zero, you have to add the numbers of both the Scans column and the IxOnlyScns column to get the total number of times the index has been accessed.
You have to be careful when interpreting index usage information. The same is true when using the db2pd approach. The fact that a certain index has not been used up until a certain point in time, does not necessarily mean that the index will never be used. So be cautious when deciding whether or not to drop an index that appears not to be used. If you do decide to drop an index, you should first save its CREATE INDEX statement so that you can easily recreate it if necessary.
Listing 15. Table and index metrics as shown by the db2pd utility to DB2 9.1 and 9.5

Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:20:34 Database Partition -1 -- Database MNTTBPP -- Active -- Up 8 days 22:53:29 TCB Table Information: Address TbspaceID TableID PartID MasterTbs MasterTab TableName SchemaNm ObjClass DataSize LfSize LobSize XMLSize 0x0000000134C8E6F8 2 17 n/a 2 17 TMPVALORITEMCOLETA MNT Perm 4300 0 0 0 TCB Table Stats: Address TableName Scans UDI PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads OvFlCrtes 0x0000000134C8E6F8 TMPVALORITEMCOLETA 389 7666 0 0 4361401580 0 5086480 0 0 0 0 TCB Index Information: Address InxTbspace ObjectID TbspaceID TableID MasterTbs MasterTab TableName SchemaNm IID IndexObjSize 0x0000000134F2F1C0 2 17 2 17 2 17 TMPVALORITEMCOLETA MNT 3 2092 0x0000000134F2F1C0 2 17 2 17 2 17 TMPVALORITEMCOLETA MNT 2 2092 0x0000000134F2F1C0 2 17 2 17 2 17 TMPVALORITEMCOLETA MNT 1 2092 TCB Index Stats: Address TableName IID EmpPgDel RootSplits BndrySplts PseuEmptPg Scans KeyUpdates InclUpdats NonBndSpts PgAllocs Merges PseuDels DelClean IntNodSpl 0x0000000134F2F1C0 TMPVALORITEMCOLETA 3 0 0 20 0 501 0 0 68 0 0 0 0 1 0x0000000134F2F1C0 TMPVALORITEMCOLETA 2 0 1 0 0 1071 0 0 176 136 0 0 0 0 0x0000000134F2F1C0 TMPVALORITEMCOLETA 1 0 2 268 0 0 0 0 0 207 0 0

SOURCE: https://www.ibm.com/developerworks/data/library/techarticle/dm-0910db2unusedindex/