DB2 savepoint in stored procedure is invalid

470 views Asked by At

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?

0

There are 0 answers