WCC 12.0 database schema upgrade fails for the Reporting schema when it is in a separate database

Products

• Autosys Workload Automation

Issue/Introduction

When the WCC DB and the REPORTING DB are in separate Oracle or MSSQL databases, the Create/Upgrade Schema option of the R12 installer cannot be used to upgrade the REPORTING DB schema. The schema upgrade fails due to the following error...

2022-02-17 22:28:51,015 [main] DEBUG com.ca.autosys.db.connection.DBOperations - Running query: SELECT VERSION FROM CFG_HA_GROUP
2022-02-17 22:28:51,021 [main] ERROR com.ca.autosys.db.main.AutoSysDBConnection - SQLException:java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

2022-02-17 22:28:51,021 [main] WARN  com.ca.autosys.db.install.CreateOracleDatabaseService - Autosys installation database not created correctly, kindly check logsjava.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

2022-02-17 22:28:51,021 [main] ERROR com.ca.wla.ae.installer.mm.action.wcc.CreateWCCDatabaseAction - Cannot create database , please check logs for error.
2022-02-17 22:28:51,021 [main] ERROR com.ca.wla.ae.installer.logger.LogHelper - FatalException : E_AE_Create_Database_Error [CAUAJM_E_116408] An error occurred while creating or upgrading the Web UI database. For more information, refer to the log file.

Environment

Release : 11.4

Component : WORKLOAD CONTROL CENTER

Cause

The schema update attempts to select from the CFG_HA_GROUP table, which does not exist in the REPORTING DB if it is in its own database. 

Resolution

Step-1  Execute the below create and insert SQL script in the reporting database schema. (The below SQL Scripts are compatible with MSSQL)

  CREATE TABLE [dbo].[CFG_HA_GROUP](
   [ID] [int] NOT NULL,
   [VERSION] [nvarchar](255) NULL,
  PRIMARY KEY CLUSTERED 
  (
   [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
  ) ON [PRIMARY]

  INSERT INTO [dbo].[CFG_HA_GROUP] ([ID],[VERSION]) VALUES (1,'11.4.7');

 Step-2 Run the upgrade process for the Reporting DB using the installer and should be successful now.

 Step-3 Execute the below clean up SQL script.
  
  DROP TABLE  [dbo].[CFG_HA_GROUP];