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.