Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32

    Question Unanswered: How to use PRAGMA AUTONOMOUS_TRANSACTION in DB2 ?

    Hi, all

    I have a procedure in oracle that contains PRAGMA AUTONOMOUS_TRANSACTION, someone knows what is the statement in DB2 that makes the same think.

    Thanks a lot.
    Last edited by Alairj; 01-12-05 at 14:48.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There's no such thing in DB2.

  3. #3
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    But that isn't nothing in DB2, that can i do to make a commit or a rollback, only in the processes that are included in my procedure or routine?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There's always a workaround; however it's difficult to give adivce not knowing your reasons.

    AUTONOMOUS_TRANSACTION feature in Oracle is often used to overcome that "mutating table" problem, which doesn't exist in DB2 - therefore you may not need autonomous transactions at all when developing for DB2.

  5. #5
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    In my case i want AUTONOMOUS_TRANSACTION to do something like that:

    I have a procedure that executs a insert and i want to do a COMMIT only in this insert, if i execute before another insert this will not be commited, only the insert that contains the AUTONOMOUS_TRANSACTION, i don't know if you understand, i don't speek english very well.

    Thanks a lot.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I haven't tried it myself by you could see if the following would work for you:
    Code:
    -- this insert lives on
    INSERT ... INTO ...;
    SAVEPOINT SP1;
    -- this insert is conditional
    INSERT ...
    IF something is wrong THEN
       ROLLBACK TO SAVEPOINT SP1;
    END IF;
    COMMIT;

  7. #7
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    But in this case the db2 will commit the first insert, the second insert or both?

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think that if you roll back to the savepoint in the above example only the first insert will be committed; otherwise both will be committed.

  9. #9
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    In my case i want to commit only the second insert, the first i'll not commit, with a savepoint can i do that?

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you commit the second insert regardless of the success of the first one you might as well change their order; then the second becomes the first...

  11. #11
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    I know this, but i need the first insert to executes the second, i can't change the order of them, in oracle do that is simple, as the same as i can isolate a statement ( in this case a insert ) i only want to validate ( commit ) the rows inserted or updated in this statement, and the others statements that i execute after or before that will not be commited.

    Thanks a lot for your atention.

Posting Permissions

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