Creating MQCP tables above MQCP005 - DB2

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Creating MQCP tables above MQCP005 - DB2

Environment

Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

Detailed Description and Symptoms

Some Customers need more than the predefined 5 CP Tables, in a standard System a maximum amount of 5 CPs can be activated

therefore a statement can add another table for another CP (can be used several times to use more cps)

 

Investigation

?

there is no investigation needed. on each Serverstart following message appears

U0003594 UCUDB Ret: '3590' OpCode: 'EXEC' SQL Stmnt: 'DELETE FROM MQCP006'
U0003590 DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00942: table or view does not exist'

to add this MQCP006 you need the statement we provide, it will NOT make the message disappear, it will only change ascending to the next MQCP list (e.g. MQCP007, MQCP008...)

however the message is not at all dangerous

 

Solution

?

Following Statement adds an additional MQCP table to your system:

NOTE if you need more than 6 CPs you have to change every occurance (in the statement shown bold) of CP006 to CP007, CP008 ... ascending. The Statement only creates one CP Table, if you need more than one additional CP, you have to run it more often, changing the bold entries:

 

Version 9.0

 

CREATE TABLE MQCP006_BLOB

(

MQCP_PK int NOT NULL,

MQCP_System varchar (8) ,

MQCP_CAddr varchar (32) ,

MQCP_CSRName varchar (8) ,

MQCP_CAcv int ,

MQCP_BAddr varchar (32) ,

MQCP_BSRName varchar (8) ,

MQCP_BAcv int ,

MQCP_FAddr varchar (32) ,

MQCP_LogAddr varchar (32) ,

MQCP_PhysAddr varchar (32) ,

MQCP_BTable varchar (8) ,

MQCP_SchedTime timestamp ,

MQCP_Status int ,

MQCP_Priority int ,

MQCP_DRole varchar (32) ,

MQCP_LAddr varchar (32) ,

MQCP_Len int ,

MQCP_Msg blob (20480 k) COMPACT ,

CONSTRAINT PK_MQCP006_BLOB PRIMARY KEY

(

MQCP_PK

)

);

 

CREATE TABLE MQCP006_NOBLOB

(

MQCP_PK int NOT NULL,

MQCP_System varchar (8) ,

MQCP_CAddr varchar (32) ,

MQCP_CSRName varchar (8) ,

MQCP_CAcv int ,

MQCP_BAddr varchar (32) ,

MQCP_BSRName varchar (8) ,

MQCP_BAcv int ,

MQCP_FAddr varchar (32) ,

MQCP_LogAddr varchar (32) ,

MQCP_PhysAddr varchar (32) ,

MQCP_BTable varchar (8) ,

MQCP_SchedTime timestamp ,

MQCP_Status int ,

MQCP_Priority int ,

MQCP_DRole varchar (32) ,

MQCP_LAddr varchar (32) ,

MQCP_Len int ,

MQCP_Msg varchar(30000) for bit data ,

CONSTRAINT PK_MQCP006_NOBLOB PRIMARY KEY

(

MQCP_PK

)

);

 

CREATE SEQUENCE SQ_MQCP006

START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 999999999

CYCLE CACHE 1000 NO ORDER;

 

CREATE VIEW MQCP006 as

SELECT MQCP_PK, MQCP_System, MQCP_CAddr, MQCP_CSRName, MQCP_CAcv, MQCP_BAddr, MQCP_BSRName, MQCP_BAcv, MQCP_FAddr, MQCP_LogAddr, MQCP_PhysAddr, MQCP_BTable, MQCP_SchedTime, MQCP_Status, MQCP_Priority, MQCP_DRole, MQCP_LAddr, MQCP_Len, blob(MQCP_Msg) as MQCP_Msg from MQCP006_NOBLOB

UNION ALL

SELECT MQCP_PK, MQCP_System, MQCP_CAddr, MQCP_CSRName, MQCP_CAcv, MQCP_BAddr, MQCP_BSRName, MQCP_BAcv, MQCP_FAddr, MQCP_LogAddr, MQCP_PhysAddr, MQCP_BTable, MQCP_SchedTime, MQCP_Status, MQCP_Priority, MQCP_DRole, MQCP_LAddr, MQCP_Len, MQCP_Msg from MQCP006_BLOB;

 

--#SET TERMINATOR !

CREATE TRIGGER MQCP006_INS INSTEAD OF INSERT ON MQCP006

REFERENCING NEW AS newvalue

FOR EACH ROW MODE DB2SQL

IF LENGTH(newvalue.MQCP_Msg) < 30001

THEN

INSERT INTO MQCP006_NOBLOB

VALUES (NEXTVAL FOR SQ_MQCP006, MQCP_System, MQCP_CAddr, MQCP_CSRName, MQCP_CAcv, MQCP_BAddr, MQCP_BSRName, MQCP_BAcv, MQCP_FAddr, MQCP_LogAddr, MQCP_PhysAddr, MQCP_BTable, MQCP_SchedTime, MQCP_Status, MQCP_Priority, MQCP_DRole, MQCP_LAddr, MQCP_Len, MQCP_Msg);

ELSE

INSERT INTO MQCP006_BLOB

VALUES (NEXTVAL FOR SQ_MQCP006, MQCP_System, MQCP_CAddr, MQCP_CSRName, MQCP_CAcv, MQCP_BAddr, MQCP_BSRName, MQCP_BAcv, MQCP_FAddr, MQCP_LogAddr, MQCP_PhysAddr, MQCP_BTable, MQCP_SchedTime, MQCP_Status, MQCP_Priority, MQCP_DRole, MQCP_LAddr, MQCP_Len, MQCP_Msg);

END IF!

--#SET TERMINATOR ;

CREATE INDEX NK_MQCP006_BAcv_B ON MQCP006_BLOB(MQCP_BAcv);

CREATE INDEX NK_MQCP006_BAcv_NB ON MQCP006_NOBLOB(MQCP_BAcv);

CREATE INDEX NK_MQCP006_Prio_B ON MQCP006_BLOB(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status);

CREATE INDEX NK_MQCP006_Prio_NB ON MQCP006_NOBLOB(MQCP_Priority, MQCP_SchedTime, MQCP_PK, MQCP_Status);