正大建设集团股份有限公司网站最佳磁力吧ciliba磁力链
db2执行文件参数：
-t 表示语句使用默认的语句终结符——分号；
-v 表示使用冗长模式，这样 DB2 会显示每一条正在执行命令的信息；
-f 表示其后就是脚本文件；
-z表示其后的信息记录文件用于记录屏幕的输出，方便以后的分析(这是可选的，但我们建议使用该选项)。
当使用了-t选项而没有标明语句终结符，则分号(；)会默认为语句的终结符。有时可能会出现使用另外的终结符的情况，例如用SQL PL 编写的的脚本使用其它的符号而不是默认的分号，因为分号在SQL PL 是用于定义数据库对象过程中的语句结束。
-d --end的简称，最后一个结束符
存储过程:
;作为DB2默认的SQL命令结束符，即你执行的不是一个创建存储过程的语句，而是多条不完整的SQL语句。
语句中最后一个;换成其它符号，如@，然后使用db2 -td@ -vf insert_log_test.sql(txt、sql都可以) 指定@为命令结束符。
一个简单的存储过程:
vi insert_log_test.sql
CREATE OR REPLACE PROCEDURE INSERT_LOG_TEST()
begin
atomic declare i int default 0;
while(i <10000)
do insert into log_test values (i,'中间提交的事务');
set i=i+1;
end while;
end
@
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 -td@ -vf insert_log_test.sql
CREATE OR REPLACE PROCEDURE INSERT_LOG_TEST()
begin
atomic declare i int default 0;
while(i <10000)
do insert into log_test values (i,'中间提交的事务');
set i=i+1;
end while;
end
DB20000I The SQL command completed successfully.
如果我们把最后一个@删的，然后改成；然后执行db2 -tvf会发生什么？DB2会不会把文件看出一个存储过程，而是普通的DDL语句来执行，以；为DDL等sql的分隔符
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 -tvf insert_log_test.sql
CREATE OR REPLACE PROCEDURE INSERT_LOG_TEST()
begin
atomic declare i int default 0
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "lare i
int default 0". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=3. SQLSTATE=42601
while(i <10000) do insert into log_test values (i,'中间提交的事务')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "while(i <10000) do" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601
set i=i+1
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "I" is not valid in the context where it is used. SQLSTATE=42703
end while
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "end
while". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
end
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "end".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
调用存储过程：
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "select count(*) from log_test"
1
-----------
260000
1 record(s) selected.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "call insert_log_test()"
Return Status = 0
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "select count(*) from log_test"
1
-----------
270000
1 record(s) selected.
db2 "call insert_log_test()"执行的很快，不到1秒就插入成功了，而MYSQL相同的存储过程需要大概26秒左右，没想到会这么慢。。。
直接执行存储过程：返回结果也很快，不到1秒。
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "begin atomic declare i int default 0;while(i <10000) do insert into log_test values (i,'中间提交的事务');set i=i+1;end while;end"
DB20000I The SQL command completed successfully.
来看看MYSQL为啥这么慢，首先看他的存储过程定义：
vi insert_log_test.sql
delimiter // #定义标识符为双斜杠
drop procedure if exists insert_log_test; #如果存在test存储过程则删除
create procedure insert_log_test() #创建无参存储过程,名称为test
begin
declare i int; #申明变量
set i = 0; #变量赋值
while i < 10000 do #结束循环的条件: 当i大于10时跳出while循环
insert into log_test values (i,'中间提交的事务+++++++++**********++++++++:q'); #往test表添加数据
set i = i + 1; #循环一次,i加一
end while; #结束while循环
end
// #结束定义语句
插入10000条需要21秒多
mysql> call insert_log_test();
Query OK, 1 row affected (21.43 sec)
什么原因呢？怀疑是每插入一条就commit一次，一共commit了10000次，而DB2是插入10000条后提交了一次而已，下面来验证下
vi insert_log_test.sql
delimiter //
drop procedure if exists insert_log_test;
create procedure insert_log_test()
begin
declare i int;
set i = 0;
start transaction;
while i < 10000 do
insert into log_test values (i,'中间提交的事务+++++++++**********++++++++');
set i = i + 1;
end while;
commit;
end//
delimiter ;
mysql> source /home/mysql/liys/insert_log_test.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call insert_log_test();
Query OK, 0 rows affected (0.26 sec)
mysql> select count(*) from log_test;
+----------+
| count(*) |
+----------+
| 410000 |
+----------+
1 row in set (0.15 sec)
mysql> call insert_log_test();
Query OK, 0 rows affected (0.27 sec)
mysql> select count(*) from log_test;
+----------+
| count(*) |
+----------+
| 420000 |
+----------+
1 row in set (0.15 sec)
结果证明猜想是对的