A sample of DB2 define java external scalar user-defined function.
Take md5() function for example: The function will accept a string as input, and return md5 string.
Step 0 : verify DB2 java environment
$ db2 GET DBM CFG $ db2 GET DBM CFG | grep JDK Java Development Kit installation path (JDK_PATH) = /home/db2inst1-m/sqllib/java/jdk64 # Update DB2 JDK path when necessary $ db2 UPDATE DBM CFG USING jdk_path SDKforJava-pathStep 1: define java function md5()
package com.<company>.<organization>.<product>.util; import java.security.MessageDigest; import java.security.NoSuchAlgorithmException; import java.sql.Blob; import java.sql.SQLException; public class DBUtil { /** * Functionality API, it must be declared as public static * * @param input * @return * @throws NoSuchAlgorithmException * @throws SQLException */ public static String md5(Blob input) throws NoSuchAlgorithmException, SQLException { MessageDigest md = MessageDigest.getInstance("MD5"); byte[] b = md.digest(input.getBytes(1, (int) input.length())); return byteArrayToHex(b); } private static String byteArrayToHex(byte[] byteArray) { char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' }; char[] resultCharArray = new char[byteArray.length * 2]; int index = 0; for (byte b : byteArray) { resultCharArray[index++] = hexDigits[b >>> 4 & 0xf]; resultCharArray[index++] = hexDigits[b & 0xf]; } return new String(resultCharArray); } }Step 2: Compile and jar
javac -d java/bin java/src/com/<company>/<organization>/<product>/util/DBUtil.java jar -cvf DBUtil.jar -C java/bin .Step 3: Install jar file into DB2 system
db2 "call sqlj.install_jar('file:/path/to/DBUtil.jar','DBUTIL')" #db2 "call sqlj.remove_jar('DBUTIL')"Then you could find the jar file, in sqllib/function/jar/<DB2INSTANCE> folder
$ ls -1 sqllib/function/jar/<DB2INSTANCE>/DBUTIL.jar sqllib/function/jar/<DB2INSTANCE>/DBUTIL.jar The original jar filename was rename to JARID.jarStep 4: Define SQL function
--#SET TERMINATOR @ DROP FUNCTION MY_TOCHAR(VARCHAR(2000))@ CREATE FUNCTION MY_TOCHAR(P VARCHAR(2000)) RETURNS VARCHAR(100) NOT FENCED LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'DBUTIL:com.<company>.<organization>.<product>.util.DBUtil.md5' NO EXTERNAL ACTION DETERMINISTIC NO SCRATCHPAD NO SQL@The "EXTERNAL NAME" define where to find the real function implementation.
#EXTERNAL NAME 'JARID:pkgname:funname(signature)' Step 5: refresh when necessary
db2 "call sqlj.refresh_classes()" Step 6: Verify db2 => select MY_TOCHAR('1234') from sysibm.sysdummy1 1 ---------------------------------------------------------------------------------------------------- xxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1 record(s) selected.