DB2存储过程中LOAD CURSOR 使用

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

记录下个人的经历：

           1、目的：

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

           2、实现思路：

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

          3、实现过程：

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

 源码如下： 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方式修改存储过程，代码如下：

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的方法，更改后的存储过程为：

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