Skip to content

Tag Archives: transaction

DB2 LUW CLP – how to rollback transaction using +c -s -f options


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.