ALTER EVENT MONITOR statement

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

Syntax

>>-ALTER EVENT MONITOR--event-monitor-name---------------------->

   .----------------------------------------------------------------------.   
   V                   (1)                                                |   
>----ADD LOGICAL GROUP------evm-group--+--------------------------------+-+-><
                                       '-(--| target-table-options |--)-'     

target-table-options

   .--------------------------------------.   
   V  (2)   (3)                           |   
|--------------+-TABLE--table-name------+-+---------------------|
               +-IN--tablespace-name----+     
               '-PCTDEACTIVATE--integer-'     

1. A logical group can be added only to TABLE event monitors (not UNFORMATTED EVENT TABLE event monitors).
2. Each clause can be specified only once.
3. Clauses can be separated with a space or a comma.

Description

event-monitor-name

The event-monitor-name must identify an event monitor that exists at the current server and has a target for the event monitor data of TABLE.

ADD LOGICAL GROUP

Adds a logical group to the event monitor that has a target for the data of TABLE.

evm-group

Identifies the logical data group for which a target table is being added. The value depends upon the type of event monitor, as shown in the following table:

Table 1. Values for evm-group based on the type of event monitor

Type of Event Monitor	evm-group Value
Database	DB CONTROL1 DBMEMUSE
Tables	TABLE CONTROL1
Deadlocks	CONNHEADER DEADLOCK DLCONN CONTROL1
Deadlocks with details	CONNHEADER DEADLOCK DLCONN2 DLLOCK3 CONTROL1
Deadlocks with details history	CONNHEADER DEADLOCK DLCONN2 DLLOCK3 STMTHIST CONTROL1
Deadlocks with details history values	CONNHEADER DEADLOCK DLCONN2 DLLOCK3 STMTHIST STMTVALS CONTROL1
Table spaces	TABLESPACE CONTROL1
Bufferpools	BUFFERPOOL CONTROL1
Connections	CONNHEADER CONN CONTROL1 CONNMEMUSE
Statements	CONNHEADER STMT SUBSECTION4 CONTROL1
Transactions	CONNHEADER XACT CONTROL1
Activities	ACTIVITY ACTIVITYMETRICS ACTIVITYSTMT ACTIVITYVALS CONTROL1
Statistics	QSTATS SCSTATS SCMETRICS WCSTATS WLSTATS WLMETRICS HISTOGRAMBIN CONTROL1
Threshold Violations	THRESHOLDVIOLATIONS CONTROL1
Locking5	LOCK LOCK_PARTICIPANTS LOCK_PARTICIPANT_ACTIVITIES LOCK_ACTIVITY_VALUES CONTROL1
Package Cache5	PKGCACHE PKGCACHE_METRICS CONTROL1
Unit of Work5	UOW UOW_METRICS UOW_PACKGE_LIST UOW_EXECUTABLE_LIST CONTROL1
Change History	CHANGESUMMARY EVMONSTART TXNCOMPLETION DDLSTMTEXEC DBDBMCFG REGVAR UTILSTART UTILSTOP UTILPHASE UTILLOCATION CONTROL1
1 Logical data groups dbheader (conn_time element only), start and overflow, are all written to the CONTROL group. The overflow group is written if the event monitor is non-blocked and events were discarded. 2 Corresponds to the DETAILED_DLCONN event. 3 Corresponds to the LOCK logical data groups that occur within each DETAILED_DLCONN event. 4 Created only for partitioned database environments. 5 Refers to the Formatted Event Table version of this event monitor type.

TABLE table-name

Specifies the name of the target table. The target table must be a non-partitioned table. If the name is unqualified, the table schema defaults to the value in the CURRENT SCHEMA special register. If no name is provided, the unqualified name is derived from evm-group and event-monitor-name as follows:

   substring(evm-group CONCAT '_' 
      CONCAT event-monitor-name,1,128)

IN tablespace-name

Defines the table space in which the table is to be created. If no table space name is provided, the table space is chosen using the same process as when a table is created without a table space name using the CREATE TABLE statement.

When specifying the table space name for an activities, locking, package cache, or unit of work event monitor, the table space's page size affects the INLINE LOB lengths used. Therefore, consider specifying a table space with as large a page size as possible to improve the INSERT performance of the event monitor.

PCTDEACTIVATE integer

If a table is being created in a DMS table space, PCTDEACTIVATE specifies how full the table space must be before the event monitor automatically deactivates. The specified value, which represents a percentage, can range from 0 to 100. The default value is 100 (meaning that the event monitor deactivates when the table space becomes completely full). This option is ignored for SMS table spaces. When a target table space has auto-resize enabled, it is recommended that PCTDEACTIVATE be set to 100.

Notes

• When system catalog changes take effect: Changes are written to the system catalog, but do not take effect until they are committed and the event monitor is reactivated.

Example

The event monitor ACT is missing the ACTIVITYMETRICS group. Alter the event monitor to add this group and give the table the name "ACTMETRICS".

   ALTER EVENT MONITOR ACT
      ADD LOGICAL GROUP ACTIVITYMETRICS TABLE ACTMETRICS