0

Im creating a DB2 stored procedure which goes like this

create or replace procedure test()
dynamic result sets 1
begin
     //declaration of variable goes here
     declare continue handler for sqlexception set errstate = sqlstate;
     savepoint save1 on rollback retain cursors;
     //some transaction
     if errstate <> '00000' then
        rollback to savepoint save1;
     end if
     //return errstate as result set
end@

yet, when the errstate is not '00000' for some reason (supposedly deadlock), the errstate that I got is 3B001 which stands for savepoint that i made is not exist or invalid

Ive tried to turn off autocommit, but still doesnt work, besides, in my understanding, my query abouve will treated as a single transaction so it doesnt necessarily turn autocommit off

its on DB2 ESE 10.5 running on CentOS 6.5

any suggestion?

-----note------

Ive managed to solve my problem by changing

 rollback to savepoint save1

to just 

rollback

this solve my problem, but still, I dont know why it refused to rollback to certain savepoint if I just use "rollback to savepoint save1" but it work when I use just "rollback"? can someone help explain it to me why is this happening?

Fuad Hanif
  • 33
  • 7
  • Platform and version of DB2? – Charles Jun 23 '15 at 18:18
  • db2 ESE 10.5 running on CentOS 6.5 – Fuad Hanif Jun 23 '15 at 18:31
  • If, as you say, your transaction receives SQLCODE -911, by that moment it is already rolled back in its entirety, so all savepoints are released. – mustaccio Jun 23 '15 at 19:24
  • I'm sorry, I think you misunderstand my question, due to my misleading question. what I'm asking is, why I cant rollback when I use "rollback to ...". when i use "rollback to ..." thats actually the only rollback statement in the SP, so i dont think that it has been rolled before. I have edited my question, sorry for my bad english. – Fuad Hanif Jun 23 '15 at 19:41
  • I think you misunderstand my comment. You suspect a deadlock occurring somewhere in your transaction. Deadlocks (and lock timeouts) are handled automatically by the DB2 database manager by rolling back the victim transaction and returning the SQLCODE -911 -- this is outside your control. A rollback removes all savepoints. Your stored procedure has a `CONTINUE` handler, which means it ignores the exception and proceeds. After it finishes and sees a non-zero SQLSTATE value it attempts a rollback to a savepoint, which was deleted by the implicit rollback earlier. – mustaccio Jun 24 '15 at 12:03
  • Ah, I see your point, but Im afraid thats not the case. in the transaction lets just say there were 2 transaction, the first one did success, and the second one cause the deadlock. if its rollbacked automatically due to the deadlock, then shouldn't be the first transaction rolled back? yet in this case im facing, the first transaction remain commited. beside, if the deadlock erased the savepoint, when I change the rollback command to just "rollback" instead of "rollback to" it works just fine. any idea? – Fuad Hanif Jun 27 '15 at 12:45

0 Answers0