By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,830 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,830 IT Pros & Developers. It's quick & easy.

Errror Declaring Local Variable in the trigger body on db2 udb v8 on z/0s

P: n/a
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NEW_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATUS;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70001','UPDATE FAILED'));
END

When we try to create it thruogh erwin data modeler we get the
following error

[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQL error '-20100' in the message file on this
workstation. The error was returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059

Execution Failed!
If i remove the declare,diagnostic & value statement & just keep the
call statement then it works.

Do you have any clue as to why this might be happening.

Mar 28 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
sh**********@gmail.com wrote:
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NEW_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATUS;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70001','UPDATE FAILED'));
END

When we try to create it thruogh erwin data modeler we get the
following error

[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQL error '-20100' in the message file on this
workstation. The error was returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059

Execution Failed!
If i remove the declare,diagnostic & value statement & just keep the
call statement then it works.

Do you have any clue as to why this might be happening.
Which version and platform? Note that there is no VALUES statement on
zOS....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 28 '07 #2

P: n/a
On Mar 28, 10:24 am, shethshee...@gmail.com wrote:
I am having some problem with the below mentioned trigger.

CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NEW_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATUS;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70001','UPDATE FAILED'));
END
By looking SQL Reference manual, followings may be spec. of DB2 for z/
OS.
DB2 for z/OS supports VALUES in trigger at least V6 or later.
But, it doesn't suppot "GET DIAGNOSTICS RS=RETURN_STATUS" on V7.
V7 supports only "GET DIAGNOSTICS SQL-variable=ROW_COUNT".
Although, V8 supports "GET DIAGNOSTICS SQL-
variable=DB2_RETURN_STATUS", GET DIAGNOSTICS statement can't be used
in TRIGGER body.
DECLARE statement is also not supported in TRIGGER body.

I think that it is worth to try to use RAISE_ERROR in OMNIP.SPSOICL0
Procedure.


Mar 28 '07 #3

P: n/a
sh**********@gmail.com wrote:
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NEW_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATUS;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70001','UPDATE FAILED'));
END

When we try to create it thruogh erwin data modeler we get the
following error

[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQL error '-20100' in the message file on this
workstation. The error was returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059
The "original tokens" say that you got a SQL0104. I believe that it chokes
on the INTEGER keyword. Try just INT instead. (I had such a problem once,
but I don't remember if it was in a similar context.)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Mar 28 '07 #4

P: n/a
On Mar 27, 10:58 pm, "Tonkuma" <tonk...@jp.ibm.comwrote:
On Mar 28, 10:24 am, shethshee...@gmail.com wrote:I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NEW_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATUS;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70001','UPDATE FAILED'));
END

By looking SQL Reference manual, followings may be spec. ofDB2for z/
OS.DB2for z/OS supports VALUES in trigger at least V6 or later.
But, it doesn't suppot "GET DIAGNOSTICS RS=RETURN_STATUS" on V7.
V7 supports only "GET DIAGNOSTICS SQL-variable=ROW_COUNT".
Although, V8 supports "GET DIAGNOSTICS SQL-variable=DB2_RETURN_STATUS", GET DIAGNOSTICS statement can't be used
in TRIGGER body.
DECLARE statement is also not supported in TRIGGER body.

I think that it is worth to try to use RAISE_ERROR in OMNIP.SPSOICL0
Procedure.

In this case the ability of the trigger to handle error in z/os is
severly restricted.

can you let me know what would be the syntax for the call statement
within the trigger with 'out parameters'.Does any variable have to be
defined in the trigger for capturing the out parameter.
Mar 29 '07 #5

P: n/a
On Mar 27, 7:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
shethshee...@gmail.com wrote:
I am having some problem with the below mentioned trigger.
CREATE TRIGGER D.TBA
AFTER UPDATE OF TBAA.CIC ON TBAA
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE RS INTEGER DEFAULT 0;
CALL OMNIP.SPSOICL0
(NEW_ROW.CID,NEW_ROW.CIC);
GET DIAGNOSTICS RS=RETURN_STATUS;
VALUES(CASE WHEN RS<0 THEN RAISE_ERROR('70001','UPDATE FAILED'));
END
When we try to create it thruogh erwin data modeler we get the
followingerror
[IBM] [CLI Driver] [DB@] SQL0969N There is no message text
corresponding to SQLerror'-20100' in the message file on this
workstation. Theerrorwas returned from module "DSNHSQL" with
original tokens "2 -104 42601 INTEGER , STATEMENT".
SQLSTATE=56059
Execution Failed!
If i remove the declare,diagnostic & value statement & just keep the
call statement then it works.
Do you have any clue as to why this might be happening.

Which version and platform? Note that there is no VALUES statement on
zOS....

Cheers
Serge

--
Serge RielauDB2Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -
I am working on db2 version 8 new function mode on z/os v1.8

Mar 29 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.