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
- Buffer pool
CONSOLEPOOL
The console checks whetherCONSOLEPOOL
exists on the monitored database by using the following statement:
If it does not exist, the console creates the buffer pool by using the following statement:SELECT COUNT(*) FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL'
CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE 32768
- Database partition group
CONSOLEGROUP
The console checks whether the database partition groupCONSOLEGROUP
exists on the monitored database by using the following statement:
If it does not exist, the console creates the database partition group by using the following statement:SELECT COUNT(*) FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP'
CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS
- Temporary table spaceThe console checks whether a 32 KB temporary table space exists on the monitored database by using the following statement:
If it does not exist, the console creates the 32 KB temporary table space by using the following statement:select count(1) from syscat.tablespaces where datatype = 'T' and pagesize = 32768
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:
If it does not exist, the console creates the tables space by using the following statement:SELECT COUNT(*) FROM SYSCAT.TABLESPACES WHERE TBSPACE = '?'
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.
- 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:
If theselect tbsp_type,tbsp_auto_resize_enabled,tbsp_max_size from table(mon_get_tablespace(?, -1)) where tbsp_name = ? fetch first 1 row only
tbsp_type
isDMS
, itstbsp_auto_resize_enabled
must be0
; Otherwise, the table space'stbsp_max_size
must be set with a value greater than-1
.