What are the "Tips" for DB2 not using indexes?

The following article mainly introduces how to find out tables and packages without using indexes in DB2. The following article describes the correct operation content, we all know how to locate tables and packages. We all know that in actual operations, the LASTUSED column mainly tells you the last access time of the index in the DB2 database.

However, this method does not guarantee that returns an accurate value.

Because DB2 also regards the update time of this column as the access time, but you can use the db2pd tool to accurately obtain the last access time that DB2 has not used indexes since the database was started.

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 DB2 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 the Index ID (IID) and 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 made a cut. DB2 did not use the index name to associate IID and used Scans = 0 to search for the index.

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.