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?