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

Ensure you have the following privileges to create event monitoring-related objects:
  • 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

  1. 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}"
  2. 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;