Michael_Lowry

AE v12.1 automatically creates MQ tables for new CPs?

Discussion created by Michael_Lowry on Oct 10, 2017
Latest reply on Apr 20, 2018 by greg_room

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?

Outcomes