Shulou(Shulou.com)06/01 Report--

Db2pd can view the application's access to tables and indexes in the database

Db2pd-db dbname-tcbstats all-file db2pd_tab_all.txt

Sometimes you just want to see access to a table and the indexes on it

Db2pd-db dbname-tcbstats all tbspaceid=id1 tableid=id2

The id1,id2 here can be obtained by query.

Db2 "SELECT TBSPACEID, TABLEID FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SCHEMANAME' AND TABNAME =' TABLENAME'"

In the output "TCB Index Stats:" section of the db2pd, you can see the IID of the index and the information Scans,IxOnlyScns of the index access.

The total number of accesses to an index since the database was started is: Scans + IxOnlyScns

If you want to see the index name, you can get it through the following query

Db2 "SELECT INDSCHEMA, INDNAME FROM SYSCAT.INDEXES WHERE TABSCHEMA = 'SCHEMANAME' AND TABNAME =' TABLENAME' AND IID = 3"

Since DB2 9.7, this information can be easily obtained using table functions.

SELECT

SUBSTR (SI.INDSCHEMA, 1,30) AS INDSCHEMA

SUBSTR (SI.INDNAME, 1,30) AS INDNAME

MGI.INDEX_SCANS

MGI.INDEX_ONLY_SCANS

FROM

TABLE (MON_GET_INDEX ('EPRICER',' CTMTTRN',-2)) as MGI

SYSCAT.INDEXES AS SI

WHERE

MGI.TABSCHEMA = SI.TABSCHEMA

AND MGI.TABNAME = SI.TABNAME

AND MGI.IID = SI.IID

ORDER BY

MGI.INDEX_SCANS DESC

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.