使用SQL存储过程实现并行更新

使用SQL存储过程实现并行更新

场景描述：
1、需要对多个表的某字段进行更新
2、需要更新的表数量很多（超过200）
3、每个表需要更新的行数很多（包含大量历史数据，超过10万）

更新策略：

考虑到以下情况：
1、需要更新的表数量很多，不可能手工逐个更新；
2、每个表需要更新的记录多，在一个数据连接中执行串行更新，耗时太长。

鉴于以上情况，决定采用使用存储过程，并行更新，实现以下目标：
1、在同一个存储过程中实现；
2、通过在不同的数据库连接中调用同一个存储过程，并行更新；
3、可以指定表进行更新，若没有指定，则根据需求自动生成更新列表；
4、更新过程中，可以随时监控更新进度；
5、可以查看每个表更新的起止时间及更新记录数量；
6、更新结束后，可以显式每次过程调用更新表的个数。

DROP PROCEDURE ppara @
DROP TABLE htab1 @
DROP TABLE htab2 @
DROP TABLE htab3 @
DROP TABLE tpara @
DROP PROCEDURE sleep @

--测试使用，模拟一个长时间更新的延时
--占用CPU过高，延时不要搞太长
CREATE PROCEDURE sleep(IN func_input smallint)
SPECIFIC sleep
LANGUAGE SQL
READS SQL DATA
BEGIN
 DECLARE spc_time timestamp;
 DECLARE cur_time timestamp;
 
 SET spc_time = CURRENT TIMESTAMP;
 
 WHILE (CURRENT TIMESTAMP - spc_time < func_input - 1.5)
 DO
  
 END WHILE;

END
@

--存放需要更新的表，如果手动将需要更新表插入该表中，则可以实现指定表更新
--uptsign，表示该表的更新标记，2表示未更新，1表示正在更新，0表示更新完毕
CREATE TABLE tpara(
    tabschema varchar(128) NOT NULL,
    tabname varchar(128) NOT NULL,
    colname varchar(128) NOT NULL,
    upvalue varchar(128),
    strtime timestamp,
    endtime timestamp,
    uptrows integer WITH DEFAULT 0,
    uptsign smallint WITH DEFAULT 2
) @

CREATE UNIQUE INDEX iu_tpara_uptsign ON tpara(tabschema, tabname, colname) INCLUDE(uptsign) @

--测试使用，需要更新的表
CREATE TABLE htab1(col1 varchar(128)) @
CREATE TABLE htab2(col1 varchar(128)) @
CREATE TABLE htab3(col1 varchar(128)) @

--测试使用，插入原始测试数据
INSERT INTO htab1 WITH temp(col1) AS (VALUES 1 UNION ALL SELECT col1 + 1 FROM temp WHERE col1 < 100) SELECT rtrim(char(col1)) FROM temp @
INSERT INTO htab2 WITH temp(col1) AS (VALUES 1 UNION ALL SELECT col1 + 1 FROM temp WHERE col1 < 100) SELECT rtrim(char(col1)) FROM temp @
INSERT INTO htab3 WITH temp(col1) AS (VALUES 1 UNION ALL SELECT col1 + 1 FROM temp WHERE col1 < 100) SELECT rtrim(char(col1)) FROM temp @

--并行更新的存储过程
CREATE PROCEDURE ppara(OUT proc_out varchar(128))
SPECIFIC ppara
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
 DECLARE v_uptrows integer;
 DECLARE v_stmt varchar(1024);
 DECLARE v_strtime timestamp;
 DECLARE v_endtime timestamp;
 DECLARE v_zxnum integer DEFAULT 0;
 DECLARE v_jxnum integer DEFAULT 0;
 
 p1:BEGIN
  IF EXISTS(SELECT 1 FROM tpara) THEN
  
  ELSE
   --模拟更新需求中需要更新的表
   --注意该脚本所有模式均为DB2ADMIN，可根据实际环境调整
   INSERT INTO tpara(tabschema, tabname, colname, upvalue)
    VALUES('DB2ADMIN', 'HTAB1', 'COL1', 'History Data1'),
          ('DB2ADMIN', 'HTAB2', 'COL1', 'History Data2'),
          ('DB2ADMIN', 'HTAB3', 'COL1', 'History Data3');
   
   COMMIT;
  END IF;
 END p1;
 
 p2:BEGIN
  for_lab1:
  FOR ThisList
   AS c1 CURSOR WITH HOLD FOR
    SELECT tabschema,
           tabname,
           colname,
           upvalue
      FROM tpara
     WHERE uptsign = 2
  DO
   UPDATE (
           SELECT uptsign
             FROM tpara
            WHERE tabschema = ThisList.tabschema
              AND tabname = ThisList.tabname
              AND colname = ThisList.colname
              AND uptsign = 2
          )
      SET uptsign = 1;
   
   GET DIAGNOSTICS v_uptrows = ROW_COUNT;
   IF (v_uptrows = 0) THEN
    SET v_jxnum = v_jxnum + 1;
    ITERATE for_lab1;
   ELSE
    COMMIT;
    SET v_zxnum = v_zxnum + 1;
   END IF;
   
   SET v_stmt = 'UPDATE'
                  ||space(1)
                  ||rtrim(ThisList.tabschema)
                  ||'.'
                  ||rtrim(ThisList.tabname)
                  ||space(1)
                  ||'SET'
                  ||space(1)
                  ||rtrim(ThisList.colname)
                  ||space(1)
                  ||'='
                  ||space(1)
                  ||chr(39)
                  ||rtrim(ThisList.upvalue)
                  ||chr(39);
   
   SET v_strtime = CURRENT TIMESTAMP;
   EXECUTE IMMEDIATE v_stmt; 
   GET DIAGNOSTICS v_uptrows = ROW_COUNT;
   SET v_endtime = CURRENT TIMESTAMP;
   
   --延时更新30秒
   CALL sleep(30);
   
   UPDATE tpara
      SET uptsign = 0,
          uptrows = v_uptrows,
          strtime = v_strtime,
          endtime = v_endtime
    WHERE tabschema = ThisList.tabschema
      AND tabname = ThisList.tabname
      AND colname = ThisList.colname;
   
   COMMIT;    
  END FOR;
 END p2;
 
 SET proc_out = 'Excute Number:'
              ||rtrim(char(v_zxnum))
              ||';'
              ||'Continue Number:'
              ||rtrim(char(v_jxnum));
END
@

测试步骤：
1、创建测试脚本；
2、分别打开四个db2cmd窗口，均连接到测试数据库；
3、分别在前三个db2cmd窗口中，执行：db2 "call ppara(?)"
4、在最后一个db2cmd窗口中，监控更新状态,通过查询：db2 "select * from tpara with ur"

测试结果：

第一个db2cmd窗口：
C:\>db2 "call ppara(?)"

  输出参数的值
  --------------------------
  参数名： PROC_OUT
  参数值： Excute Number:1;Continue Number:0

  返回状态 = 0

第二个db2cmd窗口：
C:\>db2 "call ppara(?)"

  输出参数的值
  --------------------------
  参数名： PROC_OUT
  参数值： Excute Number:1;Continue Number:0

  返回状态 = 0

第三个db2cmd窗口：
C:\>db2 "call ppara(?)"

  输出参数的值
  --------------------------
  参数名： PROC_OUT
  参数值： Excute Number:1;Continue Number:0

  返回状态 = 0
 
 注：Excute Number指更新的表个数，Continue Number指的是跳过更新的表
    （存在一种情况就是：A调用正在更新一个表时，当B调用循环到更新同一个表时，B调用就会自动跳过）
   
    有兴趣的朋友，可以研究下为什么Continue Number都为0。

第四个db2cmd窗口：

更新前：
C:\>db2 "select uptsign from tpara with ur"

UPTSIGN
-------
      2
      2
      2

  3 条记录已选择。

更新中：
C:\>db2 "select uptsign from tpara with ur"

UPTSIGN
-------
      1
      1
      1

  3 条记录已选择。

更新后
C:\>db2 "select uptsign from tpara with ur"

UPTSIGN
-------
      0
      0
      0

  3 条记录已选择。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7690668/viewspace-617831/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7690668/viewspace-617831/