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