SQL statements to create additional CP

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

In CA Automation Engine Version 12.0.x, by default 5 CP tables are created. If you want to add more CPs, you have to create the tables for these CPs manually by SQL-Statements.

Starting from v12.1.0, the additional CP tables are created automatically when an additional CP is started for the first time.
In case you are using a tablespace names other than the standard UC4_DATA / UC4_INDEX, you still have to use the statement below.

Environment

Database: Oracle

Resolution

Oracle: These are the statements is for CP006.

• If you need additional CP tables you have to adapt the statement accordingly replacing all the occurrences of CP006
• To be able to use ZDU, you also need to create the MQ2CP006 table, see second statement
• If you are using non-standard tablespace names you need to replace TABLESPACE UC4_DATA and TABLESPACE UC4_INDEX by the respective data and index tablespaces
   

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;

commit;