linux 并行存储过程,使用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 条记录已选择。