Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Question Unanswered: Rollback problem....

    I have 3 tables say A, B, C. Out of which the Table C is LOCKED by some user.
    I use some form to insert records in Table A & B and update C.
    The Sql statements are in sequence.
    It inserts rows in Table A & B.
    But since Table C is LOCKED when I try to update it raises an error, so I rollback the transaction.

    BUT STILL THE ROWS Gets committed in Table A & B. I DON'T want this.
    Could anyone provide solution.


    Thanks in Advance.
    -Milind

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure which mechinism you are using to submit the statements. Make sure that auto-commit is not turned on.

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    Originally posted by Marcus_A
    I am not sure which mechinism you are using to submit the statements. Make sure that auto-commit is not turned on.
    I'm using Centura 2000 GUI tool to do inserts and updates and this tool Centura gets connected to DB2 Database through DB2 Connect, with isolation level as "RL".

    -regards
    Milind

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The issue is auto-commit (commits after every SQL statement), not isolation level. Check with Centura 2000 to see what they say about auto-commit.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    Originally posted by Marcus_A
    The issue is auto-commit (commits after every SQL statement), not isolation level. Check with Centura 2000 to see what they say about auto-commit.
    Thanks for the info., but in our application "AUTOCOMMIT" is OFF. Whether in DB2 Connect are there any setting related to AUTOCOMMIT (while insallaion etc.)

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check out "Compound SQL (Dynamic)" in the SQL Reference Volume 2. This allows you to submit multiple dynamic SQL statements that will be rolled back if they don't all complete successfully.

    Also, for the timeout problem, I would make sure that the locktimeout database parameter is not set to zero (maybe set to 2-5 seconds).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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