Personal tools
You are here: Home → DB2 → How To's → How to identify unused indexes on DB2
Navigation
Log in


Forgot your password?
 
Document Actions

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/
Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls