CODEWITHSUNDEEP
sqltransactionsdb2rollback
seb.wired
seb.wired

Reputation: 486

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

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:

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?

Upvotes: 3

Views: 19449

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

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

Upvotes: 5

Andrew
Andrew

Reputation: 8703

To disable auto-commit for your command line:

db2 => UPDATE COMMAND OPTIONS USING c OFF

To see your current settings:

db2 => LIST COMMAND OPTIONS

Upvotes: 0

Related Questions