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

book

Article ID: 87196

calendar_today

Updated On:

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>

    0EMb0000000IYgx.png

     
  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>

    0EMb0000000IYh2.png

     
  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.
 
 

Cause

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

Environment

OS Version: N/A

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.

Attachments

1558693571551000087196_sktwi1f5rjvs16m9l.png get_app
1558693569802000087196_sktwi1f5rjvs16m9k.png get_app
1558536071783create_MQxCP006_ORA_DB_V12.1.sql get_app