Creating MQCP tables above MQCP005 - DB2

Document ID : KB000090080
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Creating MQCP tables above MQCP005 - DB2
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);