Oracle Statement to Create a New CP Table within Oracle

Document ID : KB000088369
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Note:  This information is for version 11.  It is not needed to add more tables for WP's.  This is only necessary for CP's.

The statement to add another CP table can be obtained from the Automation Engine full image. 
 
 
Resolution:
Go to the db folder > oracle > 11.2 > open up uc_ddl.sql. From there, you will need to create a MQ1CP006 table and a MQ2CP006 table. You will be creating CP006 because by default, the database already has CP's 1-5. If you will need more than this, you will have to increment to the next number. 
 
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;