Setting up statistics event monitoring opt-in

The statistics event monitor opt-in feature provides a way to automatically enable statics event monitoring on a database connection.

Procedure

This setting applies to new or imported connections that are added in the Connection profile page, and also for connections that are edited post enabling the statistics event monitoring opt-in feature. To manually enable statistics event monitoring for an existing connection, go to Settings > Event monitor profile.
Note: Statistics event monitor must be enabled on a connected database for the ‘Responsiveness’ widget in the Monitor > Summary page to display data in the widget.

Enabling the Statistics event monitor opt-in feature incurs additional costs for creating or altering the workload management objects in the monitored database, collect, and store data for historical analysis.

To set up statistics event monitor opt-in

  1. Log into web console.
  2. Click Administration and go to Settings > Stats event monitor opt-in.
  3. Click the Set statistics event monitor opt-in toggle button and set it to On/Off.
    Note:

    The ‘Responsiveness' widget is a tool that gives you an insight on changes of query response time characteristics over time. This widget uses the aggregated activity data collected by the statistics event monitor and helps you understand how quickly the system is processing the statements. You can also view your workload composition and capture activities for diagnostic reasons.

    When the Statistics event monitor opt-in feature is turned on, the Responsiveness widget displays data only if the COLLECT AGGREGATE ACTIVITY DATA clause is specified for at least one of the Db2 workloads. Otherwise, no data is displayed in the Responsiveness widget. In addition, the console makes the following changes to the monitored database.
    • Creates the following objects:
      • A buffer pool named CONSOLEPOOL (Not created if it already exists)
        CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE 32768
      • A database partition group named CONSOLEGROUP (Not created if it already exists)
        CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS
      • A 32KB table space named TS4MONITOR (Not created if it already exists)
        CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL
      • Table space named SYSTOOLSPACE (Not created if it already exists). Console uses a 32KB temporary tablespace to move event monitor records from monitored database to repository database.
        CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4
      • A temporary table space TEMPSPACE2. (If there is a 32KB temporary table space on the database, this object will not be created).
        CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL
      • A statistics event monitor named RTMON_EVMON_STATS. Console will use this event monitor to monitor query run time distribution in the database system.
      • A workload named CONSOLE_WORKLOAD. (Not created if it already exists)
        CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')
      • A stored procedure IBM_RTMON.RTMON_EVMON_STATISTICS_WATCHDOG. The stored procedure will be invoked by the ATS task when event monitor hits exceptional conditions, and it will disable the event monitor to protect the database system.
      • A Db2 ATS task named "Rtmon Evmon Statistics Watchdog". The ATS task will be triggered when event monitor hits exceptional conditions, and it will invoke the watchdog procedure to disable the event monitor to protect the database system.
    • Sets the "DB2_ATS_ENABLE" variable to "YES" to enable ATS functionality in the DB system.
    The statistics event monitor opt-in value is recorded in dswebserver_override.property file with "evmonOptin". If the event monitor opt-in value is set to true, the statistics event monitor will be created automatically by the console in the following scenarios:
    • Add a new connection profile with data collection enabled.
    • Import a new connection profile with data collection enabled, or import an existing connection with property changes and with data collection enabled.
    • Edit an existing connection with data collection enabled, to change some properties in the connection profile page.
    • Edit an existing connection with data collection enabled, to change some properties through API.
    • For existing connections, when a user specifies a tablespace for an event monitor. This action triggers a connection property "otsEventTablespace" change, and RTMON_EVMON_STATS will be created automatically.

    If the Statistics event monitor opt-in feature is turned off, the Responsiveness widget displays no data. The console does not make any change to the monitored database.

  4. Click Save.

    For newly added connections, user must manually enable and configure the statistics event monitor in console. The 'Responsiveness' widget displays data only when status event monitor is enabled for the monitored database.