Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    27

    Unanswered: Problem with BEGIN ATOMIC within trigger body

    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

  2. #2
    Join Date
    Feb 2009
    Posts
    27
    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>

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    simply omit the keyword ATOMIC.

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

  4. #4
    Join Date
    Feb 2009
    Posts
    27
    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

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    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 ).
    Last edited by umayer; 08-25-10 at 07:52.

  6. #6
    Join Date
    Feb 2009
    Posts
    27
    Hi Umayer,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •