Hi there,
This one is driving me crazy.. I successfully created this trigger in one
of our test environments. When I try and create it in our production
environment I get the following error:
CREATE TRIGGER CTSDB2.CIS03A01
AFTER INSERT ON CTSDB2.CIS_INVC_TYPE
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.TYPE_OF_INVC_CD = 'S')
INSERT INTO CTSDB2.CIS_INVC_TYPE_S
(KEY_NBR, TYPE_OF_BILL_CD, TYPE_OF_INVC_CD, EFF_START_TMST
, EFF_END_TMST, INVC_ADDR_CD, HIST_RMKS, CRTE_TMST
, CRTE_BY_ID, BIL2_CUST_NBR1, BIL2_CUST_NBR2
, PKUP_CUST_NBR, HOLD_IND)
VALUES (N.KEY_NBR, N.TYPE_OF_BILL_CD, N.TYPE_OF_INVC_CD
, N.EFF_START_TMST
, N.EFF_END_TMST, N.INVC_ADDR_CD, N.HIST_RMKS, N.CRTE_TMST
, N.CRTE_BY_ID, N.BIL2_CUST_NBR1, N.BIL2_CUST_NBR2
, N.PKUP_CUST_NBR, N.HOLD_IND);
ROR ON CREATE COMMAND, EXECUTE FUNCTION
LT OF SQL STATEMENT:
408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
SQL STATEMENT
NUMBER : 1 SQLCODE -189, SQLSTATE 22522, AND MESSAGE TOKENS 00000
418I SQLSTATE = 56059 SQLSTATE RETURN CODE
415I SQLERRP = DSNXEASC SQL PROCEDURE DETECTING ERROR
416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000
INFORMATION
I have verified that the tables between production and the test environment
are defined exactly alike, as far as I can tell. Has anyone run into this
kind of an error? Any insight would be appreciated.. The transistion
variable is defined as a char field(1) and I do understand that the data
type inherits the subtype and CCSID of the column of the triggering table.
They are defined the same, so I am really at a loss as to why this works in
one environment and not another. I am probably overlooking something
obvious, but I can't see the forest for the trees.. TIA..
Marty Killen
SR DB2 DBA
CNF INC
Information Technology
503-450-2681 - CNF INC
503-450-6038 - Conway
e-mail - [login to unmask email]
"Kind words can be short and easy, but their echoes are truly endless"
Mother Theresa
This one is driving me crazy.. I successfully created this trigger in one
of our test environments. When I try and create it in our production
environment I get the following error:
CREATE TRIGGER CTSDB2.CIS03A01
AFTER INSERT ON CTSDB2.CIS_INVC_TYPE
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.TYPE_OF_INVC_CD = 'S')
INSERT INTO CTSDB2.CIS_INVC_TYPE_S
(KEY_NBR, TYPE_OF_BILL_CD, TYPE_OF_INVC_CD, EFF_START_TMST
, EFF_END_TMST, INVC_ADDR_CD, HIST_RMKS, CRTE_TMST
, CRTE_BY_ID, BIL2_CUST_NBR1, BIL2_CUST_NBR2
, PKUP_CUST_NBR, HOLD_IND)
VALUES (N.KEY_NBR, N.TYPE_OF_BILL_CD, N.TYPE_OF_INVC_CD
, N.EFF_START_TMST
, N.EFF_END_TMST, N.INVC_ADDR_CD, N.HIST_RMKS, N.CRTE_TMST
, N.CRTE_BY_ID, N.BIL2_CUST_NBR1, N.BIL2_CUST_NBR2
, N.PKUP_CUST_NBR, N.HOLD_IND);
ROR ON CREATE COMMAND, EXECUTE FUNCTION
LT OF SQL STATEMENT:
408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
SQL STATEMENT
NUMBER : 1 SQLCODE -189, SQLSTATE 22522, AND MESSAGE TOKENS 00000
418I SQLSTATE = 56059 SQLSTATE RETURN CODE
415I SQLERRP = DSNXEASC SQL PROCEDURE DETECTING ERROR
416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000
INFORMATION
I have verified that the tables between production and the test environment
are defined exactly alike, as far as I can tell. Has anyone run into this
kind of an error? Any insight would be appreciated.. The transistion
variable is defined as a char field(1) and I do understand that the data
type inherits the subtype and CCSID of the column of the triggering table.
They are defined the same, so I am really at a loss as to why this works in
one environment and not another. I am probably overlooking something
obvious, but I can't see the forest for the trees.. TIA..
Marty Killen
SR DB2 DBA
CNF INC
Information Technology
503-450-2681 - CNF INC
503-450-6038 - Conway
e-mail - [login to unmask email]
"Kind words can be short and easy, but their echoes are truly endless"
Mother Theresa