Tuning DB2: Snapshots

To tuning a database is useful to create snapshots that capture information from different structures/components of the instance.

In this post we show how they can be used to perform an analysis of tuning.

First of all, the monitor switches must be on to collect data from the different structures.

The state monitors are available as follows:

db2pqr 2> db2 -v get monitor switches

Monitor Recording Switches

Switch list for db partition number 0

Buffer Pool Activity (BUFFERPOOL) = ON 01/19/2012 18:10:56.183312
Lock Information (LOCK) = ON 01/19/2012 18:10:56.183312
Sorting Information (SORT) = ON 01/19/2012 18:10:56.183312
SQL Statement Information (STATEMENT) = ON 01/19/2012 18:10:56.183312
Table Activity Information (TABLE) = ON 01/19/2012 18:10:56.183312
Take Timestamp Information (TIMESTAMP) = ON 01/19/2012 18:10:56.183312
Unit of Work Information (UOW) = ON 01/19/2012 18:10:56.183312

In case they are disabled, they can be activated by the sentence “update monitor switches”, or through “update dbm config”:

db2 -v update monitor switches using bufferpool on
db2 -v update monitor switches using lock on
db2 -v update monitor switches using sort on
db2 -v update monitor switches using statement on
db2 -v update monitor switches using table on
db2 -v update monitor switches using timestamp on
db2 -v update monitor switches using UOW on

One way to do a study of tuning may be:
1. Check status monitors
2. Reset all metrics
3. Launch the processes/querys that needs the tuning, or just the normal workload of the database
4. Capture the appropriate snapshot

Reset all metrics:

db2 -v reset monitor all

Capture of the different types of snapshots: (usually for performance problems with dbm / db / bufferpool is enought)

Locks db2 get snapshot for locks on pqr
Database Manager    db2 get snapshot for dbm
Database db2 get snapshot for database on pqr
Tablespaces db2 get snapshot for tablespaces on pqr
Bufferpool db2 get snapshot for bufferpools on pqr
Applicationes db2 get snapshot for applications on pqr
Dynamic SQL db2 get snapshot for dynamic sql on pqr
Tables db2 get snapshot for tables on pqr

I hope that you find it useful!

If you like this post write a comment or subscribe you to our feed to obtain future posts in your feed reader.


No comments.

Add a comment