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.

1. Create a user called ‘wcc’
   1. Select ‘SQL Server Authentication’ + fill in password (example: wcc)
   2. Unflag selection ‘Enforce password policy’

2. Create the new WCC databases WCC_DB and WCC_REPORT

Create database WCC_DB

1. Use user ‘wcc’ as ‘Owner’
2. Choose 500MB as initial size for WCC_DB
3. Choose 10% as Autogrowth and Unlimited for Maxsize
4. Choose 50MB as initial size for WCC_DB_log
5. Choose 10% as Autogrotwth and Unlimited for Maxsize

Create database WCC_REPORT

1. Use user ‘wcc’ as ‘Owner’
2. Choose 200MB as initial size for WCC_REPORT
3. Choose 10% as Autogrowth and Unlimited for Maxsize
4. Choose 20MB as initial size for WCC_REPORT_log
5. Choose 10% as Autogrowth and Unlimited for Maxsize

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.

3. Exporting data from your Derby database

1. Exporting all configurations settings

cd %CA_WCC_INSTALL_LOCATION%\bin

wcc_config.bat -u ejmcommander -p ejmcommander –x config.txt

1. Exporting all monitoring views/alerts

wcc_monitor.bat –u ejmcommander –p ejmcommander –x monviews.txt

4. Exporting ECLI (Enterprise Command Line) commands.

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.

1. Check if %CA_WCC_INSTALL_LOCATION%\jre\bin is part of PATH variable

Or run ‘java –version’. This command must work before continuing.

1. cd %CA_WCC_INSTALL_LOCATION%\derby\lib

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;

5. Updating WCC to point to SQL database

1. wcc_config -u ejmcommander -p ejmcommander --dbplatform MSSQL --dbuser wcc --dbpassword wcc

--dbhost <hostname> --dbport 1433 --dbname WCC_DB --dbapp WCC

1. wcc_config -u ejmcommander -p ejmcommander --dbplatform MSSQL --dbuser wcc --dbpassword wcc

--dbhost <hostname> --dbport 1433 --dbname WCC_REPORT --dbapp REPORTING

6. Stop/start WCC services. During WCC startup, it will create the tables in database WCC_DB and WCC_REPORT.

1. net stop CA-wcc-services
2. net start CA-wcc

7. Importing the exported derby data

1. import all configurations settings

cd %CA_WCC_INSTALL_LOCATION%\bin

wcc_config.bat -u ejmcommander -p ejmcommander –i config.txt

1. import all monitoring views/alerts

wcc_monitor.bat –u ejmcommander –p ejmcommander –i monviews.txt

8. Importing the exported ECLI commands.

If you do NOT have ECLI commands, proceed with step 9.

4. Open file 'c:\ecli_exported.txt' that was created in step 4. You will see that each field is surrounded by double quotes “. Please remove this double quotes.

        Example:

        "CheckAutoUp","NULL","chk_auto_up -r 111","GLOBAL",,"AutoSys","Production"

            CheckAutoUp,NULL,chk_auto_up -r 111,GLOBAL,,AutoSys,Production

1. In SQL Server Management Studio

• Right click on WCC_DB and select ‘Import Data’ under ‘Tasks’.
• Choose ‘Flat File Source’ and fill in the file 'c:\ecli_exported.txt'. Unflag ‘Column names in the first data row’.

• Click Next
• Click Next
• Click Next

Select under ‘Destination’ the table ‘[dbo].[ECLI_SAVED_COMMAND]

Click Next

• Click Next
• Select ‘Run Immediately’.

9. Stop WCC services

               net stop CA-wcc-services

               net stop CA-wcc-db

10. Adapt ‘Startup Type’ for ‘CA-wcc-db’ to ‘Manual’.

11. Rename directory %CA_WCC_INSTALL_LOCATION%\derby to %CA_WCC_INSTALL_LOCATION%\derby_nolonger_used

12. Start WCC services

net start CA-wcc

You have completed migrating WCC from Derby to SQL.