New CP or JCP doesn’t start: U00045142 Cannot create table 'MQ1CPnnn'

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Error Message :
U00045142 Cannot create table 'MQ1CP006'.

When a new Communication Process (CP) or Java Communication Process (JCP) is started the first time and its message queue doesn’t exists, it’s created automatically. However this is not working entirely as it should.

Note:  This article is for Automation Engine (AE) systems with Oracle database only.

Example:

In this case or example CP006 was started in a default DB scheme (only CP tables up to CP005 exits).
The CP, or JCP in this case, starts, creates the table, but not the sequence, and then it terminates. This is what went wrong:

1. The table is created, but not entirely correct.  The tablespace “USERS” is used instead of “UC4_DATA”, “ROW MOVEMENT” is not enabled and there are other differences.
   
   

   <Please see attached file for image>

   
    
2. No sequence in created, in this case SQ_MQ1CP006 and SQ_MQ2CP006 should have been created, but they weren't:
   
   

   <Please see attached file for image>

   
    
3. An error messages (see below) is issued and the JCP shuts down.

U00003590 UCUDB - DB error: '42000', 'ORA-00933: SQL-Befehl wurde nicht korrekt beendet', '933', 'java.sql.SQLSyntaxErrorException'
SQL Statement which caused this DB error:
create table MQ1CP006 as select * from MQ1CP001 where 0=1;ALTER TABLE MQ1CP006 ADD CONSTRAINT PK_MQ1CP006 PRIMARY KEY (MQCP_PK);CREATE INDEX NK_MQ1CP006_BAcv ON MQ1CP006(MQCP_BAcv) TABLESPACE UC4_INDEX;CREATE INDEX NK_MQ1CP006_Prio ON MQ1CP006(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status) TABLESPACE UC4_INDEX;CREATE INDEX NK_MQ1CP006_SchTime ON MQ1CP006(MQCP_SchedTime) TABLESPACE UC4_INDEX;
U00045142 Cannot create table 'MQ1CP006'.
U00003432 Termination of Server 'WO121#CP006' initiated.

 
 

Environment

OS Version: N/A

Cause

Cause type:
Defect
Root Cause: Faulty and incomplete SQL statements to create the tables and sequences.

Resolution

Update to a fix version listed below or a newer version if available.

Fix Status: Released

Fix Version(s):
Component(s): AE Server and Initialdata

Automation Engine 12.1.1 - Available

Additional Information

Workaround :
Create MQ1CPnnn, MQ2CPnnn tables and SQ_MQ1CPnnn, SQ_MQ2CPnnn sequences manually.
Just use the MQxCP005 and SQ_MQxCP005 tables as an example.