ALTER EVENT MONITOR statement

The ALTER EVENT MONITOR statement alters the definition of an event monitor that has a target for the event monitor data of TABLE.

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

The privileges held by the authorization ID of the statement must include one of the following authorities:
  • DBADM authority
  • SQLADM authority

Syntax

Read syntax diagramSkip visual syntax diagramALTER EVENT MONITORevent-monitor-name ADD LOGICAL GROUP1evm-group(target-table-options)
target-table-options
Read syntax diagramSkip visual syntax diagram23TABLEtable-nameINtablespace-namePCTDEACTIVATEinteger
Notes:
  • 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
Buffer pools
  • 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