Automic Workload Automation

In a recent discussion about the Java Communications Process (newly added in AE v12.1), I reported on an error I had seen when trying to start up the JCP:

20171003/152952.355 - U00003590 UCUDB - DB error: '22019', 'ORA-00911: invalid character
', '911', 'java.sql.SQLSyntaxErrorException'
20171003/152952.355 - SQL Statement which caused this DB error:
20171003/152952.355 - create table MQ1CP007 as select * from MQ1CP001 where 0=1;ALTER TABLE MQ1CP007 ADD CONSTRAINT PK_MQ1CP007 PRIMARY KEY (MQCP_PK)
;CREATE INDEX NK_MQ1CP007_BAcv ON MQ1CP007(MQCP_BAcv) TABLESPACE UC4_INDEX;CREATE INDEX NK_MQ1CP007_Prio ON MQ1CP007(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status) TABLESPACE UC4_INDEX;CREATE INDEX NK_MQ1CP007_SchTime ON MQ1CP007(MQCP_SchedTime) TABLESPACE UC4_INDEX;
20171003/152952.356 - U00045142 Cannot create table 'MQ1CP007'.
20171003/152952.469 - U00003432 Termination of Server 'UC4_EXP2#CP007' initiated.

Further investigation revealed that the error was due to a bug, documented in PRB00138707.

When the JCP starts up, it checks whether dedicated MQ tables for this particular CP exist. If they do not exist yet, it tries to create them. (In this case, the process failed because in our environment, we use non-default tablespace names.)

It is notable that the JCP is able to create its own MQ tables in the database.

Up until v12.1, the AE was not able to create this missing MQ tables by itself. If one wanted to run more than 5 communications processes, one had to manually create the tables for any CP beyond CP5. A technical article describes the process (for v11):

000009042 Oracle Statement to Create a New CP Table within Oracle

The new capability of the Java Communications Process to create missing MQ tables raises an interesting question: is the regular CP also capable of doing this?

There is also a specific KE for this problem:
000010021 New CP or JCP doesn’t start: U00045142 Cannot create table 'MQ1CPnnn'

• In v12.1, is the normal CP also capable of creating its MQ tables if they are missing, or is this capability limited to the JCP?
  

I am still hoping to get an answer to this question:

• In v12.1, is the normal CP also capable of creating its MQ tables if they are missing, or is this capability limited to the JCP?
  

U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: b97a20  DB-HDBC: be01b0
U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
U00003592 UCUDB - Status: '' Native error: '942' Msg: 'ORA-00942: table or view does not exist'
U00003594 UCUDB Ret: '3590' opcode: 'SLCO' SQL Stmnt: 'SELECT * FROM MQ2CP006 WHERE MQCP_SchedTime<=? AND MQCP_Status=0 ORDER BY MQCP_Priority, MQCP_SchedTime, MQCP_PK'
U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-00942: table or view does not exist'


it might be a good idea to add this capability to the normal CP too.

It does sound like a good idea. Please submit an enhancement request for it to reach our PM team.

Best regards,

Antoine

I just submitted the idea:

Communications Process (CP) should be able to create its own MQ tables

The new Java Communications Process (JCP) added in AWA v12.1 adds  a new and valuable capability: when it starts up, it creates its own MQ  tables in the AE database if they do not already exist. The normal  (non-Java) Communications Process (ucsrvcp) should also have this  capability. It is important that this function work with AE databases  that use non-standard tablespace names. (See PRB00138707.)

If you like the idea, please vote for it.

Hi Michael,

thanks for this helpful thread.

We are facing exactly the same issue here. We are installing 2 Engines in active/active mode (HA), so we need to create a JCP on both engines.

The first one is working fine.. But when we try to start the 2nd one, it dies after a few seconds with this error message :

20180420/161205.842 - U00003590 UCUDB - DB error: '42000', 'ORA-00933: SQL command not properly ended
', '933', 'java.sql.SQLSyntaxErrorException'
20180420/161205.842 - SQL Statement which caused this DB error:
20180420/161205.842 - 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;
20180420/161205.843 - U00045142 Cannot create table 'MQ1CP006'.
20180420/161205.856 - U00003432 Termination of Server 'AUTP7PRD#CP006' initiated. 

I am curious to know about the workaround. I tried to go to the link you shared but as the support plateform just changed, it results in a dead link.

Many thanks by advance !

Regards

Ok So I actuallu found the answer and it's working now.

What was happening is that, by default, there is 5 tables created in the DB for CPs and my JCP is defined as CP6.

So what has to be done is to run a sql script against Automic DB. This script is provided by support (you can find this piece of code for the 5 first CPs in the script in full image : db folder > oracle > your AE version >  up uc_ddl.sql)

The exact script to create the 6th CP table is there :

Oracle Statement to Create a New CP Table within O - CA Knowledge 

you can, of course create other CP table # by adapting this script

Hope it helps..