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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shethsheetal@gmail.com - 28 Mar 2007 02:24 GMT

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.

Reply to this Message

Serge Rielau - 28 Mar 2007 03:08 GMT

> I am having some problem with the below mentioned trigger.
>
[quoted text clipped - 25 lines]
>
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

shethsheetal@gmail.com - 29 Mar 2007 18:25 GMT

> shethshee...@gmail.com wrote:
> > I am having some problem with the below mentioned trigger.
[quoted text clipped - 38 lines]
>
> - Show quoted text -

I am working on db2 version 8 new function mode on z/os v1.8

Reply to this Message

Tonkuma - 28 Mar 2007 06:58 GMT

On Mar 28, 10:24 am, shethshee...@gmail.com wrote:

> I am having some problem with the below mentioned trigger.
>
[quoted text clipped - 9 lines]
> 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.

Reply to this Message

shethsheetal@gmail.com - 29 Mar 2007 16:58 GMT

> On Mar 28, 10:24 am, shethshee...@gmail.com wrote:> I am having some problem with the below mentioned trigger.
>
[quoted text clipped - 20 lines]
> 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.

Reply to this Message

Knut Stolze - 28 Mar 2007 09:18 GMT

> I am having some problem with the below mentioned trigger.
>
[quoted text clipped - 18 lines]
> 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.)

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message