Tuning DB2: Snapshots

To tune 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

Tablespace         
db2 get snapshot for tablespaces on pqr

Bufferpool         
db2 get snapshot for bufferpools on pqr

Applications    
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