Section 16.8. Event Monitors


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:\temp'
(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:\temps

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:\DB2\NODE0000\SQL00003\   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).



Understanding DB2(R. Learning Visually with Examples)
Understanding DB2: Learning Visually with Examples (2nd Edition)
ISBN: 0131580183
EAN: 2147483647
Year: 2004
Pages: 313

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net