1.数据库版本
2.Java脚本
importjava.security.MessageDigest;importCOM.ibm.db2.app.UDF;public class MD5UDF extendsUDF {public staticString MD5(String s) {
String s1= new String("");char hexDigits[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9','a', 'b', 'c', 'd', 'e', 'f'};try{byte[] strTemp =s.getBytes();
MessageDigest mdTemp= MessageDigest.getInstance("MD5");
mdTemp.update(strTemp);byte[] md =mdTemp.digest();int j =md.length;char str[] = new char[j * 2];int k = 0;for (int i = 0; i < j; i++) {byte byte0 =md[i];
str[k++] = hexDigits[byte0 >>> 4 & 0xf];
str[k++] = hexDigits[byte0 & 0xf];
}
s1= newString(str);
}catch(Exception e) {
System.out.println(e.getMessage());
}return "{" + s1 + "}";
}public static voidmain(String[] args){
System.out.println(MD5("asdf"));
}
}
3.运行javac编译
javacMD5UDF.java
4.运行jar打包MD5UDF.class 为MD5UDF.jar
jar cfMD5UDF.jarMD5UDF.class
5.连上数据库,运行安装.jar文件到数据库(C:\Documents and Settings\ALL USERS\Application Data\IBM\DB2\DB2COPY1\function\jar\ZEN 目录下)
db2 connect to sample
db2 "CALL sqlj.install_jar('file:E:\MD5UDF.jar', 'MD5UDF')"
6.登录数据库创建function
DROP FUNCTIONBI_MD5;CREATE FUNCTION BI_MD5(VARCHAR(200))RETURNS VARCHAR(70)
EXTERNAL NAME'MD5UDF:MD5UDF.MD5'FENCED VARIANT
NO SQL
EXTERNAL ACTION
LANGUAGE JAVA
PARAMETER STYLE JAVA
7.调用测试
select bi_MD5('12345') from sysibm.dual
OK!
结论:
1.安装、删除、替换jar文件
db2 "CALL sqlj.install_jar('file:D:\someBookes\Java\MD5UDF2.jar', 'BIMD5')"
db2 "CALL sqlj.remove_jar('BIMD5')"
db2 "CALL sqlj.replace_jar('file:E:\mydb2\mylog.jar', 'BIMD5')"
2.刷新已经调用的jar或class,不用重启实例就生效:
db2 "CALL SQLJ.REFRESH_CLASSES()"
3.调用的Java方式必须是静态的。
4.编译成jar包的SDK版本必须和db2的版本相符
否则会报错