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 will be created.
Before you begin
- SYSCTRL or SYSADM privilege is required to create BUFFERPOOL and TABLESPACE
- WLMADM or DBADM privilege is required to CREATE / ALTER WORKLOAD
- ACCESSCTRL or SECADM privilege is required to GRANT EXECUTE on PROCEDURE
- SYSADM privilege is required to run db2set
About this task
One or more tables are created along with the creation of event monitors. These tables are used by Db2 to store the event monitor data. You must specify a table space to contain these tables.
If you do not specify a table space, by default IBM Db2 Data Management Console will use "TS4MONITOR" as the table space name for event monitoring-related database objects
Procedure
- To override the default table space name, and set the table
space name explicitly, you can use below API:
curl --write-out '%{http_code}\n' --silent -k "https://<host name>:<port>/dbapi/v4/dbprofiles/<dbprofilename>" -d '{"name":"<dbprofilename>","otsEventTablespace":"<table_space_name>"}' -X PUT -H "Authorization: Bearer ${token}"
- To get the table space setting by REST API:
curl --write-out '%{http_code}\n' --silent -k "https://<host name>:<port>/dbapi/v4/dbprofiles/<dbprofilename>" -X GET -H "Authorization: Bearer ${token}"
Note: For the default table space ("TS4MONITOR") or any user-defined table space name, you must create it manually, and make sure all the prerequisites are met. You can refer to the following DDL statement to create the table space "TS4MONITOR" and related DB objects.Without ATS
--Create the event monitor prerequisite stored procedure --#SET TERMINATOR @ CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ() SPECIFIC IBM_RTMON.EVMON_PREREQ LANGUAGE SQL BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); --Create a bufferpool with page size 32kb SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL'; IF (RESULT = 0) THEN SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE 32768'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Create a partition spannning all partitions SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP'; IF (RESULT = 0) THEN SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Create the 32kb tablespace with max size 2G specified SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR'; IF (RESULT = 0) THEN SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Create a 32kb pagesize temporary tablespace if there is not an existing one SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768; IF (RESULT = 0) THEN SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Disable the legacy console workload DSM_WORKLOAD if it exists SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y'; IF (RESULT = 1) THEN SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Define a workload to filter the monitored console executed statements. Because the COLLECT ACTIVITY DATA option is not specified, the statement history will not collect the statements of the workload. SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD'; IF (RESULT = 0) THEN SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; END@ --#SET TERMINATOR ; -- Apply the event monitor prerequisite stored procedure CALL IBM_RTMON.EVMON_PREREQ; --Capture the statements for the default two workloads on the coordinator node. If you want to collect the activity data for all nodes of a multi-partition database, change the COORDINATOR option to ALL. ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS; ALTER WORKLOAD SYSDEFAULTADMWORKLOAD COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS; --Capture the aggregate responsiveness workload information ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT AGGREGATE ACTIVITY DATA BASE; ALTER WORKLOAD SYSDEFAULTADMWORKLOAD COLLECT AGGREGATE ACTIVITY DATA BASE;
With ATS
--Create the event monitor prerequisite stored procedure --#SET TERMINATOR @ CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ() SPECIFIC IBM_RTMON.EVMON_PREREQ LANGUAGE SQL BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); --Create a bufferpool with page size 32kb SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL'; IF (RESULT = 0) THEN SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE 32768'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Create a partition spannning all partitions SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP'; IF (RESULT = 0) THEN SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Create the 32kb tablespace with max size 2G specified SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR'; IF (RESULT = 0) THEN SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Create a 32kb pagesize temporary tablespace if there is not an existing one SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768; IF (RESULT = 0) THEN SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Disable the legacy console workload DSM_WORKLOAD if it exists SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y'; IF (RESULT = 1) THEN SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Define a workload to filter the monitored console executed statements. Because the COLLECT ACTIVITY DATA option is not specified, the statement history will not collect the statements of the workload. SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD'; IF (RESULT = 0) THEN SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; --Create SYSTOOLSPACE forAdministrative Task Schedule(ATS) SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE'; IF (RESULT = 0) THEN SET STMT = 'CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4'; EXECUTE IMMEDIATE STMT; END IF; COMMIT; END@ --#SET TERMINATOR ; -- Apply the event monitor prerequisite stored procedure CALL IBM_RTMON.EVMON_PREREQ; --Capture the statements for the default two workloads on the coordinator node. If you want to collect the activity data for all nodes of a multi-partition database, change the COORDINATOR option to ALL. ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS; ALTER WORKLOAD SYSDEFAULTADMWORKLOAD COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS; --Capture the aggregate responsiveness workload information ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT AGGREGATE ACTIVITY DATA BASE; ALTER WORKLOAD SYSDEFAULTADMWORKLOAD COLLECT AGGREGATE ACTIVITY DATA BASE; --Ensure you have ADMIN_TASK_ADD and ADMIN_TASK_UPDATE privileges to execute stored procedures(ATS) GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_ADD TO USER ${userName}; GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_UPDATE TO USER ${userName}; --Enable on the database the setting related to ATS, please choose the "CLP with SSH" Run method in Options !db2set DB2_ATS_ENABLE=YES;