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).

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset