IBM Support

75 ways to demystify DB2: #34: Techtip :A quick way to identify the top N heavy scanned tables in the database

Technical Blog Post


75 ways to demystify DB2: #34: Techtip :A quick way to identify the top N heavy scanned tables in the database



There would be a requirement for locating the top N heavy accessing tables in the database for the performance diagnostic purpose. A common tool called db2top is mostly used by DBAs for tracking such performance metrics. However, db2top monitoring has a big performance impact because it uses db2 snapshot mechanism to maintain the counters in the monitor heap which incurs a lot of performance overhead.

This article introduces an easy and quick method to detect your top N  heavy scanned tables in the database by using db2pd. db2pd directly accesses the table control block structure with almost zero performance overhead.

How to:

Here is a simple script command which uses db2pd -tcbs option to quickly identify the top 3 heavy scanned tables of mydb (database name) which almost has no extra performance impact to the system.

db2pd -db mydb -tcbs | grep -p "TCB Table Stats" | sort -k9n | tail -3

Sample output from the above command:

TCB Table Stats:

Address          SchemaNm  TableName  Scans    UDI     RTSUDI  PgReorgs  NoChgUpdts  Reads
0x0700001701F7BED8    T1        SAM        916       33303    33303      36503      0          9347531108*
0x070000170553B458    T2        SAM       2690     530207  530207    3846     125828     9047421239*
0x070000170171D158    T3 SAM      5226600   2335     2335      498       0 7639212299*

The results are sorted by column 9 which is 'Reads' whose meaning is how many rows on the table are scanned.

Note that the results are not sorted by column 4 'Scans' because it only counts the access times of a table not for rows, more scan times do not mean more row access times ,e.g if table1 is scanned 1000 times but one scan only for accessing one row ,in contrast, if table2 is only scanned 10 times, but for each scan it needs to access 1 million rows, then table2 should be a hotter table than table1.






[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]