DB2 define external scalar java user-defined function

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.