DB2存储过程中LOAD CURSOR 使用

最新推荐文章于 2021-01-14 04:23:14 发布

dingguanyi

最新推荐文章于 2021-01-14 04:23:14 发布

阅读量2k

点赞数 1

分类专栏：数据库

数据库专栏收录该内容

131 篇文章 9 订阅

订阅专栏

经过反反复复几天的折腾，终于搞定了一个DB2的存储过程，真是不容易啊，感慨DB2做开发，咋就这么困难呢？

记录下个人的经历：

1、目的：

需要通过创建一个存储过程，对一个数据量较大的表（千万级）进行数据统计，将统计之后的数据放入到另外一张临时表中，待其它操作处理。

2、实现思路：

思路其实很简单，在sql server下核心sql 不过就是 insert 。。。select。。

3、实现过程：

1）、按照sql server下原有的思路，书写存储过程，由于表名非固定，因此需要用到动态sql。

源码如下：

[c-sharp]view plain copy
CREATE PROCEDURE proc_AggregateUrlVisit (in StrStartTime varchar(50), in StrEndTime varchar(50), in TempTableName varchar(50))  
LANGUAGE SQL   
BEGIN   
DECLARE SqlStr varchar(4000);   
--若存在临时表，则将临时表删除   
call proc_DropTable('behavesa', TempTableName);   
--根据表名创建表  
SET SqlStr = 'CREATE TABLE behavesa.' || TempTableName ||' (userid varchar(50), urlinfo varchar(700) , visitnum int, urlid int GENERATED ALWAYS AS IDENTITY ) NOT LOGGED INITIALLY ';  
EXECUTE IMMEDIATE SqlStr;  
 --插入数据统计后信息   
SET SqlStr = 'insert into behavesa.' || TempTableName || '(userid , urlinfo , visitnum) select userid, urlinfo, sum (visitnum) as visitnum from behavesa.UserUrlLog where starttime >= ''' || StrStartTime || ''' and endtime < ''' || StrEndTime || ''' group by userid, urlinfo' ;   
EXECUTE IMMEDIATE SqlStr;  
END@  

执行是没有问题的，可关键是周6下午执行，到了周1早上发现还在执行。。。疯掉。

搜集了下资料发现，insert 动态sql的时候会对每次插入进行日志，因此性能较低。而提高性能的建议则是用load 来代替insert。因此进行第2种方法操作。

2）、load方法有两种，一种是export。。。load；另一种则是load 。。。cursor。

通过试验sql语句发现export ..load 可以执行，而且速度也很快，但却有个不足，需要将数据先export到本地磁盘，然后方可进行load。

如果数据量小，问题不大，但对于本系统，数据动辄上亿行，而千万行就多达6,7百兆，因此舍弃该方式。

而第二种load。。。cursor是完全可以使用的，核心sql为：

declare staffcursor cursor for select userid, urlinfo, sum(visitnum) as visitnum

--省略wherefrom behavesa.UserUrlLog group by userid, urlinfo ;

load from staffcursor of cursor insert into behavesa.temptable;

测试发现执行时间约为17分钟左右（笔记本性能不咋地）。

于是采用load 。。。cursor方式修改存储过程，代码如下：

[c-sharp]view plain copy
CREATE PROCEDURE proc_AggregateUrlVisit (in StrStartTime varchar(50), in StrEndTime varchar(50), in TempTableName varchar(50))   
LANGUAGE SQL   
BEGIN   
DECLARE SqlStr varchar(4000);   
--若存在临时表，则将临时表删除 call beahvesa.proc_DropTable('behavesa', TempTableName);  
--根据表名创建表   
SET SqlStr = 'CREATE TABLE behavesa.' || TempTableName ||' (userid varchar(50), urlinfo varchar(700) , visitnum int, urlid int GENERATED ALWAYS AS IDENTITY ) NOT LOGGED INITIALLY in userspace1';   
EXECUTE IMMEDIATE SqlStr;  
--插入数据统计后信息   
SET SqlStr = 'declare staffcursor cursor for select userid, urlinfo, sum(visitnum) as visitnum from behavesa.UserUrlLog where starttime >= ''' || StrStartTime || ''' and endtime < ''' || StrEndTime || ''' group by userid, urlinfo ; load from staffcursor of cursor insert into behavesa.' || TempTableName ||';';   
EXECUTE IMMEDIATE SqlStr;   
END@  

db2 -td@ -f test.db2，没有问题，可以执行，但执行

      db2 call proc_AggregateUrlVisit ('2009-02-01 00:00:01','2009-04-01 00:00:01', 'temptable')

      则会报错：

   SQL0104N 在 "01' group by userid," 后面找到异常标记 "urlinfo ; load from
   staffcursor of curso"。预期标记可能包括："<space>"。 SQLSTATE=42601

怎么修改也死活过不去，一开始怀疑是动态sql的问题，查资料发现有资料说

“execute immediate只能执行DML语句!”，因此试验了下，直接在存储过程中load。。。cursor，发现也不成整半天，存储过程中是不支持LOAD的。。。。

    3）、借助 SYSPROC.ADMIN_CMD 实现

       没办法了。。。于是查资料吧，嘿，发现原来可以借助系统存储过程 SYSPROC.ADMIN_CMD 来实现的。主要sql如下：

        CALL SYSPROC.ADMIN_CMD ('load from (select userid, urlinfo, sum(visitnum) as visitnum

from UserUrlLog where starttime >= StrStartTime and EndTime <= StrEndTime
        group by userid, urlinfo ) of cursor insert into HttplogTempTable');

         按此思路继续进行修改存储过程，发现本机可以哦～没问题（db2版本为DB2 Express-C 9.5）。

4）、借助 sysproc.db2load 实现

       折腾了半天，以为可以了，于是将写好的存储过程移到服务器上，由于版权问题服务器版本为 "DB2 企业服务器版8.2",

编译不过去了。。。。

错误信息：

DB21034E 该命令被当作 SQL语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：SQL0440N 未找到类型为 "PROCEDURE" 命名为 "SYSPROC.ADMIN_CMD"
且具有兼容自变量的已授权例程。 LINE NUMBER=7. SQLSTATE=42884

郁闷啊，通过控制中心查找ADMIN_CMD存储过程，嘿嘿，真的没有哦，搜吧，整半天，从8.2版本以后才开始支持该存储过程的，怪不得呢。继续查资料吧（估计公司的监控会显示我最近访问网页数量过多哦～～）。

解决办法：1）、更新fix包； 2）寻找其它替代方式。

害怕fix后影响版权相关，而且也嫌麻烦，所以就找到了db2load的方法，更改后的存储过程为：

[c-sharp]view plain copy
CREATE PROCEDURE proc_AggregateUrlVisit (in StrStartTime varchar(50), in StrEndTime varchar(50) , in TempTableName varchar(100))   
LANGUAGE SQL   
BEGIN   
declare SqlStr varchar(500);  
declare v_version_number INTEGER default 1;  
declare v_cursor_statement VARCHAR(32672);  
declare v_load_command VARCHAR(32672);  
declare v_sqlcode INTEGER default -1;  
declare v_sqlmessage VARCHAR(2048) default '';  
declare v_rows_read BIGINT default -1 ;  
declare v_rows_skipped BIGINT default -1;  
declare v_rows_loaded BIGINT default -1;  
declare v_rows_rejected BIGINT default -1;  
declare v_rows_deleted BIGINT default -1;  
declare v_rows_committed BIGINT default -1;  
declare v_rows_part_read BIGINT default -1;  
declare v_rows_part_rejected BIGINT default -1;  
declare v_rows_part_partitioned BIGINT default -1;  
declare v_mpp_load_summary VARCHAR(32672) default NULL;  
--若存在临时表，则将临时表删除  
call proc_DropTable('behavesa', TempTableName);  
--根据表名创建表  
SET SqlStr = 'CREATE TABLE behavesa.' || TempTableName ||' (userid varchar(50), urlinfo varchar(700)  
, visitnum int, urlid int GENERATED ALWAYS AS IDENTITY ) NOT   LOGGED   INITIALLY ';  
EXECUTE IMMEDIATE SqlStr;  
--更改表附加方式  
SET SqlStr = 'ALTER TABLE ' || TempTableName || ' APPEND ON';  
EXECUTE IMMEDIATE SqlStr;  
--调用DB2LOAD,进行select cursor 插入数据统计后的信息  
CALL sysproc.db2load ( v_version_number, 'declare cur cursor  
for select userid, urlinfo, sum(visitnum) as visitnum  from UserUrlLog  
where starttime >= ''' || StrStartTime || ''' and endtime < ''' || StrEndtime  
|| '''group by userid, urlinfo ',  
'load from cur of cursor modified by norowwarnings insert into ' || TempTableName,  
v_sqlcode, v_sqlmessage, v_rows_read, v_rows_skipped,  
v_rows_loaded, v_rows_rejected, v_rows_deleted, v_rows_committed,  
v_rows_part_read, v_rows_part_rejected,  
v_rows_part_partitioned, v_mpp_load_summary) ;  
END@  

oh，搞定了。。。呵呵，终于见到阳光了。。难为死我了。

但是db2load那些参数都是啥作用呢？查Reference 文档没查到。。只能大概揣测了。

参考网址：

    http://www.itpub.net/thread-936984-1-1.html

    http://www.db2china.net/?viewnews-2142.html

    http://topic.csdn.net/u/20071216/19/aad3c957-e4cb-4aab-9d52-414a7a6cf32f.html

    http://bytes.com/groups/ibm-db2/478049-calling-sysproc-db2load-within-sql-stored-procedure

    http://topic.csdn.net/u/20070416/15/4285e1e8-6693-46d0-9912-5c87781e7fa0.html