Go语言视频零基础入门到精通

apekji

我用的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 s12.p1 @
select id from s1.t1@

结果表中已经生成记录了。我已经将db2 command options 中的auto-commit 设置weioff了，是用db2set DB2OPTIONS=+c 的；考虑到工具De配置項ke能会有影响，已将命令窗口zhong的自動提交qu消，但还是會生成记录到表中，這是怎么回事？ 请帮忙，谢谢。
欢迎来到Java学习者论壇,转载請注明地址:http://www.javaxxz.com.

yy1020

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

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


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

jjuiwe

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

叁点水

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

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

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

zhouj1990

LS请仔細看我問题：）

mone12

你用db2clp试试

fangfang1

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

时间旅人

找到原因了？

apekji

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.

okwoie

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