How perform a transaction in ibm db2

I’m working with an accounts table

I tried to run this sql which transfer funds from one account to another.

savepoint first_savepoint on rollback retain cursors;
update accounts set accbalance = accbalance -50
where accnumber = 'C-009';
update accounts set accbalance = accbalance +50
where accnumber = 'C-009';
rollback to savepoint first_savepoint;

I got this error

savepoint “FIRST_SAVEPOINT” does not exist or is invalid in this context.. SQLCODE=8080, SQLSTATE=3B001, DRIVER=3.69.56

What could i do ? I’m using IBM Data Studio 4.1.2 and IBM DB2 Express-C

Thanks for all the help

Answer

In Db2-LUW, all statements execute in a transaction, automatically.

The transaction begins on the first statement after a previous transaction ends, or after a connect. The transaction ends when there is a COMMIT, or a ROLLBACK..

But any COMMIT can either be explicit in your code, or implicit made by the tool that submits the SQL (in this case IBM Data Studio) by autocommit after each line. Usually autocommit is the default behaviour for such tools, but is configurable.

You can configure IBM Data Studio (and any other similar tools) to disable autocommit, which then forces you to explicitly use COMMIT or ROLLBACK to end a transaction.

When you want multiple statements to execute atomically then you have these options:

• you can disable autocommit, run both update statements and explicitly COMMIT

• or, regardless of autocommit setting, you can use an anonymous block, if you configure your SQL submitting tool (ie. Data Studio) to use an alternative delimiter to indicate the end of the block (for example, use @ as the block delimiter), and then you use SQL like this:

`

begin atomic
   update accounts set accbalance = accbalance -50
   where accnumber = 'C-009';
   update accounts set accbalance = accbalance +50
   where accnumber = 'C-009';
   commit;
end@

`

If you do not want to use anonymous blocks, then your code can (with autocommit DISABLED) read:

commit;  -- if you want to end any previous transaction
update accounts set accbalance = accbalance -50
where accnumber = 'C-009';
update accounts set accbalance = accbalance +50
where accnumber = 'C-009';
commit;

Notice that you do not need to use explicit SAVEPOINTS as shown in your question. You only need to use an explicit SAVEPOINT if and only if you want to break down a transaction into different groups of statements , with each group being individually undoable within the constraints defined by Db2.