Snapshot table function

Information returned

SNAPSHOT_DYN_SQL

Point in time statement information from the SQL 

statement cache for the database.

Similar to snapshot commands, the amount of information returned from table 

snapshots functions is controlled by the monitor switches. Because snapshots 

can collect large amounts of diagnostic data, enabling all monitor switches 

(especially DYNAMIC SQL) can have a very negative impact on database 

performance. 

All the monitoring utilities use memory heap, controlled by the 

MON_HEAP_SZ

database manager parameter. This monitoring heap size should be increased 

when many applications access snapshot data.

Event monitoring

Event monitors are used to monitor the performance of DB2 over a fixed period 

of time. The information that can be captured by an event monitor is similar to the 

snapshots, but event monitors examine transition events in the database, and 

consider each event as an object. Event monitors can capture information about 

DB2 events in the following areas:

Database

: An event of database information is recorded when the last 

application disconnects from the database.

Tables

: All active table events will be recorded when the last application 

disconnects from the database. An active table is one which has been altered 

or created since the database was activated. The monitor captures the 

number of rows read and written to the table.

Deadlocks

: A deadlock event is recorded immediately when a deadlock 

occurs. This monitor also has an additional option 

WITH DETAILS

. This option 

will capture additional information, like which SQL statement was being 

executed when the deadlock occurred, and what locks were held by the 

application that encountered the deadlock. The information captured by the 

monitor focuses on the locks involved in the deadlock and the applications 

that own them.

Buffer pools

: A buffer pool event is recorded when the last application 

disconnects from the database. The information captured contains the type 

and volume of use of the buffer pool, use of pre fetchers and page cleaners, 

and whether or not direct I/O was used.

Table spaces

: A table space event is recorded when the last application 

disconnects from the database. This monitor captures the same information 

as the buffer pool monitor, but the information is summarized at a table space 

level. 

292 

MySQL to DB2 UDB Conversion Guide