DB2 Monitor Utilities

refer: http://www.ebenner.com/db2dba_blog/?p=168

The DB2 System Monitor is the database administrator’s ally in evaluating database performance. The benefit, however, from having detailed metrics about your database’s behavior has to be weighed against potential database performance degradation when either executing GET SNAPSHOT or activating an event monitor. In this post I compare the relative cost of using snapshot, event monitor, and other performance utilities, and demonstrate how to manage their potential risks.

First, a word about LATCHES. The System Monitor (via GET SNAPSHOT or through activation of an event monitor) acquires LATCHES ( “. . .  low level serialization mechanisms used to protect shared data structures . . . ”¹ ) to ensure accurate reporting. System Monitor latches can enqueue (delay) any database process or operation. Both user applications and database utilities may be affected. Significant global delays due to latches can cause a database hang (hang-wait) condition.² Hang-waits can have many secondary symptoms: statements appear to be in lock contention but there is no obvious resource in contention; connection attempts hanging; utilities refusing to terminate.
DB2 monitoring and problem determination utilities vary widely in their database performance impact.

• db2pd (first made available in DB2 LUW version 8.2) causes the least impact, because it does not acquire latches. db2pd as a relatively new utility is not as easily used as snapshots or event monitors, but it appears to be winning mindshare among database administrators. It’s text output is even more difficult to summarize and parse than either snapshot or event monitor output (db2pd will be the subject of a future post in this blog).
• GET SNAPSHOT has a somewhat heavier potential performance footprint (which varies by number of monitor switches active, type of workload, and mix of static vs. dynamic SQL); GET SNAPSHOT has, according to IBM Toronto Laboratory’s Steve Rees³ , anywhere from a 1-10% effect.⁴
• Event monitors have potentially the heaviest impact on a database and pose the greatest (potential) risk (depending upon the kind of monitor, transactions per minute, and event monitor parameters). Because the Event Monitor is a trace, the performance impact is directly proportionate to the number of events being captured. Carelessly configured STATEMENTS and TRANSACTIONS event monitors can do harm in a high Transactions Per Minute (TPM) environment ( TPM > 3000 ).