Event monitors for partitioned databases and databases in a DB2 pureScale environment

Generally, event monitors on partitioned database systems or in a DB2 pureScale environment work similarly to event monitors that run on nonpartitioned, single-member databases. However, there are some differences to be aware of.

Partitioned database environments

Event monitors that write to regular tables and unformatted event (UE) tables

You cannot create event monitors that write to regular tables and UE tables on a specific partition. Instead, for a partitioned database environment, an event monitor process runs on each of the partitions. More specifically, the event monitor process runs on the members for each partition that belong to the database partition groups in which the target tables exist.

Each partition where the event monitor process runs has the same set of target tables for a specific event monitor. The data in these tables is different from partition to partition because the data for a specific partition reflects only events that take place on that partition. For table event monitors, you can retrieve aggregate values from all the partitions by issuing SQL statements to collect data from event monitor tables from each partition. For UE table event monitors, you can aggregate data across partitions by using the SQL statement that you specify for the EVMON_FORMAT_UE_TO_TABLE stored procedure or by using the EVMON_FORMAT_UE_TO_XML table function.

The first column of each event monitor table is named PARTITION_KEY and is used as the partitioning key for the table. The value of this column is chosen so that each event monitor process inserts data into the database partition on which the process is running. That is, insert operations are performed locally on the database partition where the event monitor process is running. On any database partition, the PARTITION_KEY field contains the same value. As a result, if you drop a data partition and data redistribution is performed, all data on the dropped database partition goes to one other database partition instead of being evenly distributed. Therefore, before dropping a database partition, consider deleting all table rows on that database partition.

In addition, in partitioned database environments, a column named PARTITION_NUMBER, or MEMBER is defined for each table. This column contains the number of the partition or member on which the data was inserted.

Events are written to the event monitor target tables on those partitions where the table space for the target tables exists. If the table space for the event monitor target tables does not exist on any partition where the event monitor runs, no data is collected on those partitions, and no error is returned. Moreover, no log records for these events are written where the table space does not exist. This behavior means that you can choose a subset of partitions for monitoring by creating a table space that exists only on certain partitions.

During write-to-table event monitor activation, the CONTROL table rows for FIRST_CONNECT and EVMON_START are inserted on all database partitions where the table space for target tables exists.

If a partition is not yet active when an event monitor is activated, the event monitor is activated when that partition is next activated.

Event monitors that write to files and named pipes

File and pipe event monitors, with one exception, capture only events that take place on the database partition on which they are running (the monitor partition). Such an event monitor is known as a local event monitor. The exception is the DEADLOCK event monitor; you can create it as a local or a global event monitor. When you create it as a global event monitor, deadlock information is collected on all database partitions and is reported to the specific database partition where the event monitor process runs.1

When you create a file or pipe event monitor in a partitioned database environment, you can specify the partition that you want it to run on as part of the CREATE EVENT MONITOR statement. If you omit the partition number, the event monitor runs on the database partition that was connected when you created the event monitor.

An event monitor can be activated only if the monitor partition is active. If you use the SET EVENT MONITOR statement to activate an event monitor but the monitor partition is not yet active, event monitor activation occurs when the monitor partition is next started. Furthermore, the event monitor is activated automatically until you explicitly deactivate the event monitor or the instance. For example, consider the following sequence of statements: 
DB2 CONNECT TO PAYROLL
DB2 CREATE EVENT MONITOR ABC ... ON DBPARTITIONNUM 2
DB2 SET EVENT MONITOR ABC STATE 1
After these statements are run, event monitor ABC activates automatically whenever the database PAYROLL is activated on database partition 2. This automatic activation occurs until the statement DB2 SET EVENT MONITOR ABC STATE 0 is issued or partition 2 is stopped.
If you add database partitions, the existing global, table, or UE table event monitors do not automatically start collecting data for the newly created partitions. To collect and record data about the new partitions, you must take one of the following steps:
  • For global event monitors (that is, a DEADLOCKS event monitor), restart the event monitors.
  • For table or UE table event monitors, drop, re-create, and restart the event monitors.

DB2 pureScale environments

In DB2 pureScale environments, there is effectively one data partition, with two or more members that process data. Thus, when you create an event monitor, event monitor processes run on all members, regardless of whether they write to a file, pipe, tables, or a UE table.

Event data is reported on a per-member basis. As a result, monitor elements or metrics that are associated with a member, such as the total_cpu_time monitor element, report data that is specific to that member. However, other monitor elements related to the data itself, such as the tablespace_total_pages monitor element, reflect the same values regardless of what member reports them.

Examples

Example 1: Creating a write-to-file event monitor in a partitioned database environment
The example that follows shows how to create an event monitor that runs and collects data for buffer pool-related events on partition 3, writing its output to a file:
CREATE EVENT MONITOR bpmon FOR BUFFERPOOLS
                     WRITE TO FILE '/tmp/dlevents'
                     ON DBPARTITION 3
Example 2: Creating a table event monitor in a partitioned database environment
The example that follows shows how to create a table monitor that runs and collects data for activities-related events and writes its output to a table:
CREATE EVENT MONITOR myacts FOR ACTIVITIES
                     WRITE TO TABLE
In this example, because no logical data groups are specified for the event monitor, tables are created for all logical data groups associated with this type of event monitor. Each of these tables is created on each partition in the default table space if the default table space exists on each partition. The data that is collected in the tables on each database partition pertains to events that take place on that partition.
To view event monitor data from selected partitions, issue a SELECT statement that queries those partitions:
SELECT TOTAL_CPU_TIME FROM myacts WHERE PARTITION_NUMBER = 3
1 This event monitor is deprecated. The LOCKING event monitor is the preferred event monitor for capturing lock and deadlock event information.