DB2 Tuning Tables

19 December,2011 by Jack Vamvas

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”

Statements_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

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 Tuning Tables

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright