JCP/CP table creation assumes default tablespace names | 'UC4_DATA' does not exist

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

On Automation Engines using an Oracle DB, when trying to start an additional CP or JCP a new table should be automatically created. However, if the tablespace names of the AE DB are other than UC4_DATA and UC4_INDEX this fails.

The following error appears in the CP log:
U00003590 UCUDB - DB error: '42000', 'ORA-00959: tablespace 'UC4_DATA' does not exist 
U00045142 Cannot create table 'MQ1CP<xxx>'. 

where <xxx> corresponds to the number of the CP to be created.

Environment

Oracle

Cause

The Automation Engine assumes the standard tablespace names UC4_DATA and UC4_INDEX. These names are the recommended ones.

Note: If you are encountering this issue due to non-standard tablespace names, the steps taken to resolve will need to be applied with any update of the initial data; for an upgrade, to enable ILM, etc. 

Resolution

If you need to use other tablespace names than the standard, you need to replace UC4_DATA and UC4_INDEX.

This is done in two places:
1.) UC_DDL.SQL, necessary for the DB load
2.) A modification in the AE DB is necessary for the automatic creation of additional CP tables.

The following DB field needs to be edited:

select DBSYN_CONTENT from UC_DBSYN where DBSYN_TYPE= 'CREATE_MQCP' and DBSYN_DB = 'ORACLE';

The result needs to have all references to UC4_DATA and UC4_INDEX updated (please note, this includes substitution variables - you may need "set define off;" to properly insert the values). 

Alternatively, you may create the additional CP table manually as describes here:

https://knowledge.broadcom.com/external/article/112281/sql-statements-to-create-additional-cp.html