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.
