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?

Solution

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

Comments

[Lennart - Slava Ukraini]

Strictly speaking you could keep the connect and the commit in the script, but I think it is nice to start transaction, commit and rollback at the same level.

[Ian Bjorhovde]

Be careful: Certain DB2 utilities (like REORG) will necessarily commit your unit of work. Since certain alter table statements will require you to run reorg, you have to consider if you are going to try to complete a migration in a single unit of work.

[Lennart - Slava Ukraini]

That's certainly true. In the long run it is therefor good to create a framework for upgrade of databases. I have one where certain checks are performed before the version is committed (invalidated objects for example). On error an exception is signalled. The database keeps track of it's own version so the utility can skip over any committed versions. Before anything that is not transaction safe is executed, the utility tries to commit that version first.