Hello All,

We are having hard time with Zos Db2 triggers, when I run below mentioned query, I get a error message saying that BEGIN ATOMIC is not supported.

Below is the query that we are struggling with.

--#SET DELIMITER ~
CREATE PROCEDURE @METADI2.I27_SP(IN NEW_VAL_COTF_USUINTER CHAR(10)) LANGUAGE SQL
BEGIN ATOMIC
DECLARE SYNCPOINT INTEGER DEFAULT 0;
SET SYNCPOINT = NEXT VALUE FOR @METADI2.CLSyncSEQ;
.....
.....
END~

So can any one kindly let me know whether BEGIN ATOMIC is not supported within procedure body? Also where can I get detailed SQL reference for ZOS.

Any help will be highly appreciated.

Anticipating your response.
Mahesh

forgot to mention the error message in my last comment.

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "ATOMIC". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: NOT ATOMIC, <EMPTY>

simply omit the keyword ATOMIC.

look here for the SQL reference
DB2 V9.1 - DB2 SQL - CREATE PROCEDURE (SQL - native)

Hi Umayer,

Thanks for responding.

ATOMIC keyword plays an important role in ensuring that every thing is rolled back if something goes wrong, hence we really need this to be that part of our SQL.

Can you please throw some light on why are we not able to use ATOMIC? Is there any way we can retain ATOMIC keyword?

Please let us know.


Thanks again,
Mahesh

Actions taken by a stored procedure are part of the Unit Of Work.

Create the stored procedure with COMMIT ON RETURN NO ( which is the default ). Then, if the calling program rolles back, all changes made by the stored procedure are rolled back, too.

If you create the stored procedure with COMMIT ON RETURN YES, the current Unit Of Work will be committed when the stored procedure returns control to the calling program.

BEGIN ATOMIC is allowed for triggers.
BEGIN NOT ATOMIC is allowed for stored procedures. ( The text string "NOT ATOMIC" may be omitted, it's the only allowed option ).

Hi Umayer,

That's solved my problem, many many thanks to you