Ends a transaction and undoes all changes you made since that
transaction began. The ROLLBACK statement also:
o Closes all open cursors (with the exception of WITH HOLD
cursors)
o Releases all row locks
o Performs a checkpoint operation if fast commit processing is
enabled
The ROLLBACK statement affects:
o All open databases included in the current transaction
o All changes to data made with SQL data manipulation statements
(DELETE, UPDATE, and INSERT)
o All changes to data definitions made with SQL data definition
statements (ALTER, CREATE, DROP, RENAME, GRANT, and REVOKE)
1 – Environment
You can use the ROLLBACK statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1mRollback-statement = [m
[1;4mROLLBACK[m[1m WORK qqqqwqqqqqqqqqqqqqqqwqqq>[m
[1m [m [1mmq> [1;4mAND[m [1;4mCHAIN[m[1m qqj[m
3 – Arguments
3.1 – AND_CHAIN
Starts a new transaction implicitly using the same attributes as
the rolled back transaction.
3.2 – WORK
Specifies an optional keyword that has no effect on the ROLLBACK
statement. It is provided for compatibility with the ANSI/ISO SQL
standard.
4 – Examples
Example 1: Rolling back changes in a COBOL program
GET-ID-NUMBER.
DISPLAY "Enter employee ID number: "
WITH NO ADVANCING.
ACCEPT EMPLOYEE-ID.
CHANGE-SALARY.
DISPLAY "Enter new salary amount: "
WITH NO ADVANCING.
ACCEPT SALARY-AMOUNT.
EXEC SQL UPDATE SALARY_HISTORY
SET SALARY_AMOUNT = :SALARY-AMOUNT
WHERE EMPLOYEE_ID = :EMPLOYEE-ID
AND END_DATE IS NULL
END-EXEC
DISPLAY EMPLOYEE-ID, SALARY-AMOUNT.
DISPLAY "Is this figure correct? [Y or N] "
WITH NO ADVANCING.
ACCEPT ANSWER.
IF ANSWER = "Y" THEN
EXEC SQL COMMIT END-EXEC
ELSE
EXEC SQL ROLLBACK END-EXEC
DISPLAY "Please enter the new salary amount again."
GO TO CHANGE-SALARY
END-IF.
Example 2: Using COMMIT and AND CHAIN
The following simple example executes SET TRANSACTION once at
the start of the procedure. Then periodically the transaction is
committed and restarted using the COMMIT AND CHAIN syntax. This
simplifies the application since there is only one definition of
the transaction characteristics.
SQL> -- process table in batches
SQL>
SQL> set compound transactions 'internal';
SQL> set flags 'transaction,trace';
SQL>
SQL> begin
cont> declare :counter integer = 0;
cont> declare :emp integer;
cont>
cont> set transaction
cont> read write
cont> reserving employees for exclusive write;
cont>
cont> for :emp in 0 to 600
cont> do
cont> begin
cont> declare :id char(5)
cont> default substring (cast (:emp+100000 as varchar(6))
cont> from 2 for 5);
cont> if exists (select * from employees where employee_id = :id)
cont> then
cont> trace 'found: ', :id;
cont> if :counter > 20
cont> then
cont> commit and chain;
cont> set :counter = 1;
cont> else
cont> set :counter = :counter + 1;
cont> end if;
cont> end if;
cont> end;
cont> end for;
cont>
cont> commit;
cont> end;
~T Compile transaction (1) on db: 1
~T Transaction Parameter Block: (len=2)
0000 (00000) TPB$K_VERSION = 1
0001 (00001) TPB$K_WRITE (read write)
~T Start_transaction (1) on db: 1, db count=1
~T Rollback_transaction on db: 1
~T Compile transaction (3) on db: 1
~T Transaction Parameter Block: (len=14)
0000 (00000) TPB$K_VERSION = 1
0001 (00001) TPB$K_WRITE (read write)
0002 (00002) TPB$K_LOCK_WRITE (reserving) "EMPLOYEES" TPB$K_EXCLUSIVE
~T Start_transaction (3) on db: 1, db count=1
~Xt: found: 00164
.
.
.
~Xt: found: 00184
~Xt: found: 00185
~T Commit_transaction on db: 1
~T Prepare_transaction on db: 1
~T Restart_transaction (3) on db: 1, db count=1
~Xt: found: 00186
.
.
.
~Xt: found: 00205
~Xt: found: 00206
~T Commit_transaction on db: 1
~T Prepare_transaction on db: 1
~T Restart_transaction (3) on db: 1, db count=1
~Xt: found: 00207
.
.
.
~Xt: found: 00228
~Xt: found: 00229
~T Commit_transaction on db: 1
~T Prepare_transaction on db: 1
~T Restart_transaction (3) on db: 1, db count=1
~Xt: found: 00230
.
.
.
~Xt: found: 00249
~Xt: found: 00267
~T Commit_transaction on db: 1
~T Prepare_transaction on db: 1
~T Restart_transaction (3) on db: 1, db count=1
~Xt: found: 00276
.
.
.
~Xt: found: 00435
~Xt: found: 00471
~T Commit_transaction on db: 1
~T Prepare_transaction on db: 1
SQL>