db2savepoints

savepoint is not recognized in db2

I have following query - 

SAVEPOINT A ON ROLLBACK RETAIN CURSORS;

select max(id) from testdb.table1;

ROLLBACK TO SAVEPOINT A;

commit

I am getting following error

ROLLBACK TO SAVEPOINT A
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0880N  SAVEPOINT "A" does not exist or is invalid in this context.  
SQLSTATE=3B001

I am new to DB2, I didnt get any start transaction type statement, however commit is present.

How to resolve this error?

Resolution

I got following - 

update command options using c OFF; 

SAVEPOINT A ON ROLLBACK RETAIN CURSORS;

select max(id) from testdb.table1;

ROLLBACK TO SAVEPOINT A;

commit;

update command options using c ON ;

It is working fine in my context.

Solution 
  • update command options using c OFF; 

SAVEPOINT A ON ROLLBACK RETAIN CURSORS;

select max(id) from testdb.table1;

ROLLBACK TO SAVEPOINT A;

commit;

update command options using c ON ;