I run the following script in DB2 V8.1 under Linux.

CREATE TABLE tmp(
<table definition>
);
savepoint SPTMP on rollback retain cursors;
insert into tmp (select * from orders where o_id<10000);
select count(*) from tmp;
rollback to savepoint SPTMP;
select count(*) from tmp;

But get the following errors:

rollback to savepoint SPTMP
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 "SPTMP" does not exist or is invalid in this context.
SQLSTATE=3B001

Apparently I have the savepoint defined. What have I done wrong?

Do you turn off autocommit when you run this script?

Thanks for your quick reply. But if I don't want to turn off autocommit, I just want to run the following piece of code in a transaction. What should I do?

savepoint SPTMP on rollback retain cursors;
insert into tmp (select * from orders where o_id<10000);
select count(*) from tmp;
rollback to savepoint SPTMP;

I tried wrapping those statements with BEGIN ATOMIC ... END but it doesnt work.

What you're saying essentially is "I want a transaction but I don't want a transaction." A COMMIT ends the current transaction. If you have autocommit turned on it issues a COMMIT after each statement. So, the answer is: if you want to run several statements in a single transaction, turn off autocommit.

Maybe I migrated the wrong idea from oracle to DB2. In oracle I can have a bunch of statements executed as a single transaction if they are wrapped with BEGIN ... END clause, but the autocommit flag is still ON. That is I can have some statement autocommited but other running as a transaction.

--the following statements will be auto committed
<some statements>
--statements inside BEGIN..END run as a transaction
BEGIN
<statements>
END
I thought I can do that as well in DB2.