南昌网站优化,wordpress对接公众号源码,如何做网站拉动条,wordpress 栏目菜单db2执行文件参数#xff1a; -t 表示语句使用默认的语句终结符——分号#xff1b; -v 表示使用冗长模式#xff0c;这样 DB2 会显示每一条正在执行命令的信息#xff1b; -f 表示其后就是脚本文件#xff1b; -z表示其后的信息记录文件用于记录屏幕的输出 -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 ii1; end while; end
[db2inst1t3-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 ii1; end while; end
DB20000I The SQL command completed successfully.
如果我们把最后一个删的然后改成然后执行db2 -tvf会发生什么DB2会不会把文件看出一个存储过程而是普通的DDL语句来执行以为DDL等sql的分隔符
[db2inst1t3-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 NUMBER3. SQLSTATE42601
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. SQLSTATE42601
set ii1 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. SQLSTATE42703
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. SQLSTATE42601
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. SQLSTATE42601
调用存储过程 [db2inst1t3-dtpoc-dtpoc-web04 liys]$ db2 select count(*) from log_test
1 ----------- 260000 1 record(s) selected.
[db2inst1t3-dtpoc-dtpoc-web04 liys]$ db2 call insert_log_test() Return Status 0 [db2inst1t3-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秒。 [db2inst1t3-dtpoc-dtpoc-web04 ~]$ db2 begin atomic declare i int default 0;while(i 10000) do insert into log_test values (i,中间提交的事务);set ii1;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)
结果证明猜想是对的
