是什么让DB2存储过程自动提交了DML操作

[yxsalj]

我用的DB2 ESE V9.7, Run 'db2ce', 运行以下代码：

connect to sample @
create table s1.t1( id int)@
create procedure s1.p1
begin
insert into s1.t1 values(1);
end @
call s1.p1 @
select id from s1.t1@

结果表中已经生成记录了。我已经将db2 command options 中的auto-commit 设置为off了，是用db2set DB2OPTIONS=+c 的；考虑到工具的配置项可能会有影响，已将命令窗口中的自动提交取消，但还是会生成记录到表中，这是怎么回事？ 请帮忙，谢谢。

[ACMAIN_CHM]

call s1.p1 @
select id from s1.t1@

你这是在同一个SESSION中，当然可以看到自己insert的记录。 你应该打开另一个SESSION来检查以确认事务是否提交。


建议参考一下事务的定义。

[yxsalj]

呵呵，在别的session看到的也是一样

[xffangbin]

不要写在存储过程中试试呢。。。

[yxsalj]

谢谢，看起来存储过程的AUTOCOMMIT受调用它的环境的影响，DB2CMD,DB2CE,SQLJ，PHP等不同环境都会有默认设置的AUTOCOMMIT，而且看起来彼此不覆盖

[wwwwb]

DB2中没有显式的事务开始语句，上一个事务的结束（Commit或Rollback）就是新事务的开始。

在命令行处理器中，默认的方式是自动提交（Auto Commit），所以执行一个语句之后就会隐含的自动执行Commit。如果想由程序员自行控制提交时机，可以把该参数设置为False，方法是：
update command option using c off

同一个事务中，即使没有提交插入，也是可以查看到数据的。如果不是同一个事务，那么插入操作提交以后才可以查询到。

[yxsalj]

LS请仔细看我问题：）

[zhaojianmi1]

你用db2clp试试

[Mr_Bean]

多个作用域之间的一个很有趣的问题 ，值得关注一下

[wwwwb]

找到原因了？

[yxsalj]

It seems that DML cant be auto-committed in DB2 procedures even no 'commit' issued; from current tests, it properly
be affected by diffrents caller environments such as CLP,Command Editor,SQLJ,PHP etc. For CLP and CE, they are in-
depend from their own setting options. See below labs:

Firstly, create relevent table and procedure in 'sample' database as follow via 'db2ce':

create table s1.t1( id int)@
create procedure s1.p1
begin
insert into s1.t1 values(1);
end @

1. BehaviourS of auto-commit ON in CLP

1.1> Setting auto-commit ON in CLP, result as:

1.2> Query "s1.t1" table, no row existed, result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

0 record(s) selected.


1.3> Call "s1.p1" procedure and query table again, result as:

C:\Documents and Settings\Administrator>db2 call s1.p1

Return Status = 0

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

1 record(s) selected.


1.4> Run another 'db2cmd', found 1 row inserted into table, same as step '1.3'.



2. BehaviourS of COMMIT via Command Editor calling procedure when CLP auto-commit is ON

2.1> Run 'db2ce', click off 'Tools-> Tools Settings-> Command Editor -> Automatically commit SQL statements'.

2.2> Issue command of calling procedure and query table, results as:

------------------------------ Commands Entered ------------------------------
call s1.p1;
select * from s1.t1;
------------------------------------------------------------------------------
call s1.p1

Return Status = 0

select * from s1.t1

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

2 record(s) selected.

2.3> Switch to 'db2cmd', issue command 'db2 select * from s1.t1', no result returns, SQL command is waiting,
result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

- (flashing cursor)

2.4> Switch back to 'db2ce', issue 'commit;', execution successfully, result as:

------------------------------ Commands Entered ------------------------------
commit;
------------------------------------------------------------------------------
commit
DB20000I The SQL command completed successfully.

2.5> Switch to 'db2cmd', SQL finished successfully, the second row is inserted, result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

2 record(s) selected.


2.6> Click on 'Tools-> Tools Settings-> Command Editor -> Automatically commit SQL statements' and call pro-
cedure again 'call s1.p1;' in CE, then switch to 'db2ce', issuce command 'db2 select * from s1.t1', can
see SQL command executed successfully with no waitting, result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

3 record(s) selected.


3. Behaviours when auto-commit OFF in CLP.

3.1> Setting auto-commit ON in CLP, result as:
3.2> Query current table rows, result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

3 record(s) selected.


3.3> Call procedure in CLP, then query the table in current session, result as:

C:\Documents and Settings\Administrator>db2 call s1.p1

Return Status = 0

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

4 record(s) selected.


3.4> Run another 'db2cmd', issue command 'db2 select * from s1.t1', SQL command excecute waitting,
no any return; switch back to the first 'db2cmd' and issue command 'db2 rollback', executed
successfully and the second 'db2mcd' SQL finished successfully with rows return, resutl as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

3 record(s) selected.


4. BehaviourS of COMMIT via Command Editor calling procedure when CLP auto-commit is OFF

4.1> Run 'db2ce', click on 'Tools-> Tools Settings-> Command Editor -> Automatically commit SQL statements'.

4.2> In 'db2ce', call procedure, executed successfully, resutl as:

------------------------------ Commands Entered ------------------------------
call s1.p1;
------------------------------------------------------------------------------
call s1.p1

Return Status = 0

4.3> Switch to 'db2cmd', query table rows, executed successfully without waiting, result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

4 record(s) selected.

4.4> click off 'Tools-> Tools Settings-> Command Editor -> Automatically commit SQL statements', and call
procedure again, then switch to 'db2cmd' and query table rows, it waits, result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1


4.5> Issue command 'commit' in 'db2ce' window, it executed successfully; switch to 'db2cmd', the waitting
SQL finished successfully, result as:

C:\Documents and Settings\Administrator>db2 select * from s1.t1

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

5 record(s) selected.

[davidyu720]

我在DB2 9.5下用CLP测试了，不存在你说的问题。update command options using c off后，调用这个存储过程并不会导致事务提交DML.

[davidyu720]

[Quote=引用 12 楼 davidyu720 的回复:]
我在DB2 9.5下用CLP测试了，不存在你说的问题。update command options using c off后，调用这个存储过程并不会导致事务提交DML.
[/Quote]

在另一个CLP中select表，会处于锁定等待状态，直到前面的CLP commit，

[davidyu720]

:( 刚才没看完。一会用db2ce执行看看