Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Store procedure -- Error/rollback

    how can i rollback transactions in stored procedure if of the query returns error.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you don't do a commit in the SP, just issue rollback.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2008
    Posts
    48
    i have a procedure like this :


    Insert into table1 .....

    update table2 ....

    Insert into table3 .....

    update table4 ....

    for some reason Insert into table3 ..... failed (run time error)

    when i looked, data was inserted in table1 and updated in table2

    i thought if procedure throws run time error, all transcations will be rolled back automatically. I dont not have commit statement in the code.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No, your assumption is not correct. Why should the procedure roll back everything? If you want to have this behavior, set a savepoint at the beginning of the procedure and in case an error occurred, roll back to that savepoint.

    Stored procedures are not different in any respect from transaction management. Each procedure call runs in the transactional scope defined by the caller. So whenever the caller commits or rolls back, that will determine what happens with the changes made inside the procedure. (It doesn't matter whether you have auto-commit turned on.) Furthermore, a stored procedure being basically just a container for a sequence of SQL statements can also execute a COMMIT or ROLLBACK. That has an impact on the caller's transaction. There is no autonomous transaction or so...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by phil72
    i have a procedure like this :


    Insert into table1 .....

    update table2 ....

    Insert into table3 .....

    update table4 ....

    for some reason Insert into table3 ..... failed (run time error)

    when i looked, data was inserted in table1 and updated in table2

    i thought if procedure throws run time error, all transcations will be rolled back automatically. I dont not have commit statement in the code.
    You can put a rollback statement in a condition handler if that is what you want to happen if a negative return code is encountered. Make sure you don't rollback for warnings like +100.
    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
  •