How can DBAs locate unused indexes, tables, and packages in DB2?

BKJIA: From DB2 9.7, you can use the following query to check indexes not used in DB2:

 
 

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

The LASTUSED column tells you the last access time of the index in DB2, but this method does not guarantee that returns an accurate value, because DB2 also regards the update time of this column as the access time, however, you can use the db2pd tool to accurately obtain the last access time of the index since the database was started.

This query can only work in DB2 9.7 or later versions, but db2pd can be used in other versions, in SYSCAT. TABLES, SYSCAT. INDEXES and SYSCAT. a lastused column has been added to the PACKAGES table. Therefore, starting from DB2 9.7, you can easily query unused indexes, tables, and PACKAGES.

 
 

  
  
$ db2pd -d sample -tcbstats index 
 
 

When you run the db2pd tool on the SAMPLE database, use the tcbstats option to pass the index parameter to it. You will see a long string of output content. When you view the TCB Index information, you need to find the SCANS column. You must associate Index IDIID with the Index name through the catalog table.

 
 

  
  
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 above output is concise and beautiful. I have made a cut, the index name is associated with the IID, and the index is searched using Scans = 0.

If your database has been running for one month, you can run the db2pd tool to find indexes that have never been used for one month. When you run the db2pd tool and the database is active, all the information is stored for a very short time, but SYSCAT. TABLES, SYSCAT. INDEXES and SYSCAT. the information of the LASTUSED column in the PACKAGES table is permanently stored. With this, you can find out the last access time of the object. Remember that DB2 for z/OS had this function long ago, DB2 LUW now has this feature.

Source: http://www.db2ude.com /? Q = node/127

Original article name: Unused indexes, tables and packages in DB2