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

Source: Internet
Author: User

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

 
 
  1. SELECT INDSCHEMA, INDNAME, TABNAME   
  2.   FROM SYSCAT.INDEXES  
  3. 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.

 
 
  1. $ 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.

 
 
  1. Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:09:45  
  2.  
  3. TCB Table Information:  
  4. Address    TbspaceID TableID PartID MasterTbs MasterTab TableName  
  5. 0x7C6EF8A0 0         1       n/a    0         1         SYSBOOT    
  6. 0x7A0AC6A0 2         -1      n/a    2         -1        INTERNAL   
  7.  
  8. TCB Table Stats:  
  9. Address    TableName          Scans      UDI        RTSUDI  
  10. 0x7C6EF8A0 SYSBOOT            1          0          0       
  11. 0x7A0AC6A0 INTERNAL           0          0          0       
  12.  
  13. TCB Index Information:  
  14. Address    InxTbspace ObjectID TbspaceID TableID MasterTbs   
  15. 0x7A0ABDA8 0          5        0         5       0           
  16. 0x7A0ABDA8 0          5        0         5       0           
  17.  
  18. TCB Index Stats:  
  19. Address    TableName          IID   EmpPgDel   RootSplits BndrySplts PseuEmptPg Scans     
  20. 0x7A0ABDA8 SYSTABLES          9     0          0          0          0          0         
  21. 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.