Database Derby migration to MSSQL failed

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - System Agent (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• CA Workload Automation Agent
• CA Workload Automation AE

Issue/Introduction

WCC : How to move from Derby to MS-SQL

Environment

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.

 

Resolution

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.