Implementing a Business Notification using DB2 's own soap UDF

DB2 comes with the implementation of the WebService client, with the following specific commands:

Enable command:

Db2enable_soap_udf-n dbname-u uid-p Password [-force]
The parameters has the following definitions:
Dbname-the database name to is enabled
UID User-id for accessing the database
Password-the password associated with the user ID
-force-attempts to drop any existing functions.

such as: Db2enable_soap_udf-n ms_trd-u zjyw-p 123456

After success is enabled, the following API interfaces can be called:

DB2XML.SOAPHTTPV (varchar), varchar (+), varchar (3072))
DB2XML.SOAPHTTPV (varchar), varchar (CLOB (1M))
DB2XML.SOAPHTTPC (varchar), varchar (+), varchar (3072))
DB2XML.SOAPHTTPC (varchar), varchar (CLOB (1M))
DB2XML.SOAPHTTPCL (varchar), varchar (+), varchar (3072))

To deactivate a command:

Db2disable_soap_udf-n dbname-u uid-p Password

To invoke a step:

1. Test the connectivity between the WebService client and the server

To create a new Test.sql, write the following code:

 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  ( '  

Execute in the DB2 command window: DB2-TVF d:\test.sql

After a successful call, you can continue to write the DB2 function for the stored procedure or trigger to invoke the

2, the new table ttrd_udf, see the use of DB2 triggers and Java UDF to implement business notifications

3. Added 10,000 test data

beginAtomicDeclareV_cntint; SetV_cnt=0;  while(v_cnt<10000) doInsert  intoTTRD_UDF (CODE, NAME, REMARK)Values(RTrim(Char(v_cnt+100000)),'Test','-'); SetV_cnt=V_cnt+1; End  while;End

4. New DB2 function

CREATE FUNCTIONZjyw. SENDMESSAGE2 (symbolVARCHAR( -))RETURNS VARCHAR( +) LANGUAGE SQLCONTAINSsqlexternal ACTION notDeterministicRETURNVALUESDb2xml.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. New TTRD_UDF Trigger

CREATE TRIGGERZjyw. After_update_uftafterUPDATE  ofNAME onZjyw. Ttrd_udfreferencing Old asOldrow NEW asNEWROW forEach ROW MODE db2sqlBEGINATOMICIF(Newrow.name<>"') ThenVALUES(SENDMESSAGE2 (Oldrow. CODE));END IF;END;

6, test batch modification 10,000 data performance

Update Set NAME='TEST2'

It takes 36 minutes and 17 seconds to complete the execution, and the performance is very general.

If the WebService service side is not turned on, the trigger will time out waiting and the utility is relatively poor.

Implementing a Business Notification using DB2 's own soap UDF