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
运行这些语句后，每当在数据库分区 2 上激活数据库 PAYROLL 时，事件监视器 ABC 都会自动激活。直到发出 DB2 SET EVENT MONITOR ABC STATE 0 语句或停止分区 2，才不会进行此自动激活。
如果添加数据库分区，那么现有全局表或 UE 表事件监视器不会自动开始收集新创建分区的数据。要收集并记录关于新分区的数据，您必须执行下列其中一个步骤：
  • 对于全局事件监视器（即，DEADLOCKS 事件监视器），请重新启动这些事件监视器。
  • 对于表或 UE 表事件监视器，请删除、重新创建和重新启动这些事件监视器。

Db2 pureScale 环境

在 Db2 pureScale 环境中，实际上有一个数据分区及两个或更多处理数据的成员。因此，如果创建事件监视器，那么事件监视器进程会在所有成员上运行，不管它们写至文件、管道、表还是 UE 表。

事件数据是针对每个成员报告的。因此，与成员相关联的监视元素或指标（例如，total_cpu_time 监视元素）报告特定于该成员的数据。但是，不管哪个成员报告，与该数据本身相关的监视元素（例如，tablespace_total_pages 监视元素）都反映相同的值。

示例

示例 1：在分区数据库环境中创建“写至文件”事件监视器
以下示例说明如何创建事件监视器，该事件监视器运行并收集分区 3 上与缓冲池相关的事件的数据，然后将其输出写至文件：
CREATE EVENT MONITOR bpmon FOR BUFFERPOOLS
                     WRITE TO FILE '/tmp/dlevents'
                     ON DBPARTITION 3
示例 2：在分区数据库环境中创建表事件监视器
以下示例说明如何创建表事件监视器，该表事件监视器运行并收集与活动相关的事件的数据，然后将其输出写至表：
CREATE EVENT MONITOR myacts FOR ACTIVITIES
                     WRITE TO TABLE
在此示例中，因为未对事件监视器指定逻辑数据组，所以系统为与此类型的事件监视器相关联的所有逻辑数据组创建表。如果每个分区上存在缺省表空间，那么系统会在缺省表空间中的每个分区上创建其中每个表。在每个数据库分区上的表中收集的数据与该分区上发生的事件相关。
要查看所选分区中的事件监视数据，请发出查询这些分区的 SELECT 语句：
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.