Objective: You have a set of SQL DML (or other, such as DDL) statements that you want to run as a file using the CLP db2 -f filename option. If any of the statements in the file fail, you want processing of the -f file to end, every statement executed successfully (but not yet committed) prior to the fail to be rolled back. How do you do this?
The short answer is to avoid any connection state changes in the -f file (and obviously any explicit COMMIT’s). Anything that closes the connection will commit all changes up to that point, regardless of what auto-commit behavior you have designated for the session. If you remain connected at the end of processing a file using the +c -s -t -f options, you can explicitly ROLLBACK the transactions that succeeded before the one that failed. Ascertain that your CLP -f file has no QUIT, CONNECT RESET or TERMINATE statement. Read below for more detail.
[Thanks to Ian Bjorhovde, Chad Walmer, and other members of DB2-L for their help with this article.]
For illustration, I have created a simple table:
create table jbenner.TEST ( TESTCOL CHAR(1) )
Now I want to run the following set of statements:
insert into jbenner.TEST values('A');
insert into jbenner.TEST values('B');
-- intentional error here - value exceeds column length
insert into jbenner.TEST values('CC');
insert into jbenner.TEST values('D');
I want all executed statements in the file to be rolled back if any statement fails. We know here the third insert, of attempted value ‘CC’, will fail due to column length violation. We want all SQL run before it to be rolled back when this happens. In this example, then, we want the inserts of ‘A’ and ‘B’, which will normally be successful, to be rolled back.
First save the above lines into a file, call it filename.txt.
Then connect to your database (I am assuming for the sake of the example that this and following steps are made from a Unix shell session and as a foreground task):
$ db2 connect to MYDB
Database Connection Information
Database server = DB2/SUN64 9.1.0
SQL authorization ID = DB2
Local database alias = MYDB
Now run your script with the following parameters:
$ db2 +c -vstf filename.txt
What the above CLP options will do ( See Info Center for full options reference) :
- +c Turn AUTO-COMMIT OFF. The default CLP behavior is to commit each line individually then proceed on with next line. Explicit ROLLBACK and COMMIT in the file will do nothing under default CLP behavior. If you want to control transaction commits and rollbacks, you need to specify the +c option.
- -v Verbose output.
- -s Discontinue processing on an error.
- -t Recognize line termination character ‘;’.
- -f Process all statements in following filename.
Having executed this command you will see the following output:
insert into jbenner.TEST values('A') DB20000I The SQL command completed successfully. insert into jbenner.TEST values('B') DB20000I The SQL command completed successfully. insert into jbenner.TEST values('CC') DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0433N Value "CC" is too long. SQLSTATE=22001
So far so good. The CLP has stopped processing on the third statement with the excessively long insert value. But you’re not out of the woods yet. The CLP has ended and returned you to the Unix shell prompt. You must now, to rollback the previous statements that were successfully executed (but not yet committed) issue an explicit ROLLBACK:
$ db2 ROLLBACK
DB20000I The SQL command completed successfully.
Now if you check your table, you will see that the INSERT’s were rolled back:
$ db2 "select * from jbenner.test" TESTCOL ------- 0 record(s) selected.
But if you had ended the -f file with a QUIT, CONNECT RESET or TERMINATE, you will find that no matter what you do, the first 2 inserts will show up in your table, not having been rolled back. Any connection reset causes an implicit COMMIT regardless of your other settings and options.
Remember: once you have turned AUTO-COMMIT off, and removed QUIT, CONNECT RESET and TERMINATE statements, you are in the dangerous position of holding open a transaction for a very long time (possibly hours or days; I have seen it happen, especially when non-DBA users have access to ad hoc SQL tools). This could cause locking contention in your database, so be careful to immediately end your CLP transaction with a COMMIT, ROLLBACK, QUIT, CONNECT RESET or TERMINATE.
In the above example, you issued a ROLLBACK manually upon inspection of the results of the CLP execution. This could also be done automatically in a Unix Korn shell or other script, like this:
db2 connect to mydb
db2 +c -vstf filename.txt
if [ $? > 4 ]; then
In conclusion, if you want to trigger a rollback of all preceding changes in the event of any statement error, you want to avoid any statement in the -f file which may change connection state ( QUIT/CONNECT RESET/TERMINATE ). (You also obviously want to make sure there are no explicit COMMIT statements in the -f file.) If you do not care about explicit commit and rollback handling within a -f file, though, it continues to be a good idea to end your -f file with a CONNECT RESET and TERMINATE.