Description of the query solution for which DB2 does not use Indexes

This article mainly describes the actual operation steps for identifying tables and packages that DB2 does not use indexes. If you do not use indexes for DB2, if you are interested in the actual operation steps for tables and packages, you can click to view the following articles. We all know that the LASTUSED column tells you the exact time for the last access to the index in the DB2 database.

However, this method does not guarantee that an accurate value is returned, because DB2 also regards the time for updating 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.

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 = '2017/123 ';

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 the tables and PACKAGES that DB2 does not use.

$ 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 0 0x7A0ABDA8 0 5 0 5 0 TCB Index Stats: address TableName IID EmpPgDel RootSplits BndrySplts pseudo emptpg Scans 0x7A0ABDA8 tables Ables 9 0 0 0 0 0 0x7A0ABDA8 tables Ables 8 0 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.

The above content is an introduction to how to find out tables and packages without using indexes in DB2.

The above content is an introduction to the absence of indexes for DB2 and the identification of tables and packages. I hope you will gain some benefits.