回复 23# zhenda 但是有一个地方解释不通还想跟您探讨一下比如b2inst1@testing1:~> db2 +c "insert into t values(1)"DB20000I The SQL command completed successfully.db2inst1@testing1:~> db2 +c "insert into t values('d')"DB21034E The co...
显示全部回复 23# zhenda 但是有一个地方解释不通还想跟您探讨一下
比如
b2inst1@testing1:~> db2 +c "insert into t values(1)"
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 +c "insert into t values('d')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018
db2inst1@testing1:~> db2 +c "insert into t values('1')"
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 +c "select * from t"
I
-----------
1
1
2 record(s) selected.
db2inst1@testing1:~> db2 commit
DB20000I The SQL command completed successfully.
db2inst1@testing1:~> db2 "select * from t"
I
-----------
1
1
2 record(s) selected.
db2inst1@testing1:~>
但是如果用procedure写的话
CREATE or replace PROCEDURE test
LANGUAGE SQL
snap:BEGIN not ATOMIC
insert into t values(1);
commit;
insert into t values('d');
commit;
insert into t values(1);
commit;
END snap @
--------------------------------------------
db2inst1@testing1:~> db2 "call test()"
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018
db2inst1@testing1:~> db2 "select * from t"
I
-----------
1
1 record(s) selected.
db2inst1@testing1:~>
procedure里面出错之后的语句都没有再执行,这个是正常的。
但是db2 +c 等于是不管是出错前的还是出错后的语句,只要是正常的都执行了。并且提交的时候都提交上去了。
我觉得这样应该解释得通
就是你在trancation中提交的语句如果有一句出错应该会整体回滚。
但是你用CLP提交的时候如果碰到了出错的语句应该是不会加入到事物中的,
比如你执行
db2 +c “insert into t values(1)”;
db2 +c “insert into t values(d)“;
db2 +c “insert into t values(1)”;
实际上db2的这个事物里面只执行了
db2 +c “insert into t values(1)”;
db2 +c “insert into t values(1)”;
中间那条已经报错返回了根本就没有给你加到事物中。
而你单独写一个trancation就不一样了
他会把三句都算到这个事物中,所以才会出现有一句错误就会整体回滚,或者对于not ATOMIC会把出错之前的都执行。
所以关于clp对于事物的定义是绝对符合的,只不过用clp执行的时候会把所有不成功的语句都过滤掉了而已。
通过写JAVA也能模拟出这种情况。
收起