CCSID Error on trigger creation.

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

Marty,

You need PQ42601/UQ49651 PTF to fix the problem.

Thanks,
Mike Hsiao


"Killen, Martin W - CNF" wrote:

> 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
>
>
>

Marty,

I've run into something similar.

Don't remember why, but decided to put everything
starting at "FOR EACH ROW" on one line (creating one
very long line), and the error went away.

From others I think I understood that DB2 can have problems
differentiating semicolons and "end of statement" characters.

Try it and see what happens :)


Jef Jakobs
-----Original Message-----
From: Killen, Martin W - CNF [mailto:[login to unmask email]
Sent: Wednesday, March 21, 2001 12:53 PM
To: [login to unmask email]
Subject: CCSID Error on trigger creation.


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

Thanks for the suggestion, but alas, I get the same error..

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

> -----Original Message-----
> From: Jakobs, Jef [SMTP:[login to unmask email]
> Sent: Wednesday, March 21, 2001 12:06 PM
> To: [login to unmask email]
> Subject: Re: CCSID Error on trigger creation.
>
> Marty,
>
> I've run into something similar.
>
> Don't remember why, but decided to put everything
> starting at "FOR EACH ROW" on one line (creating one
> very long line), and the error went away.
>
> From others I think I understood that DB2 can have problems
> differentiating semicolons and "end of statement" characters.
>
> Try it and see what happens :)
>
>
> Jef Jakobs
> -----Original Message-----
> From: Killen, Martin W - CNF [mailto:[login to unmask email]
> Sent: Wednesday, March 21, 2001 12:53 PM
> To: [login to unmask email]
> Subject: CCSID Error on trigger creation.
>
>
> 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
>
>
>
> the
>
>
>
>
>
>
>

Thanks Mike.. I talked with our SYSPROG's and we do not have these applied
to our production environment yet.. They were working on getting these
applied, but hadn't got there yet.. Thanks for the quick response.. I did
hear from one other indvidual that they were having the exact same problem.
It has to do with our production tables being created around the V4 time and
our test tables are much younger... Thanks again..

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

> -----Original Message-----
> From: Mike Hsiao [SMTP:[login to unmask email]
> Sent: Wednesday, March 21, 2001 11:18 AM
> To: [login to unmask email]
> Subject: Re: CCSID Error on trigger creation.
>
> Marty,
>
> You need PQ42601/UQ49651 PTF to fix the problem.
>
> Thanks,
> Mike Hsiao
>
>
> "Killen, Martin W - CNF" wrote:
>
> > 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
> >
> >
> >
>
>
>
>
>
>
>

Hi Marty,


We had the same problem on our system. We found the PTFs but they had not yet
been applied.

The problem is due to the CCSID of pre v5 objects being 0. One alternative we
have implemented is to ALTER the CCSID of the database and tablespace to 37.

ALTER DATABASE <dbname> CCSID 37;
ALTER TABLESPACE <tsname> CCSID 37;

This works, and there are no hidden gotchas, as yet. U may try this if
possible.

Cheers,
Kals


-----Original Message-----
From: Killen, Martin W - CNF [SMTP:[login to unmask email]
Sent: Thursday, March 22, 2001 8:30 AM
To: [login to unmask email]
Subject: Re: CCSID Error on trigger creation.

Thanks Mike.. I talked with our SYSPROG's and we do not have these applied
to our production environment yet.. They were working on getting these
applied, but hadn't got there yet.. Thanks for the quick response.. I did
hear from one other indvidual that they were having the exact same problem.
It has to do with our production tables being created around the V4 time and
our test tables are much younger... Thanks again..

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

> -----Original Message-----
> From: Mike Hsiao [SMTP:[login to unmask email]
> Sent: Wednesday, March 21, 2001 11:18 AM
> To: [login to unmask email]
> Subject: Re: CCSID Error on trigger creation.
>
> Marty,
>
> You need PQ42601/UQ49651 PTF to fix the problem.
>
> Thanks,
> Mike Hsiao
>
>
> "Killen, Martin W - CNF" wrote:
>
> > 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

---------------------------------
Powered by http://www.telstra.com

Actually we're getting away with just ALTER TABLESPACE <TSNAME> CCSID 37;
in several of our non-Production environments (but only until the fix is
implemented as it's not the recommended approach).

We do have a Trigger in a development environment where we instead did an
ALTER DATABASE <DBNAME> CCSID 37;
and this has worked also. The Tablespace is still CCSID=0 so apparently
either can be used in creating the Trigger, although the manual seems to
recommend both be altered.

I do feel that we're lucky in that the former values of CCSID=0 equate to
our site default of CCSID=37 (through the DSNDECP zparm?) but we still
unloaded/reloaded the data anyway.

regards,
Steve

I 2nd Floor 484 St Kilda Rd, Melbourne

' (03) 9865 8557 7 (03) 9804 5368
* <mailto: [login to unmask email]>



> -----Original Message-----
> From: teldb2kals [SMTP:[login to unmask email]
> Sent: Thursday, March 22, 2001 9:32 AM
> To: [login to unmask email]
> Subject: Re: CCSID Error on trigger creation.
>
> Hi Marty,
>
>
> We had the same problem on our system. We found the PTFs but they had not
> yet
> been applied.
>
> The problem is due to the CCSID of pre v5 objects being 0. One alternative
> we
> have implemented is to ALTER the CCSID of the database and tablespace to
> 37.
>
> ALTER DATABASE <dbname> CCSID 37;
> ALTER TABLESPACE <tsname> CCSID 37;
>
> This works, and there are no hidden gotchas, as yet. U may try this if
> possible.
>
> Cheers,
> Kals
>
>
> -----Original Message-----
> From: Killen, Martin W - CNF [SMTP:[login to unmask email]
> Sent: Thursday, March 22, 2001 8:30 AM
> To: [login to unmask email]
> Subject: Re: CCSID Error on trigger creation.
>
> Thanks Mike.. I talked with our SYSPROG's and we do not have these
> applied
> to our production environment yet.. They were working on getting these
> applied, but hadn't got there yet.. Thanks for the quick response.. I
> did
> hear from one other indvidual that they were having the exact same
> problem.
> It has to do with our production tables being created around the V4 time
> and
> our test tables are much younger... Thanks again..
>
> 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
>
> > -----Original Message-----
> > From: Mike Hsiao [SMTP:[login to unmask email]
> > Sent: Wednesday, March 21, 2001 11:18 AM
> > To: [login to unmask email]
> > Subject: Re: CCSID Error on trigger creation.
> >
> > Marty,
> >
> > You need PQ42601/UQ49651 PTF to fix the problem.
> >
> > Thanks,
> > Mike Hsiao
> >
> >
> > "Killen, Martin W - CNF" wrote:
> >
> > > 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
>
> ---------------------------------
> Powered by http://www.telstra.com
>
>
>
>
>

Hi guys
I have a peculiar problem while creating Trigger. I am using Secondary
Auth-id. Using that I created all tables, Tablespaces,etc.
When creating a trigger with different schema (xxxx), the SQL got executed
with sqlcode = 0 but its not actually written in the SYSIBM.SYSTRIGGERS.
Several times I executed the sql but it returns SQLCODE = 0. I am executing
the thru SPUFI.

This seems to be a ridiculous, the same SQL (create trigger) can be executed
with PRIMARY AUTHID (I mean the userid, if that id has been given authority
to use the schema xxxx). I tried in different way by giving CREATIN ON
SCHEMA xxxx to SECONDARY-AUTHID, but that also not actually creating the
trigger but it returns SQLCODE = 0 in the SPUFI.

Does trigger support only primary auth-id..?
Can you suggest on this..?

TIA

Jane



_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

Hi,
We had the same problem a while ago: execute the CREATE TRIGGER statement under a secondary-auth-ID thru SPUFI, receive SQLCODE = 0, but have no trigger created (DB2 UDB for OS/390 V6.1).
We solved the problem by APAR PQ40202 (SERVICE CONTROLLER PROBLEM: BIND OF IMPLICITLY-CREATED TRIGGER PACKAGE FAILS DURING CREATE TRIGGER, BUT SQLCA'S SQLCODE = 0).
Hope this helps,

Gulin Atabek

-----Original Message-----
From: jane mike [mailto:[login to unmask email]
Sent: Thursday, December 27, 2001 12:46 PM
To: [login to unmask email]
Subject: Trigger Creation


Hi guys
I have a peculiar problem while creating Trigger. I am using Secondary
Auth-id. Using that I created all tables, Tablespaces,etc.
When creating a trigger with different schema (xxxx), the SQL got executed
with sqlcode = 0 but its not actually written in the SYSIBM.SYSTRIGGERS.
Several times I executed the sql but it returns SQLCODE = 0. I am executing
the thru SPUFI.

This seems to be a ridiculous, the same SQL (create trigger) can be executed
with PRIMARY AUTHID (I mean the userid, if that id has been given authority
to use the schema xxxx). I tried in different way by giving CREATIN ON
SCHEMA xxxx to SECONDARY-AUTHID, but that also not actually creating the
trigger but it returns SQLCODE = 0 in the SPUFI.

Does trigger support only primary auth-id..?
Can you suggest on this..?

TIA

Jane



_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx




==========-
Bu e-posta sadece yukarida isimleri belirtilen kisiler arasinda ozel haberlesme amacini tasimaktadir. Size yanlislikla ulasmissa lutfen mesaji geri gonderiniz ve sisteminizden siliniz. Turkiye Cumhuriyet Merkez Bankasi A.S. bu mesajin icerigi ile ilgili olarak hic bir hukuksal sorumlulugu kabul etmez.

This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system. The Central Bank of The Republic of Turkey does not accept legal responsibility for the contents of this message.

Hi Jane,

Could PQ40202 be your problem? PTF is UQ46393 for V6.

Neil Price
IMS and DB2 Systems Programmer / DBA
IS Central Systems
TNT Post Group, UK




"jane mike"
<[login to unmask email] Trigger Creation
L.COM>
Sent by: "DB2
Data Base
Discussion
List"
<[login to unmask email]
M>


27/12/2001
10:45
Please respond
to "DB2 Data
Base
Discussion
List"






Hi guys
I have a peculiar problem while creating Trigger. I am using Secondary
Auth-id. Using that I created all tables, Tablespaces,etc.
When creating a trigger with different schema (xxxx), the SQL got executed
with sqlcode = 0 but its not actually written in the SYSIBM.SYSTRIGGERS.
Several times I executed the sql but it returns SQLCODE = 0. I am executing
the thru SPUFI.

This seems to be a ridiculous, the same SQL (create trigger) can be
executed
with PRIMARY AUTHID (I mean the userid, if that id has been given authority
to use the schema xxxx). I tried in different way by giving CREATIN ON
SCHEMA xxxx to SECONDARY-AUTHID, but that also not actually creating the
trigger but it returns SQLCODE = 0 in the SPUFI.

Does trigger support only primary auth-id..?
Can you suggest on this..?

TIA

Jane

_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx








---------------------------------------------------------------------------------------------------------------
This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.
If you are not the intended recipient, please telephone or email the sender and delete this message and any attachment from your system.
If you are not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
---------------------------------------------------------------------------------------------------------------

Its working now after applied PTF UQ46393.
Thanks guys..


regards
jane

Hi Jane,

Could PQ40202 be your problem? PTF is UQ46393 for V6.

Neil Price
IMS and DB2 Systems Programmer / DBA
IS Central Systems
TNT Post Group, UK



_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com