Implementing business notifications using DB2 triggers and Java UDFs

Application Scenarios:

1. The records in the DB2 database are changed.
2. If this change causes the data record to satisfy certain Boolean logic-based conditions, the database trigger invokes a Java UDF.
3. Java UDF notifies other services using the WebService interface.

Step 1: Create a table
CREATE TABLE Zjyw. Ttrd_udf
(CODE varchar () is not NULL,
NAME varchar (NOT NULL),
REMARK varchar (+) NOT NULL)

Step 2: Test the data
INSERT INTO ZJYW. TTRD_UDF values (' TF1501 ', ' TF1501 ', ' http://127.0.0.1:8080/Test ');
INSERT INTO ZJYW. TTRD_UDF values (' TF1503 ', ' TF1503 ', ' http://127.0.0.1:8080/Test ');

Step 3: Create a new Javaudf.java

Import Java.io.bufferedwriter;import java.io.file;import java.io.fileoutputstream;import java.io.IOException; Import Java.io.outputstreamwriter;import Com.ibm.db2.app.udf;public class Javaudf extends UDF {//private static final Logger Logger = Loggerfactory.getlogger (javaudf.class);/** * Create file * @param fileName * @throws ioexception */public static void CreateFile (String fileName) throws IOException {file F = new File (fileName), if (!f.exists ()) {f.createnewfile ();}} /** * Append Write file * @param fileName * @param content * @throws ioexception */public static void WriteFile (String fileName, Strin G content) throws IOException {createFile (fileName); BufferedWriter out = null;try {out = new BufferedWriter (new OutputStreamWriter (New FileOutputStream (FileName, True)); O Ut.write (content);} Finally {if (out! = null) {Out.close ();}}} public static string Send (String I_code, String i_name,string i_code_new, string i_name_new) {try {WriteFile ("D:/test.txt ", I_name + i_name_new);} catch (IOException e) {//TODO Auto-generated catch Blocke.printstacktrace ();} Logger.info (input);//Sendwebservice (""); Not implemented return "OK";}}

Compile the Javaudf.java, generate the Javaudf.class copy to DB2 's FUNCTION directory, such as: C:\Program files\ibm\sqllib\function

Packaged into a xquant-udf.jar bag

Step 4: Register the JAR Package
Call Sqlj.install_jar (' File:///d:/xquant/java/xQuant-UDF.jar ', ' UDF ')

Step 5: Create a new function
Create function Zjyw. SENDMESSAGE (CODE varchar (30),
NAME varchar (50),
Code_new varchar (30),
Name_new varchar (50))
Returns varchar (100)
Fenced
Variant
No SQL
External action
Language Java
Parameter style Java
External name ' Xquant-udf:javaudf.send '

Step 6: Triggers
CREATE TRIGGER Zjyw. After_update_uft
After UPDATE of the NAME on Zjyw. Ttrd_udf
Referencing old as Oldrow NEW as NEWROW
For each ROW MODE Db2sql
BEGIN ATOMIC
IF (newrow.name<> ")
Then
VALUES (SENDMESSAGE (Oldrow. Code,oldrow.name,newrow. Code,newrow.name));
END IF;
END;

Step 7: Modify the data

Update Zjyw. TTRD_UDF set name= ' TF1506 ' where code= ' TF1503 ';

After successful execution, a test.txt file is generated on the D drive of the DB2 server.

Implementing business notifications using DB2 triggers and Java UDFs