Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    21

    Unanswered: Begin transaction in DB2

    Hallo,

    I am trying to initiate a transaction in DB2. Tried with 'begin', 'begin transaction', begin tran', with no success. The command interpreter does not recognize them. I does recognize 'commit' and 'rollback'.

    Can anyone help?

    Thanks a lot in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Transactions in DB2 are implied rather than explicitly defined.
    A transaction is essentially all the work from one persistence
    point to another. A persistence point is where data is actually
    written to the database or not written, i.e COMMIT or ROLLBACK.

    In other words a transaction is all the work from one COMMIT /
    ROLLBACK to the next COMMIT / ROLLBACK.

    I do not believe DB2 actually uses the term transaction any more.
    They prefer to use Unit of Work (UOW).

    HTH

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    21

    Talking

    Thank you Andy!!
    One more thing... so if I insert a row using its JDBC driver, can I afterwards perform a "ROLL BACK" if I get an exception???

    Thanks again!!!

    Cheers!!!!

  4. #4
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Begin transaction in DB2

    Heys,

    All statements in DB2 initiates a transaction, thus you donīt need to state a 'begin transaction', but as any transaction, itīll only end when you call a 'commit' or 'rollback'. Take a look at the Transaction Isolation Level and choose which fits your needs best.
    Just be careful when using Repeatable Read isolation level, because itīll lock all objects untill a commit/rollback is called, even when the transaction is only a 'select' statement. But will give you security and consistency on the data youīre working with.

    But anyways, go through the Administration Guide to choose the Isolation Level for your system. Also look at the Command Reference for the command 'CHANGE ISOLATION LEVEL'.

    Fernando

    Originally posted by rgarcia
    Hallo,

    I am trying to initiate a transaction in DB2. Tried with 'begin', 'begin transaction', begin tran', with no success. The command interpreter does not recognize them. I does recognize 'commit' and 'rollback'.

    Can anyone help?

    Thanks a lot in advance.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are using JDBC to perform UOW against DB2, make sure
    you disable the autocommit on the connection (it defaults to ON).

    A UOW would be:
    con.setAutoCommit(false);

    all work in here, insert, update, delete
    rollback/commit

    con.setAutoCommit(true);

    THis will work the way you want it.

    HTH

    Andy

    Originally posted by rgarcia
    Thank you Andy!!
    One more thing... so if I insert a row using its JDBC driver, can I afterwards perform a "ROLL BACK" if I get an exception???

    Thanks again!!!

    Cheers!!!!

  6. #6
    Join Date
    Sep 2011
    Posts
    5
    Quote Originally Posted by F.OHANA View Post
    Heys,

    All statements in DB2 initiates a transaction, thus you donīt need to state a 'begin transaction', but as any transaction, itīll only end when you call a 'commit' or 'rollback'. Take a look at the Transaction Isolation Level and choose which fits your needs best.
    Just be careful when using Repeatable Read isolation level, because itīll lock all objects untill a commit/rollback is called, even when the transaction is only a 'select' statement. But will give you security and consistency on the data youīre working with.

    But anyways, go through the Administration Guide to choose the Isolation Level for your system. Also look at the Command Reference for the command 'CHANGE ISOLATION LEVEL'.

    Fernando
    dear friend,

    if i want to execute a few of insert and delete statement in a sequence and using CHANGE ISOLATION LEVEL they don't work. Some error code says : The object is in an invalid state for the operation.

    can you help me to solve this problem? fyi, i want to make a sql transaction with COMMIT control in the end, so if i ended manually the transaction in the middle progress, all the changes in table MUST restore to the beginning state.

  7. #7
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by Bey View Post
    dear friend,

    if i want to execute a few of insert and delete statement in a sequence and using CHANGE ISOLATION LEVEL they don't work. Some error code says : The object is in an invalid state for the operation.

    can you help me to solve this problem? fyi, i want to make a sql transaction with COMMIT control in the end, so if i ended manually the transaction in the middle progress, all the changes in table MUST restore to the beginning state.
    Hey Bey,

    F.OHANA hasn't logged into this forum since 2008, so I doubt you'll get a reply from them. With that said AR Winner's reply looks like it might be relevant to your question if I understood it...

  8. #8
    Join Date
    Sep 2011
    Posts
    5
    Quote Originally Posted by meehange View Post
    Hey Bey,

    F.OHANA hasn't logged into this forum since 2008, so I doubt you'll get a reply from them. With that said AR Winner's reply looks like it might be relevant to your question if I understood it...
    oww sorry, i'm using as/400 in my case. What is the statement in COBOL to implement that method?

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should start a new thread instead of resurrecting one that is over 8.5 years old. You need to post what you have tried, and what error message you are getting as well as what you expect to happen.

    Andy

Posting Permissions

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