DB2 SQL script: how to start a transaction and rollback on error

Question

I am implementing a SQL script for a DB2 database (V9.5) in order to add columns and reorganize data. I want to execute the script from the linux commandline:

$ db2 -vstf migration.sql

All statements in migration.sql are supposed to be wrapped in a transaction. If one statement fails all previous changes must be rolled back. How can I do that? I tried to use START TRANSACTION but DB2 returns a syntax error in this case.

migration.sql

    connect to ...        

    -- not accepted by DB2
    START TRANSACTION; 

    update ... set ... 
    alter table ... 

    COMMIT;

    connect reset;
    terminate;

Additionally I tried to turn off the autocommit as described here:

• db2 +c -vstf migration.sql (does not work when the connection is opened in the script)
• add update command options using c off to migration.sql (does not rollback anything on error)

So, does anybody has an idea to get the transaction and rollback working on the db2 commandline as expected? If not, does it work with Java/JDBC?

Answer 1

Remove the connect and commit from the script and do that in the shell. Then use +c and test the exit status (assuming bash, should be easy to port to other shells):

db2 connect to <db>

db2 +c -vstf migration.sql
if [ $? -ge 4 ]; then
    db2 rollback
else
    db2 commit
fi

db2 returns:

* 8 on system error
* 4 db2 error (constraint violation, object not found etc)
* 2 db2 warning 
* 1 no rows found

-s will stop the execution for exit codes >= 4, the test checks whether this happened and rollbacks the transaction. In addition you might want to add a logfile:

db2 -l migration.log +c -vstf migration.sql
if [ $? -ge 4 ]; then
    db2 rollback
    tail -10 migration.log
else
    db2 commit
fi

in case of error you can tail the logfile to quickly find out what the error was. If you use a logfile you probably would like to remove -v since it is kind of noisy:

db2 -l migration.log +c -stf migration.sql
if [ $? -ge 4 ]; then
    db2 rollback
    tail -10 migration.log
else
    db2 commit
fi