DB2 Tuning Tables

Find bottlenecks in DB2 performance   is part of maintaining good response times. Here's a useful approach to locating slow  query response times. Focusing on scans

1)  Run a “user level” workload and check “get snapshot for database” .  Add “Rollback statements attempted” and “Commit statements attempted”   results in “Number of Transactions attempted”

2)  Multiply the Number of Transactions Attempted  by 5. Call it “Transaction Threshold”

3)  Based on the same workload execute “get snapshot for tables” , look for tables where “Rows Read” is greater than the Transaction Threshold. There is probability these tables require some tuning

 Analyse these tables for potential indexes, MQT, MDC or database partitioning

Materialised Query Table (MQT)  are useful for a number of scenarios , such as aggregate data over more than 1 dimension, joins and aggregate data over a number of tables, or data from data subset hotzones.

Multi Dimensional Clustering  (MDC) Tables  maintain the same logical structure as standard tables . MDC tables are organised into pages , containing rows of data. The main difference with standard tables is MDCs are organised into extent sized  blocks.

Related Posts

Database Tuning overview –Storage Tuning