事务相关问题

再来点料, V91和V95/V97在CLP上call procedure的不同的行为模式.


DB21085I  Instance "e91q3f" uses "64" bits and DB2 code release "SQL09014" with
level identifier "01050107".
Informational tokens are "DB2 v9.1.0.4", "s071028", "U811792", and Fix Pack
"4".
Product is installed at "/opt/IBM/db2/v9.1.3".


[db2a /home/e91q3f]$ db2 "delete from t1"
DB20000I  The SQL command completed successfully.
[db2a /home/e91q3f]$ db2 "select * from t1"

ID         
-----------

  0 record(s) selected.

[db2a /home/e91q3f]$ db2 "alter table t1 add constraint id_check check(id<=1000)"
DB20000I  The SQL command completed successfully.

[db2a /home/e91q3f]$ db2 "create procedure proc_test begin insert into t1 values(1);insert into t1 values(1001);end"
DB20000I  The SQL command completed successfully.

[db2a /home/e91q3f]$ db2 "call proc_test"
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "E91Q3F.T1.ID_CHECK".  SQLSTATE=23513

[db2a /home/e91q3f]$ db2 "select * from t1"

ID         
-----------
          1

  1 record(s) selected.



DB21085I  Instance "w95q7a" uses "64" bits and DB2 code release "SQL09057" with
level identifier "06080107".
Informational tokens are "DB2 v9.5.0.7", "s101129", "IP23136", and Fix Pack
"7".
Product is installed at "/opt/IBM/db2/V9.5.7".


[db2a /home/db2users/w95q7a]$ db2 "select * from t1"

ID         
-----------

  0 record(s) selected.

[db2a /home/db2users/w95q7a]$ db2 "alter table t1 add constraint id_check check(id<=1000)"
DB20000I  The SQL command completed successfully.

[db2a /home/db2users/w95q7a]$ db2 "create procedure proc_test begin insert into t1 values(1);insert into t1 values(1001);end"
DB20000I  The SQL command completed successfully.

[db2a /home/db2users/w95q7a]$ db2 "call proc_test"
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "W95Q7A.T1.IDCHECK".  SQLSTATE=23513

[db2a /home/db2users/w95q7a]$ db2 "select * from t1"

ID         
-----------

  0 record(s) selected.


[db2a /home/db2users/w95q7a]$ db2 +c "call proc_test"
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "W95Q7A.T1.IDCHECK".  SQLSTATE=23513

[db2a /home/db2users/w95q7a]$ db2 "select * from t1"

ID         
-----------
          1

  1 record(s) selected.