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
