Antoine_Sauteron_1266

Having more than 5 Communication Processes : how to add them ?

Discussion created by Antoine_Sauteron_1266 on Feb 21, 2017
By default an Automation Engine only supports 5 Communication Processes, because of the DB schema. Contrarily to WPs, each CP has one (v11.1 and below) or 2 (v11.2 and above) dedicated MQ table(s).
Therefore having more than 5 CPs also means adding dedicated MQ tables.

In the examples below we are going to see how to add a 6th CP, called CP006. You will need to run the same queries for every CP you want to add.

You will also need to add the CP(s) to ucsrv.ini/

1 - Version 11.1 and lower (version 9, 10 & 11.1)

There is only one MQ table per CP.

Oracle:
CREATE TABLE MQCP006  (
        MQCP_PK NUMBER(38,0) NOT NULL,
        MQCP_System VARCHAR2 (8 CHAR) NULL,
        MQCP_CAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_CSRName VARCHAR2 (8 CHAR) NULL,
        MQCP_CAcv NUMBER(38,0) NULL,
        MQCP_BAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_BSRName VARCHAR2 (8 CHAR) NULL,
        MQCP_BAcv NUMBER(38,0) NULL,
        MQCP_FAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_LogAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_PhysAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_BTable VARCHAR2 (8 CHAR) NULL,
        MQCP_SchedTime DATE NULL,
        MQCP_Status NUMBER(38,0) NULL,
        MQCP_Priority NUMBER(38,0) NULL,
        MQCP_DRole VARCHAR2 (32 CHAR) NULL,
        MQCP_LAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_Len NUMBER(38,0) NULL,
        MQCP_Msg BLOB NULL,
        CONSTRAINT PK_MQCP006 PRIMARY KEY
        (
                MQCP_PK
        ) USING INDEX  TABLESPACE UC4_INDEX
) LOB (MQCP_Msg) STORE AS (ENABLE STORAGE IN ROW CACHE) TABLESPACE UC4_DATA
  ENABLE ROW MOVEMENT NOCOMPRESS;
CREATE SEQUENCE SQ_MQCP006
        INCREMENT BY 1 START WITH 1 MAXVALUE 999999999
        MINVALUE 1 CYCLE CACHE 1000 NOORDER;
CREATE   INDEX NK_MQCP006_BAcv ON MQCP006 (MQCP_BAcv) TABLESPACE UC4_INDEX;
CREATE   INDEX NK_MQCP006_Prio ON MQCP006 (MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status) TABLESPACE UC4_INDEX;
CREATE   INDEX NK_MQCP006_SchedTime ON MQCP006 (MQCP_SchedTime) TABLESPACE UC4_INDEX;
SQL Server:
CREATE TABLE MQCP006
(
        MQCP_PK int IDENTITY NOT NULL,
        MQCP_System varchar (8) NULL,
        MQCP_CAddr varchar (32) NULL,
        MQCP_CSRName varchar (8) NULL,
        MQCP_CAcv int NULL,
        MQCP_BAddr varchar (32) NULL,
        MQCP_BSRName varchar (8) NULL,
        MQCP_BAcv int NULL,
        MQCP_FAddr varchar (32) NULL,
        MQCP_LogAddr varchar (32) NULL,
        MQCP_PhysAddr varchar (32) NULL,
        MQCP_BTable varchar (8) NULL,
        MQCP_SchedTime datetime NULL,
        MQCP_Status int NULL,
        MQCP_Priority int NULL,
        MQCP_DRole varchar (32) NULL,
        MQCP_LAddr varchar (32) NULL,
        MQCP_Len int NULL,
        MQCP_Msg varbinary (max) NULL,

        CONSTRAINT PK_MQCP006 PRIMARY KEY NONCLUSTERED
        (                MQCP_PK
        )
)
GO
CREATE   INDEX NK_MQCP006_BAcv ON MQCP006(MQCP_BAcv)
GO
CREATE  CLUSTERED INDEX NK_MQCP006_Prio ON MQCP006(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status)
GO
CREATE INDEX NK_MQCP006_SchedTime ON MQCP006(MQCP_SchedTime)
GO

2 - Version 11.2 and above

Because of the Zero Downtime Upgrade, you will have to create 2 MQ tables per CP.

Oracle :
CREATE TABLE MQ1CP006  (
        MQCP_PK NUMBER(38,0) NOT NULL,
        MQCP_System VARCHAR2 (8 CHAR) NULL,
        MQCP_CAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_CSRName VARCHAR2 (8 CHAR) NULL,
        MQCP_CAcv NUMBER(38,0) NULL,
        MQCP_BAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_BSRName VARCHAR2 (8 CHAR) NULL,
        MQCP_BAcv NUMBER(38,0) NULL,
        MQCP_FAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_LogAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_PhysAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_BTable VARCHAR2 (8 CHAR) NULL,
        MQCP_SchedTime DATE NULL,
        MQCP_Status NUMBER(38,0) NULL,
        MQCP_Priority NUMBER(38,0) NULL,
        MQCP_DRole VARCHAR2 (32 CHAR) NULL,
        MQCP_LAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_Len NUMBER(38,0) NULL,
        MQCP_Msg BLOB NULL,
        CONSTRAINT PK_MQ1CP006 PRIMARY KEY
        (
                MQCP_PK
        ) USING INDEX  TABLESPACE UC4_INDEX
) LOB (MQCP_Msg) STORE AS (ENABLE STORAGE IN ROW CACHE) TABLESPACE UC4_DATA ENABLE ROW MOVEMENT NOCOMPRESS;
CREATE SEQUENCE SQ_MQ1CP006
        INCREMENT BY 1 START WITH 1 MAXVALUE 2147483647
        MINVALUE 1 CYCLE CACHE 1000 NOORDER;
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;

CREATE TABLE MQ2CP006  (
        MQCP_PK NUMBER(38,0) NOT NULL,
        MQCP_System VARCHAR2 (8 CHAR) NULL,
        MQCP_CAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_CSRName VARCHAR2 (8 CHAR) NULL,
        MQCP_CAcv NUMBER(38,0) NULL,
        MQCP_BAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_BSRName VARCHAR2 (8 CHAR) NULL,
        MQCP_BAcv NUMBER(38,0) NULL,
        MQCP_FAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_LogAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_PhysAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_BTable VARCHAR2 (8 CHAR) NULL,
        MQCP_SchedTime DATE NULL,
        MQCP_Status NUMBER(38,0) NULL,
        MQCP_Priority NUMBER(38,0) NULL,
        MQCP_DRole VARCHAR2 (32 CHAR) NULL,
        MQCP_LAddr VARCHAR2 (32 CHAR) NULL,
        MQCP_Len NUMBER(38,0) NULL,
        MQCP_Msg BLOB NULL,
        CONSTRAINT PK_MQ2CP006 PRIMARY KEY
        (
                MQCP_PK
        ) USING INDEX  TABLESPACE UC4_INDEX
) LOB (MQCP_Msg) STORE AS (ENABLE STORAGE IN ROW CACHE) TABLESPACE UC4_DATA ENABLE ROW MOVEMENT NOCOMPRESS;
CREATE SEQUENCE SQ_MQ2CP006
        INCREMENT BY 1 START WITH 1 MAXVALUE 2147483647
        MINVALUE 1 CYCLE CACHE 1000 NOORDER;
CREATE   INDEX NK_MQ2CP006_BAcv ON MQ2CP006 (MQCP_BAcv) TABLESPACE UC4_INDEX;
CREATE   INDEX NK_MQ2CP006_Prio ON MQ2CP006 (MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status) TABLESPACE UC4_INDEX;
CREATE   INDEX NK_MQ2CP006_SchTime ON MQ2CP006 (MQCP_SchedTime) TABLESPACE UC4_INDEX;

MS SQL Server:
CREATE TABLE MQ1CP006
(
        MQCP_PK int IDENTITY NOT NULL,
        MQCP_System varchar (8) NULL,
        MQCP_CAddr varchar (32) NULL,
        MQCP_CSRName varchar (8) NULL,
        MQCP_CAcv int NULL,
        MQCP_BAddr varchar (32) NULL,
        MQCP_BSRName varchar (8) NULL,
        MQCP_BAcv int NULL,
        MQCP_FAddr varchar (32) NULL,
        MQCP_LogAddr varchar (32) NULL,
        MQCP_PhysAddr varchar (32) NULL,
        MQCP_BTable varchar (8) NULL,
        MQCP_SchedTime datetime NULL,
        MQCP_Status int NULL,
        MQCP_Priority int NULL,
        MQCP_DRole varchar (32) NULL,
        MQCP_LAddr varchar (32) NULL,
        MQCP_Len int NULL,
        MQCP_Msg varbinary (max) NULL,
       CONSTRAINT PK_MQ1CP006 PRIMARY KEY
        (                MQCP_PK
        )
)
GO
CREATE   INDEX NK_MQ1CP006_BAcv ON MQ1CP006(MQCP_BAcv)
GO
CREATE   INDEX NK_MQ1CP006_Prio ON MQ1CP006(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status)
GO
CREATE   INDEX NK_MQ1CP006_SchTime ON MQ1CP006(MQCP_SchedTime)
GO

CREATE TABLE MQ2CP006
(
        MQCP_PK int IDENTITY NOT NULL,
        MQCP_System varchar (8) NULL,
        MQCP_CAddr varchar (32) NULL,
        MQCP_CSRName varchar (8) NULL,
        MQCP_CAcv int NULL,
        MQCP_BAddr varchar (32) NULL,
        MQCP_BSRName varchar (8) NULL,
        MQCP_BAcv int NULL,
        MQCP_FAddr varchar (32) NULL,
        MQCP_LogAddr varchar (32) NULL,
        MQCP_PhysAddr varchar (32) NULL,
        MQCP_BTable varchar (8) NULL,
        MQCP_SchedTime datetime NULL,
        MQCP_Status int NULL,
        MQCP_Priority int NULL,
        MQCP_DRole varchar (32) NULL,
        MQCP_LAddr varchar (32) NULL,
        MQCP_Len int NULL,
        MQCP_Msg varbinary (max) NULL,

       CONSTRAINT PK_MQ2CP006 PRIMARY KEY
        (                MQCP_PK
        )
)
GO
CREATE   INDEX NK_MQ2CP006_BAcv ON MQ2CP006(MQCP_BAcv)
GO
CREATE   INDEX NK_MQ2CP006_Prio ON MQ2CP006(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status)
GO
CREATE   INDEX NK_MQ2CP006_SchTime ON MQ2CP006(MQCP_SchedTime)
GO

Outcomes