使用DB2自带的SOAP UDF实现业务通知

DB2自带了WebService客户端的实现，具体命令如下：

启用命令：

db2enable_soap_udf -n dbName -u uID -p password [-force]
The parameters have the following definitions:
dbName - The database name to be enabled
uID user - ID for accessing the database
password - The password associated with the user ID
-force - Attempts to drop any existing functions.

如：db2enable_soap_udf -n MS_TRD -u zjyw -p 123456

启用成功后，可调用以下API接口：

db2xml.soaphttpv (VARCHAR(256), VARCHAR(256), VARCHAR(3072))
db2xml.soaphttpv (VARCHAR(256), VARCHAR(256), CLOB(1M))
db2xml.soaphttpc (VARCHAR(256), VARCHAR(256), varchar(3072))
db2xml.soaphttpc (VARCHAR(256), VARCHAR(256), CLOB(1M))
db2xml.soaphttpcl ( VARCHAR(256), VARCHAR(256), varchar(3072))

停用命令：

db2disable_soap_udf -n dbName -u uID -p password

调用步骤：

1、测试webService客户端与服务端的连通性

新建test.sql，编写如下代码：

 

connect to MS_TRD;
VALUES db2xml.soaphttpc(
'http://191.168.0.209:8080/SHCH/services/shch',
'http://191.168.0.209:8080/SHCH/services/shch.shchHttpEndpoint/',
varchar ('<SendMessage xmlns="http://axis2.shch.xtp.xquant.com"><action>81</action><requestXml>12345</requestXml></SendMessage>'
)
);

 在DB2命令窗口中执行：db2 -tvf d:\test.sql

成功调用后，就可以继续写DB2函数供存储过程或者触发器调用了

2、新建表TTRD_UDF,具体见使用 DB2 触发器和Java UDF实现业务通知

 

3、新增10000条测试数据

begin atomic 
 declare v_cnt int;
 set v_cnt=0;
 while (v_cnt<10000) do 
 insert into TTRD_UDF(CODE, NAME, REMARK) values (rtrim(char(v_cnt+100000)),'test', '-');
 set v_cnt=v_cnt+1;
 end while;
end

 

4、新建DB2函数

CREATE FUNCTION zjyw.SENDMESSAGE2 (symbol VARCHAR(100))
RETURNS VARCHAR(1000)
LANGUAGE SQL CONTAINS SQL
EXTERNAL ACTION NOT DETERMINISTIC
RETURN
VALUES db2xml.soaphttpc(
'http://191.168.0.209:8080/SHCH/services/shch',
'http://191.168.0.209:8080/SHCH/services/shch.shchHttpEndpoint/',
varchar ('<SendMessage xmlns="http://axis2.shch.xtp.xquant.com"><action>81</action><requestXml>'|| + symbol + ||'</requestXml></SendMessage>' ) ) )

5、新建TTRD_UDF触发器

CREATE TRIGGER ZJYW.AFTER_UPDATE_UFT
AFTER UPDATE OF NAME ON ZJYW.TTRD_UDF
REFERENCING OLD AS OLDROW NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF(NEWROW.NAME<>'')
THEN
VALUES(SENDMESSAGE2(OLDROW.CODE));
END IF;
END;

6、测试批量修改10000条数据性能

update zjyw.TTRD_UDF set NAME='TEST2'

全部执行完成耗时36分17秒，性能很一般。

如果WebService服务端未开启时，触发器会超时等待，实用性比较差。