DB2基于JAVA的自定义函数(user-defined-function)示例

DB2基于JAVA的自定义函数(user-defined-function)示例, 作用是返回两个数的和。

测试环境：

$ db2level DB21085I This instance or install (instance name, where applicable: "e105q5a") uses "64" bits and DB2 code release "SQL10055" with level identifier "0606010E". Informational tokens are "DB2 v10.5.0.5", "s141128", "IP23626", and Fix Pack "5". Product is installed at "/opt/IBM/db2/V10.5.5". $ oslevel -s 6100-08-02-1316

1. 找到DB2自带的JDK path

$ db2 get dbm cfg | grep -i jdk_path  Java Development Kit installation path       (JDK_PATH) = /home/db2users/e105q5a/sqllib/java/jdk64

2. 创建JAVA文件 

MyCalculate.java，内容如下： import COM.ibm.db2.app.*; import java.sql.*; public class MyCalculate extends UDF{ public static int mySum(int a, int b) throws SQLException { return a + b; } }

3. 编译JAVA文件  

$ /home/db2users/e105q5a/sqllib/java/jdk64/bin/javac MyCalculate.java

4. 将生成的类文件拷贝到CLASSPATH中

将生成的MyCalculate.class 文件拷贝到CLASSPATH环境变量指定的任一目录中 $ cp MyCalculate.class $HOME/sqllib/function

5. 创建function

$ cat crtFn.txt CREATE FUNCTION mqssum( numbera int, numberb int) RETURNS INT LANGUAGE java PARAMETER STYLE java NO SQL FENCED THREADSAFE DETERMINISTIC RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION EXTERNAL NAME 'MyCalculate!mySum' @ $ db2 -td@ -f crtFn.txt DB20000I The SQL command completed successfully.

6.调用function：

$ db2 "select mqssum(3,4) from sysibm.sysdummy1" 1 ----------- 7 1 record(s) selected. 注意1：一定要使用DB2自带的java，不然调用函数时可能会报出SQL4304N Reason Code "1" 的错误  注意2：如果修改了Class或者JAR包，重新部署之后，会发现调用function仍然返回原来的结果，并没有生效，这时候需要调用下面的存储过程 db2 "CALL SQLJ.REFRESH_CLASSES()" The CALL SQLJ.REFRESH_CLASSES() statement forces the database manager to load the new class into memory upon the next commit or rollback operation. The CALL SQLJ.REFRESH_CLASSES() statement does not affect the unfenced routines. For unfenced routines, you must explicitly stop and restart the database manager in order for new versions of Java routine classes to be loaded and used. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.routines.doc/doc/t0006410.html 参考资料： https://newpush.com/2009/08/creating-a-user-defined-function-udf-in-java-for-ibm-db2-9-7/