Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: atomic and not atomic - clarity

    Version:
    UDB Version 8.1 SQL PL

    Example:
    BEGIN NOT ATOMIC
    ...
    SQL STATEMENT 1
    SIGNAL SQLSTATE '70000'
    SQL STATEMENT 2
    END

    The spec says that in the above example the SQL STATEMENT 1 will NEITHER be rolled back or commited? What DOES this mean??? If it is neither rolled back or commited then is it in limbo???

    In my testing the SQL STATEMENT 1 was an insert into a table A and after the run of the above procedure the insert is NOT executed!

    You are the creator of your own destiny!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ATOMIC Compound Statement
    The ATOMIC compound statement, as the name suggests, can be thought of as a singular whole—if any unhandled error conditions arise within it, all statements which have been executed up to that point are considered to have failed as well and are therefore rolled back. ATOMIC compound statements cannot be nested inside other ATOMIC compound statements.

    In addition, you cannot use SAVEPOINTs or issue explicit COMMITs or ROLLBACKs from within an ATOMIC compound statement.

    NOTE

    COMMIT, ROLLBACK, SAVEPOINTS and nested ATOMIC compound statements are not allowed within an ATOMIC compound statement.


    NOT ATOMIC Compound Statement
    If an unhandled error (that is, no condition handler has been declared for the SQLSTATE raised) occurs within the compound statement, any work which is completed before the error will not be rolled back, but will not be committed either. The group of statements can only be rolled back if the unit of work is explicitly rolled back using ROLLBACK or ROLLBACK TO SAVEPOINT . You can also COMMIT successful statements if it makes sense to do so.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    Sathyaram_s:

    Your definition is in sync with the book!

    My question was specific to NOT ATOMIC - If the successfull SQL statements (before the error happened) are not committed or rolled back then what really happens to these statements? Or is it just how it has been implemented

    You are the creator of your own destiny!

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by db2guru1
    Sathyaram_s:

    Your definition is in sync with the book!

    My question was specific to NOT ATOMIC - If the successfull SQL statements (before the error happened) are not committed or rolled back then what really happens to these statements? Or is it just how it has been implemented
    They are not rolled back OR commited, so it's up to you to either do a ROLLBACK or COMMIT to determine what happens to them.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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