DB2 define external scalar java user-defined function

    xiaoxiao2021-12-10  7

    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-path

    Step 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.jar

    Step 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.

    转载请注明原文地址: https://ju.6miu.com/read-700193.html

    最新回复(0)