Creating event monitors that write to tables

To create an event monitor, use the CREATE EVENT MONITOR STATEMENT. There are different forms of this statement that you use, depending on the type of events that you intend to monitor.

Before you begin

  • You need SQLADM or DBADM authority to create a table event monitor.
  • The target table of a CREATE EVENT MONITOR statement - that is, the table to which the event monitor writes its output - must be a non-partitioned table.

About this task

The various options for table event monitors are set in the CREATE EVENT MONITOR statement. For further assistance in generating CREATE EVENT MONITOR SQL statements for write-to-table event monitors, you can use the db2evtbl command. Simply provide the name of the event monitor and the required event type (or types), and the CREATE EVENT MONITOR statement is generated, complete with listings of all the target tables. You can then copy the generated statement, make modifications, and then execute the statement from the command line processor.

Procedure

To create an event monitor that writes its output to a regular table, perform the following steps:

  1. Formulate a CREATE EVENT MONITOR statement using the WRITE TO TABLE clause to indicate that event monitor data is to be collected in a table (or set of tables). 
    CREATE EVENT MONITOR evmon-name FOR eventtype
   WRITE TO TABLE
    Where evmon-name is the name of the event monitor, and eventtype is one of the following values:
    • ACTIVITIES
    • BUFFERPOOLS
    • CHANGE HISTORY
    • CONNECTIONS
    • DATABASE
    • DEADLOCKS
    • LOCKING
    • PACKAGE CACHE
    • STATEMENTS
    • STATISTICS
    • TABLE
    • TABLESPACE
    • THRESHOLD VIOLATIONS
    • TRANSACTIONS
    • UNIT OF WORK
    For example, to create a unit of work event monitor called myevmon, use a statement like the one that follows:
    CREATE EVENT MONITOR myevmon FOR UNIT OF WORK
   WRITE TO TABLE
    The preceding statement creates a unit of work event monitor that uses defaults for the logical groups of monitor elements collected, the corresponding output table names, and the target table spaces for the tables. For more information on these defaults, refer to the documentation for the appropriate CREATE EVENT MONITOR statement.
  2. Optional: Specify the logical groups for which you want data collected.
    By default, event data is collected for all logical data groups for the event monitor type. (See Target tables, control tables, and event monitor table management for details.) If you want only data for selected logical groups collected, you can specify the names of the logical groups to include in the CREATE EVENT MONITOR statement. For example, with a locking event monitor, you might want to collect only the information associated with the LOCK and PARTICIPANT logical groups. To include only these logical groups, you can use a statement like the one that follows: 
    CREATE EVENT MONITOR mylocks FOR LOCKING 
   WRITE TO TABLE
   LOCK, PARTICIPANTS
  3. Optional: Specify the table names to use for the output tables.
    Unless you specify otherwise, default names are used for the tables for each logical group of monitor elements. The default name used is derived by concatenating the logical group name with the name of the event monitor. For example, for the locking event monitor created by the statement in the preceding step, the unqualified names for the tables produced are LOCK_MYLOCKS and PARTICIPANTS_MYLOCKS. To override the default names, include the table names to use when specifying the logical groups:
    CREATE EVENT MONITOR mylocks FOR LOCKING 
   WRITE TO TABLE
   LOCK(TABLE LOCKDATA), PARTICIPANTS(TABLE PARTICIP)
    In the preceding example, the names used for the tables for the LOCK and PARTICIPANTS logical groups are LOCKDATA_MYLOCKS and PARTICIP_MYLOCKS.

    You can also override the table space to be used for each table by including the name of the table space to use:

    CREATE EVENT MONITOR mylocks FOR LOCKING 
  WRITE TO TABLE
  LOCK(TABLE LOCKDATA IN EVMONSPACE), PARTICIPANTS(TABLE PARTICIP IN EVMONSPACE)
    In the preceding example, the EVMONSPACE table space is used for both output tables.

Additional options

Different event monitors provide different configuration options. For details on the options available for a specific type of event monitor, refer to the documentation for the CREATE EVENT MONITOR statement for the type of event monitor you want to use. The examples that follow show some of the configuration options you can choose for different event monitors:
Capturing multiple event types with a single event monitor
Some types1 of event monitors can capture different types of events with a single event monitor. If you want to capture multiple types of events with this event monitor, specify additional values for eventtype, separated by a comma. For example, you might want to combine bufferpool and table space monitoring in a single event monitor: 
CREATE EVENT MONITOR myevmon FOR BUFFERPOOLS, TABLESPACES
                     WRITE TO TABLE
This event monitor will monitor for the BUFFERPOOL and TABLESPACE event types. Assuming that the previously listed statement was issued by the user dbadmin, the derived names and table spaces of the target tables are as follows:
  • DBADMIN.BUFFERPOOL_MYEVMON
  • DBADMIN.TABLESPACE_MYEVMON
  • DBADMIN.CONTROL_,MYEVMON
Adjusting the size of event monitor output buffers
You can alter the size of the table event monitor buffers (in 4K pages) for some types1 of event monitors by adjusting the BUFFERSIZE value. For example, in the following statement:
CREATE EVENT MONITOR myevmon FOR BUFFERPOOLS, TABLESPACES
                     WRITE TO TABLE BUFFERSIZE 8
8 is the combined capacity (in 4K pages) of the two event table buffers. This adds up to 32K of buffer space; 16K for each buffer.

The default size of each buffer is 4 pages (two 16K buffers are allocated). The minimum size is 1 page. The maximum size of the buffers is limited by the size of the monitor heap, because the buffers are allocated from that heap. For performance reasons, highly active event monitors should have larger buffers than relatively inactive event monitors.

Controlling whether event monitor output is blocked or non-blocked
Some event monitors1 let you control how to proceed when event monitor output buffers are full. For blocked event monitors, each agent that generates an event will wait for the event buffers to be written to table if they are full. This can degrade database performance, as the suspended agent and any dependent agents cannot run until the buffers are clear. Use the BLOCKED clause to ensure no losses of event data: 
CREATE EVENT MONITOR myevmon FOR BUFFERPOOLS, TABLESPACES
                     WRITE TO TABLE BUFFERSIZE 8 BLOCKED
If database performance is of greater importance than collecting every single event record, use non-blocked event monitors. In this case, each agent that generates an event will not wait for the event buffers to be written to table if they are full. As a result, non-blocked event monitors are subject to data loss on highly active systems. Use the NONBLOCKED clause to minimize the additional processing time caused by event monitoring: 
CREATE EVENT MONITOR myevmon FOR BUFFERPOOLS, TABLESPACES
                     WRITE TO TABLE BUFFERSIZE 8 NONBLOCKED
Note: See Target tables, control tables, and event monitor table management and Write-to-table and file event monitor buffering for additional information about how information about discarded events is written to the control table for the event monitor.
Controlling what monitor elements for which data is collected
Which monitor elements to collect data for. If you are interested in only a few monitor elements, you can specify which ones you want to collect for some event monitors1 by specifying the element name in the CREATE EVENT MONITOR statement:
CREATE EVENT MONITOR myevmon FOR DATABASE, BUFFERPOOLS, TABLESPACES
              WRITE TO TABLE DB, DBMEMUSE,
              BUFFERPOOL (EXCLUDES(db_path, files_closed)),
              TABLESPACE (INCLUDES
                (tablespace_name, direct_reads, direct_writes))
              BUFFERSIZE 8 NONBLOCKED

All the monitor elements for the DB and DBMEMUSE logical data groups are captured (this is the default behavior). For BUFFERPOOL, all monitor elements except db_path and files_closed are captured. And finally, for TABLESPACE, tablespace_name, direct_reads and direct_writes are the only monitor elements captured.

Setting a threshold for deactivating an event monitor based on table space used
All event monitors provide the option to specify how full the table space can get before the event monitor automatically deactivates: 
CREATE EVENT MONITOR myevmon FOR BUFFERPOOLS, TABLESPACES
                     PCTDEACTIVATE 90

When the table space reaches 90% capacity, the myevmon event monitor automatically shuts off. The PCTDEACTIVATE clause can only be used for DMS table spaces. If the target table space has auto-resize enabled, set the PCTDEACTIVATE clause to 100.

What to do next

By default, event monitors that were introduced in Version 9.7 or later are created as AUTOSTART event monitors. They are activated automatically when the database is next activated, and on subsequent database activations thereafter. If you want to activate the event monitor immediately, before the next database activation, use the SET EVENT MONITOR STATE statement to manually start the event monitor. In addition for each of the locking, unit of work and package cache event monitors, you must also enable data collection.
1 Event monitors for BUFFERPOOLS, CONNECTIONS, DATABASE, DEADLOCKS, STATEMENTS, TABLES, and TABLESPACES support this option.