DB2 SQL 脚本:如何启动事务并在出错时回滚 [英] DB2 SQL script: how to start a transaction and rollback on error

问题描述

我正在为 DB2 数据库 (V9.5) 实现 SQL 脚本，以便添加列和重新组织数据.我想从 linux 命令行执行脚本:

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

migration.sql 中的所有语句都应该包含在一个事务中.如果一个语句失败，则必须回滚所有先前的更改.我怎样才能做到这一点?我尝试使用 START TRANSACTION 但 DB2 在这种情况下返回语法错误.

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.

迁移.sql

    connect to ...        

    -- not accepted by DB2
    START TRANSACTION; 

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

    COMMIT;

    connect reset;
    terminate;

此外，我尝试按照此处的描述关闭自动提交:

• db2 +c -vstf migration.sql(在脚本中打开连接时不起作用)
• 将update command options using c off添加到migration.sql(错误时不回滚任何内容)

• 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)

那么，有没有人有想法让事务和回滚按预期在 db2 命令行上工作?如果不是，它是否适用于 Java/JDBC?

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?

推荐答案

从脚本中删除连接和提交，并在 shell 中执行此操作.然后使用 +c 并测试退出状态(假设是 bash，应该很容易移植到其他 shell):

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 返回:

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

-s 将停止执行退出代码 >= 4，测试检查是否发生这种情况并回滚事务.此外，您可能还想添加一个日志文件:

-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

如果出现错误，您可以拖尾日志文件以快速找出错误所在.如果您使用日志文件，您可能希望删除 -v，因为它有点嘈杂:

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

这篇关于DB2 SQL 脚本:如何启动事务并在出错时回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！