16.8. Event Monitors
While Snapshot Monitors let you “take a picture” of your system at a given time, Event Monitors allow you to collect information based on an event. A typical example is the case of deadlocks. Since it is very difficult to determine when a deadlock is going to happen, taking a snapshot at the right time is almost impossible. Instead, you can use an Event Monitor, which will automatically collect the deadlock information when it occurs. Event Monitors can also be used to collect static and dynamic SQL statements.
The following example shows a sequence of statements that illustrate how to collect Event Monitor information using commands.
(1) create event monitor mymon1 for database, statements write to file 'e: emp'
(2) set event monitor mymon1 STATE=1
(3) select * from employee
(4) set event monitor mymon1 STATE=0
(5) drop event monitor mymon1
(6) db2evmon -path e: emps
In the example, (1) is the statement used to create the Event Monitor mymon1, which specifically collects DATABASE and STATEMENTS events. Other events that can be collected are DEADLOCKS, TABLESPACES, BUFFERPOOLS, CONNECTIONS and TRANSACTIONS.
In (2), the Event Monitor is turned on. (3) is used as an example of an SQL statement that should be captured by the Event Monitor. In (4) the Event Monitor is turned off. In (5) the Event Monitor is dropped or deleted, and in (6) the collected information is analyzed with the command db2evmon, the command line version of the Event Analyzer.
Figure 16.16 shows part of the output of the db2evmon command after the previous sequence of statements were performed.
Figure 16.16. Output of the db2evmon command
--------------------------------------------------------------------------
EVENT LOG HEADER
Event Monitor name: MYMON1
Server Product ID: SQL08020
Version of event monitor data: 7
Byte order: LITTLE ENDIAN
Number of nodes in db2 instance: 1
Codepage of database: 1252
Territory code of database: 1
Server instance name: DB2
--------------------------------------------------------------------------
--------------------------------------------------------------------------
Database Name: SAMPLE
Database Path: H:DB2NODE0000SQL00003
First connection timestamp: 06/06/2005 03:10:25.965440
Event Monitor Start time: 06/08/2005 02:49:26.271226
--------------------------------------------------------------------------
3) Connection Header Event ...
Appl Handle: 249
Appl Id: *LOCAL.DB2.016DC5204042
Appl Seq number: 0014
DRDA AS Correlation Token: *LOCAL.DB2.016DC5204042
Program Name : db2bp.exe
Authorization Id: DB2ADMIN
Execution Id : DB2ADMIN
Codepage Id: 1252
Territory code: 1
Client Process Id: 3048
Client Database Alias: SAMPLE
Client Product Id: SQL08020
Client Platform: Unknown
Client Communication Protocol: Local
Client Network Name:
Connect timestamp: 06/06/2005 03:11:27.082303
4) Statement Event ...
Appl Handle: 249
Appl Id: *LOCAL.DB2.016DC5204042
Appl Seq number: 0014
Record is the result of a flush: FALSE
-------------------------------------------
Operation: Static Commit
Package :
Consistency Token :
Package Version ID :
Cursor :
Cursor was blocking: FALSE
-------------------------------------------
Start Time: 06/08/2005 02:49:26.367666
Stop Time: 06/08/2005 02:49:26.379876
Exec Time: 0.012210 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
5) Statement Event ...
Appl Handle: 249
Appl Id: *LOCAL.DB2.016DC5204042
Appl Seq number: 0015
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Prepare
Section : 201
Creator : NULLID
Package : SQLC2E03
Consistency Token : AAAAAJHR
Package Version ID :
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : select * from employee
-------------------------------------------
Start Time: 06/08/2005 02:49:37.126020
Stop Time: 06/08/2005 02:49:37.163379
Exec Time: 0.037359 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.010014 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
...
|
You can also create Event Monitors through the Control Center (see section 11.6.5, Event Monitoring, for an example).