What is the equivalent to SQL Server Transactions in DB2?

Question

What is the equivalent to the following SQL Server statements in DB2?

• Begin Transaction
• Commit Transaction
• Rollback Transaction

Answer 1

The answer is actually a little more complicated than indicated here. True, transactions are ANSI standardized, and DB2 may support them.

DB2 for z/OS can be a very different beast from the other variants (LUW, Linux-Unix-Windows, being the most common). At risk of sliding into a rant, this makes the idea of talking about DB2 almost pointless. You are talking about some specific variant of IBM's database, but what works in one can be completely invalid in another. I will assume that whatever flavor the OP was using was not the z/OS one, since the BEGIN TRANSACTION answer was accepted.

For those of you who stumble across this trying to use transactions with DB2 z/OS, here is the rundown: DB2 for the mainframe does not have explicit transactions. There is no BEGIN TRANSACTION or any other comparable construct. Transactions are begun implicitly (usually referred to as a unit of work in the docs) and committed or rolled back explicitly (usually--many GUI tools, like Toad, have an autocommit feature that can sneak up on you once in a while).

From the 9.1 z/OS SQL reference manual (page 28; available at http://www-01.ibm.com/support/docview.wss?uid=swg27011656#manuals):

"A unit of work is initiated when an application process is initiated. A unit of work is also initiated when the previous unit of work is ended by something other than the end of the application process. A unit of work is ended by a commit operation, a full rollback operation, or the end of an application process. A commit or rollback operation affects only the database changes made within the unit of work it ends."

The closest thing you get when writing scripts is to manually specify a savepoint.

These look like this:

 SAVEPOINT A ON ROLLBACK RETAIN CURSORS;

 UPDATE MYTABLE SET MYCOL = 'VAL' WHERE 1;

 ROLLBACK WORK TO SAVEPOINT A;

Superficially, these resemble explicit transactions, but they are not. Instead, they really are just points in time within a single implicit transaction. For many purposes, they may suffice, but it is important to be aware of the conceptual differences.

Answer 2

See here for more info. But basically

BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK