DB2 event monitor and snapshot monitor

I collect database usage information in two different ways. Snapshots and Monitoring Events.  Either  method collects real-time  database usage information such as SQL queries, buffer pool activity and locking information.

 An event monitor is a statement that defines a monitor to record defined events in a database

 A snapshot monitor captures information about a database and connected applications at a given time.

 Use a snapshot monitor when:

1)   You want to capture database information at a specific point-in-time

2)  You want to observe trends.  Also, allows you to forecast problems

3)  Troubleshoot database performance  problems during a  known time period.

4)  Ad-hoc database health review

 Use event monitors when:

1)  Require event-driven information

2)  Needing data triggered by an event

3)  Requiring more detailed object-based statistics such as tablespace statistics

 Event monitoring is resource intensive and can have a negative performance impact on a busy Production system.  Some points to consider when applying event monitoring

1)     Plan which event monitoring to use in separate batches. The STATEMENTS Event Type is the most likely candidate to have a negative performance hit on the database

 CREATE EVENT MONITOR mystmon1 FOR STATEMENTS

2)   Plan a test run on the test server or a shorter run on a Production Server

3)   Monitor CPU and set a threshold . Once the threshold is met , stop the monoitor

Read More

Trace sql statements in DB2 database

DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis

DB2 Tuning Toolkit – db2exfmt