A transaction is commonly referred to in DB2 Database for Linux, UNIX, and Windows as a unit of work. A unit of work is a recoverable sequence of operations within an application process. It is used by the database manager to ensure that a database is in a consistent state. Any reading from or writing to the database is done within a unit of work.
For example, a bank transaction might involve the transfer of funds from a savings account to a checking account. After the application subtracts an amount from the savings account, the two accounts are inconsistent, and remain so until the amount is added to the checking account. When both steps are completed, a point of consistency is reached. The changes can be committed and made available to other applications.
A unit of work is started implicitly when the first SQL statement is issued against the database. All subsequent reads and writes by the same application are considered part of the same unit of work. The application must end the unit of work by issuing either a COMMIT or a ROLLBACK statement. The COMMIT statement makes permanent all changes made within a unit of work. The ROLLBACK statement removes these changes from the database. If the application ends normally without either of these statements being explicitly issued, the unit of work is automatically committed. If it ends abnormally in the middle of a unit of work, the unit of work is automatically rolled back. Once issued, a COMMIT or a ROLLBACK cannot be stopped. With some multi-threaded applications, or some operating systems (such as Windows), if the application ends normally without either of these statements being explicitly issued, the unit of work is automatically rolled back. It is recommended that your applications always explicitly commit or roll back complete units of work. If part of a unit of work does not complete successfully, the updates are rolled back, leaving the participating tables as they were before the transaction began. This ensures that requests are neither lost nor duplicated.
There is no physical representation of a unit of work because it is a series of instructions (SQL statements).
Use the SAVEPOINT statement to set a savepoint within a transaction.
This statement can be imbedded in an application program (including a stored procedure) or issued interactively. It is an executable statement that can be dynamically prepared.
>>-SAVEPOINT--savepoint-name--+--------+------------------------> '-UNIQUE-' .-ON ROLLBACK RETAIN LOCKS-. >--ON ROLLBACK RETAIN CURSORS--+--------------------------+----><
Once a SAVEPOINT statement has been issued, insert, update, or delete operations on nicknames are not allowed.
Omitting the UNIQUE clause specifies that savepoint-name can be reused within the savepoint level by another savepoint. If a savepoint of the same name already exists within the savepoint level, the existing savepoint is destroyed and a new savepoint with the same name is created at the current point in processing. The new savepoint is considered to be the last savepoint established by the application. Note that the destruction of a savepoint through the reuse of its name by another savepoint simply destroys that one savepoint and does not release any savepoints established after the destroyed savepoint. These subsequent savepoints can only be released by means of the RELEASE SAVEPOINT statement, which releases the named savepoint and all savepoints established after the named savepoint.
If the UNIQUE clause is specified, savepoint-name can only be reused after an existing savepoint with the same name has been released.
Within a savepoint, if a utility, SQL statement, or DB2 command performs intermittent commits during processing, the savepoint will be implicitly released.
If the SET INTEGRITY statement is rolled back within the savepoint, dynamically prepared statement names are still valid, although the statement might be implicitly prepared again.
If inserts are buffered (that is, the application was precompiled with the INSERT BUF option), the buffer will be flushed when SAVEPOINT, ROLLBACK, or RELEASE TO SAVEPOINT statements are issued.
Example: Perform a rollback operation for nested savepoints. First, create a table named DEPARTMENT. Insert a row before starting SAVEPOINT1; insert another row and start SAVEPOINT2; then, insert a third row and start SAVEPOINT3:
CREATE TABLE DEPARTMENT ( DEPTNO CHAR(6), DEPTNAME VARCHAR(20), MGRNO INTEGER ) INSERT INTO DEPARTMENT VALUES ('A20', 'MARKETING', 301) SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS INSERT INTO DEPARTMENT VALUES ('B30', 'FINANCE', 520) SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS INSERT INTO DEPARTMENT VALUES ('C40', 'IT SUPPORT', 430) SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS INSERT INTO DEPARTMENT VALUES ('R50', 'RESEARCH', 150)
At this point, the DEPARTMENT table exists with rows A20, B30, C40, and R50. If you now issue:
ROLLBACK TO SAVEPOINT SAVEPOINT3row R50 is no longer in the DEPARTMENT table. If you then issue:
ROLLBACK TO SAVEPOINT SAVEPOINT1the DEPARTMENT table still exists, but the rows inserted since SAVEPOINT1 was established (B30 and C40) are no longer in the table.