search cancel

Deleting unnecessary CP tables in MSSQL/Oracle

book

Article ID: 87897

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

E.g, you had 9 CPs in your system. Now you don't need so many CPs any more and want to delete CPs CP006 to CP009. The normal and correct way to do this is to delete the server objects in the GUI. This removes the CPs completely from the system. However, the supernumerary MQCP-tables created for these CPs will not be deleted and remain in the database. This is in general no problem and has no side effects, but if you want to get rid of them, you can delete them manually by SQL-command.

Cause

Cause type:
By design

Environment

Release: AUTWAB99000-10.0-Automic Workload Automation-Base Edition
Component:

Resolution

Oracle

For MS SQL, the command looks as following:
 

DECLARE @tableName varchar(50), @dsql nvarchar(95)
DECLARE cur CURSOR FOR
 
SELECT name FROM sys.Tables
WHERE name in ('MQCP006', 'MQCP007', 'MQCP008', 'MQCP009')
 
OPEN cur
FETCH NEXT FROM cur INTO @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
SET @dsql = 'DROP TABLE ' + @tableName
EXEC sp_executesql @dsql
FETCH NEXT FROM cur INTO @tableName
 
END
CLOSE cur DEALLOCATE cur
GO


In the SELECT you have to enter the tables to delete in the where-clause. In this example, these are MQCP006 to MQCP009.

Check with
 

SELECT name FROM sys.Tables WHERE name LIKE 'MQCP%'


if the tables are gone.

MSSQL

ECLARE @tableName varchar(50), @dsql nvarchar(95)
DECLARE cur CURSOR FOR 
SELECT name FROM sys.Tables
WHERE name in ('MQCP006', 'MQCP007', 'MQCP008', 'MQCP009')
OPEN cur
FETCH NEXT FROM cur
INTO @tableName
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @dsql = 'DROP TABLE ' + @tableName
EXEC sp_executesql @dsql
FETCH NEXT FROM cur INTO @tableName
END
CLOSE cur
DEALLOCATE cur
GO

 
In the third line, in the select, the MQCP-tables to be deleted have to be entered.
 
With the command below you can check which tables are available:
 

SELECT name FROM sys.Tables WHERE name LIKE 'MQCP%'