Cleaning up monitor objects for deleted connections
You can clean up database objects created by the console for deleted database connections.
About this task
When you add a database connection with the data collection credential option turned on, the console collects the monitor data. In addition, some database objects are also created on the monitored database. For example, if activity event monitor function is enabled on the monitored database, several database objects such as activity event monitor, event monitor table, ATS, TS4MONITOR table space, etc. are also created. But when you delete the database connection, these objects are automatically removed. If they are not removed, you can use a script to remove or clean up these database objects.
Procedure
To remove or clean up database objects for deleted connections:
- Copy the monObjCleanup.sql script to where repository database is
located.
SET SERVEROUTPUT ON@ CALL DBMS_OUTPUT.PUT_LINE('Initializing...')@ -- Create ARRAY type CREATE OR REPLACE TYPE DMC_STRINGARRAY AS VARCHAR(100) ARRAY[]@ -- Drop Watch Dog tasks BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for Watch Dog tasks...'); SET n = (SELECT COUNT(*) FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SYSTOOLS' AND TABNAME = 'ADMIN_TASK_LIST'); IF (n = 1) THEN SET STMT = 'BEGIN DECLARE m, i INTEGER;' || ' DECLARE ARR2 DMC_STRINGARRAY;' || ' DECLARE STMT2 VARCHAR(1000);' || ' SET ARR2 = ARRAY[SELECT NAME FROM SYSTOOLS.ADMIN_TASK_LIST WHERE NAME LIKE ''Rtmon Evmon%''];' || ' SET m = CARDINALITY(ARR2);' || ' IF (m > 0) THEN CALL DBMS_OUTPUT.PUT_LINE(''Found '' || m || '' Watch Dog task(s). Cleaning them up...''); ELSE CALL DBMS_OUTPUT.PUT_LINE(''No Watch Dog tasks found''); END IF;' || ' SET i = 1;' || ' WHILE (i <= m) DO' || ' SET STMT2 = ''CALL SYSPROC.ADMIN_TASK_REMOVE('''''' || ARR2[i] || '''''', NULL)'';' || ' EXECUTE IMMEDIATE STMT2;' || ' SET i = i + 1;' || ' END WHILE;' || ' END'; -- CALL DBMS_OUTPUT.PUT_LINE('STMT = ' || STMT); EXECUTE IMMEDIATE STMT; COMMIT; END IF; END@ -- Drop Watch Dog procedures BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for Watch Dog procedures...'); SET ARR1 = ARRAY[select routinename from SYSCAT.ROUTINES where routineschema = 'IBM_RTMON']; SET n = CARDINALITY(ARR1); IF (n > 0) THEN CALL DBMS_OUTPUT.PUT_LINE('Found ' || n || ' Watch Dog procedure(s). Cleaning them up...'); ELSE CALL DBMS_OUTPUT.PUT_LINE('No Watch Dog procedure(s) found'); END IF; SET i = 1; WHILE (i <= n) DO SET STMT = 'drop procedure IBM_RTMON.' || ARR1[i]; EXECUTE IMMEDIATE STMT; SET i = i + 1; END WHILE; COMMIT; END@ -- Drop RTMON_EVMON_STATS BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for RTMON_EVMON_STATS...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.eventmonitors WHERE evmonname = 'RTMON_EVMON_STATS'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found RTMON_EVMON_STATS. Cleaning it up...'); SET STMT = 'set event monitor RTMON_EVMON_STATS state 0'; EXECUTE IMMEDIATE STMT; SET STMT = 'DROP EVENT MONITOR RTMON_EVMON_STATS'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('RTMON_EVMON_STATS not found'); END IF; COMMIT; END@ -- Drop RTMON_EVMON_UTILITY BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for RTMON_EVMON_UTILITY...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.eventmonitors WHERE evmonname = 'RTMON_EVMON_UTILITY'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found RTMON_EVMON_UTILITY. Cleaning it up...'); SET STMT = 'set event monitor RTMON_EVMON_UTILITY state 0'; EXECUTE IMMEDIATE STMT; SET STMT = 'DROP EVENT MONITOR RTMON_EVMON_UTILITY'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('RTMON_EVMON_UTILITY not found'); END IF; COMMIT; END@ -- Drop RTMON_EVMON_LOCKING BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for RTMON_EVMON_LOCKING...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.eventmonitors WHERE evmonname = 'RTMON_EVMON_LOCKING'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found RTMON_EVMON_LOCKING. Cleaning it up...'); SET STMT = 'set event monitor RTMON_EVMON_LOCKING state 0'; EXECUTE IMMEDIATE STMT; SET STMT = 'DROP EVENT MONITOR RTMON_EVMON_LOCKING'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('RTMON_EVMON_LOCKING not found'); END IF; COMMIT; END@ -- Drop RTMON_EVMON_ACTIVITIES_XXXXXX BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for RTMON_EVMON_ACTIVITIES...'); SET ARR1 = ARRAY[SELECT EVMONNAME FROM SYSCAT.EVENTMONITORS WHERE EVMONNAME LIKE 'RTMON_EVMON_ACTIVITIES_%']; SET n = CARDINALITY(ARR1); IF (n > 0) THEN CALL DBMS_OUTPUT.PUT_LINE('Found ' || n || ' RTMON_EVMON_ACTIVITIES...(s). Cleaning them up...'); ELSE CALL DBMS_OUTPUT.PUT_LINE('RTMON_EVMON_ACTIVITIES not found'); END IF; SET i = 1; WHILE (i <= n) DO SET STMT = 'set event monitor ' || ARR1[i] || ' state 0'; EXECUTE IMMEDIATE STMT; CALL dbms_alert.sleep(10); SET STMT = 'DROP EVENT MONITOR ' || ARR1[i]; EXECUTE IMMEDIATE STMT; SET i = i + 1; END WHILE; COMMIT; END@ -- Drop table IBM_RTMON.XXX BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for IBM_RTMON tables...'); SET ARR1 = ARRAY[SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'IBM_RTMON']; SET n = CARDINALITY(ARR1); IF (n >0) THEN CALL DBMS_OUTPUT.PUT_LINE('Found ' || n || ' IBM_RTMON table(s). Cleaning them up...'); ELSE CALL DBMS_OUTPUT.PUT_LINE('IBM_RTMON tables not found'); END IF; SET i = 1; WHILE (i <= n) DO SET STMT = 'DROP TABLE IBM_RTMON.' || ARR1[i]; EXECUTE IMMEDIATE STMT; SET i = i + 1; END WHILE; COMMIT; END@ -- SYSTOOLSPACE -- Drop CONSOLE_WORKLOAD BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for workload CONSOLE_WORKLOAD...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found workload CONSOLE_WORKLOAD. Cleaning it up...'); SET STMT = 'ALTER WORKLOAD CONSOLE_WORKLOAD DISABLE'; EXECUTE IMMEDIATE STMT; COMMIT; SET STMT = 'DROP WORKLOAD CONSOLE_WORKLOAD'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('Workload CONSOLE_WORKLOAD not found'); END IF; COMMIT; END@ -- Drop DSM_WORKLOAD BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for workload DSM_WORKLOAD...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found workload DSM_WORKLOAD. Cleaning it up...'); SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE'; EXECUTE IMMEDIATE STMT; COMMIT; SET STMT = 'DROP WORKLOAD DSM_WORKLOAD'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('Workload DSM_WORKLOAD not found'); END IF; COMMIT; END@ -- Drop TEMPSPACE2 BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for temp tablespace TEMPSPACE2...'); SELECT COUNT(*) INTO RESULT FROM syscat.tablespaces WHERE TBSPACE = 'TEMPSPACE2' AND OWNERTYPE = 'U' AND DATATYPE = 'T' AND PAGESIZE = 32768; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found temp tablespace TEMPSPACE2. Cleaning it up...'); SET STMT = 'DROP TABLESPACE TEMPSPACE2'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('Temp tablespace TEMPSPACE2 not found'); END IF; COMMIT; END@ -- Drop TS4MONITOR BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for tablespace TS4MONITOR...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found tablespace TS4MONITOR. Cleaning it up...'); SET STMT = 'DROP TABLESPACE TS4MONITOR'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('Tablespace TS4MONITOR not found'); END IF; COMMIT; END@ -- Drop CONSOLEGROUP BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for DB partition group CONSOLEGROUP...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found DB partition group CONSOLEGROUP. Cleaning it up...'); SET STMT = 'DROP DATABASE PARTITION GROUP CONSOLEGROUP'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('DB partition group CONSOLEGROUP not found'); END IF; COMMIT; END@ -- Drop CONSOLEPOOL BEGIN DECLARE RESULT INTEGER; DECLARE STMT VARCHAR(1000); DECLARE ARR1 DMC_STRINGARRAY; DECLARE n, i INTEGER; CALL DBMS_OUTPUT.NEW_LINE(); CALL DBMS_OUTPUT.PUT_LINE('Checking for bufferpool CONSOLEPOOL...'); SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL'; IF (RESULT = 1) THEN CALL DBMS_OUTPUT.PUT_LINE('Found bufferpool CONSOLEPOOL. Cleaning it up...'); SET STMT = 'DROP BUFFERPOOL CONSOLEPOOL'; EXECUTE IMMEDIATE STMT; ELSE CALL DBMS_OUTPUT.PUT_LINE('Bufferpool CONSOLEPOOL not found'); END IF; COMMIT; END@ -- Drop ARRAY type DROP TYPE DMC_STRINGARRAY@ CALL DBMS_OUTPUT.PUT_LINE('Done.')@ SET SERVEROUTPUT OFF@
- Back up the deleted monitored database.
- From the Db2 command line, connect to the deleted monitored database, and run the
script:
# db2 -td@ -vf monObjCleanup.sql
Note: Make sure to run the script against the correct database.