WCC : How to move from Derby to MS-SQL
Release : 11.3.6
Component : CA Workload Automation AE (AutoSys)
Workload Control Center (WCC) r11.3.5/r11.3.6/r11.4 on Windows platform.
This document explains how you can move from Derby to MSSQL keeping all you WCC data and configurations.
We are using Microsoft SQL Server Management Studio.
Create database WCC_DB
Create database WCC_REPORT
Note: Please try creating a table manually and delete it to validate if the user have access to create “CONNECT, CREATE TABLE, CREATE SEQUENCE”
The above mentioned parameter is just example to compare. Not for Sql Server DB.
cd %CA_WCC_INSTALL_LOCATION%\bin
wcc_config.bat -u ejmcommander -p ejmcommander –x config.txt
wcc_monitor.bat –u ejmcommander –p ejmcommander –x monviews.txt
This step is ONLY required is you have ECLI commands defined. There is no WCC utility available to export/import these commands. As such we need to extract them from derby database itself and import them afterwards.
If you do NOT have ECLI, you can proceed with step 5.
We will need to access the derby database itself. This can been done as follows.
Or run ‘java –version’. This command must work before continuing.
java –jar derbyrun.jar ij
this creates an ij prompt where you can enter sql commands
connect 'jdbc:derby://localhost:1527/WCC_MAIN' user 'wcc' password 'wcc';
show tables;
(this is done to check if you see all WCC tables)
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,'ECLI_SAVED_COMMAND','c:\ecli_exported.txt',null,null,null);
(this is a derby procedure that exports all data from table ECLI_SAVED_COMMAND to an output file)
exit;
--dbhost <hostname> --dbport 1433 --dbname WCC_DB --dbapp WCC
--dbhost <hostname> --dbport 1433 --dbname WCC_REPORT --dbapp REPORTING
cd %CA_WCC_INSTALL_LOCATION%\bin
wcc_config.bat -u ejmcommander -p ejmcommander –i config.txt
wcc_monitor.bat –u ejmcommander –p ejmcommander –i monviews.txt
If you do NOT have ECLI commands, proceed with step 9.
Example:
"CheckAutoUp","NULL","chk_auto_up -r 111","GLOBAL",,"AutoSys","Production"
CheckAutoUp,NULL,chk_auto_up -r 111,GLOBAL,,AutoSys,Production
Select under ‘Destination’ the table ‘[dbo].[ECLI_SAVED_COMMAND]
Click Next
net stop CA-wcc-services
net stop CA-wcc-db
net start CA-wcc
You have completed migrating WCC from Derby to SQL.