Table space and other prerequisites for event monitoring

IBM® Db2® Data Management Console allows you to specify the table space in the monitored database in which event monitoring-related database objects are created.

If you do not specify a table space, by default IBM Db2 Data Management Console uses "TS4MONITOR" as the table space name for event monitoring-related database objects.

New table space

If you define a new table space, IBM Db2 Data Management Console creates the following database objects:
  • Buffer pool CONSOLEPOOL
    The console checks whether CONSOLEPOOL exists on the monitored database by using the following statement:
    SELECT COUNT(*) FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL'
    If it does not exist, the console creates the buffer pool by using the following statement:
    CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE
              32768
  • Database partition group CONSOLEGROUP
    The console checks whether the database partition group CONSOLEGROUP exists on the monitored database by using the following statement:
    SELECT COUNT(*) FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP'
    If it does not exist, the console creates the database partition group by using the following statement:
    CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS
  • Temporary table space
    The console checks whether a 32 KB temporary table space exists on the monitored database by using the following statement:
    select count(1) from syscat.tablespaces where datatype = 'T' and pagesize = 32768
    If it does not exist, the console creates the 32 KB temporary table space by using the following statement:
    CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL
  • The table space specified by the user. The console checks whether the table space that is specified by user exists on the monitored database by using the following statement:
    SELECT COUNT(*) FROM SYSCAT.TABLESPACES WHERE TBSPACE = '?'
    If it does not exist, the console creates the tables space by using the following statement:
    CREATE TABLESPACE '?' IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
              AUTORESIZE NO INITIALSIZE 2G BUFFERPOOL CONSOLEPOOL DROPPED TABLE RECOVERY OFF

    If the table space exists on the monitored database, the console checks whether the event monitor table space prerequisitesare met. If the prerequisites are met, then the table space is set as console's event monitor table space.

Existing table space

If you select an existing table space to enable event monitor functions, the console filters the list of table spaces that meet the event monitor table space prerequisites.

Table space prerequisites
  • Privilege to create, insert, delete, drop tables on the table space. The console creates a test table in the table space, performs insert, delete, and drop table actions to validate the privilege.
  • Privilege to create, enable, disable, and drop an event monitor on the table space. The console creates a test activity event monitor on the table space, performs enable, disable, and drop event monitor actions to validate the privilege.
  • The table space spans all members. This requirement ensures that all events that run on the databases can be collected. For example, if the results of the following two SQL statements are equal, then this prerequisite is met.
    select count(id) from sysibmadm.db2_member
    select count(member) from table(mon_get_tablespace(?, -2))
  • The size of the table space is fixed. This requirement protects the database system from running heavy workloads to avoid the risk when the table space size continues to grow without limitation. With a fixed size, the event monitor is set to be disabled when its usage reaches the limit. The console checks the table space size by using the following statement:
    select tbsp_type,tbsp_auto_resize_enabled,tbsp_max_size from table(mon_get_tablespace(?,
              -1)) where tbsp_name = ? fetch first 1 row only
    If the tbsp_type is DMS, its tbsp_auto_resize_enabled must be 0; Otherwise, the table space's tbsp_max_sizemust be set with a value greater than -1.