Java Implementation

To implement an MD5 hashing function within DB2, a Java class extending COM.ibm.db2.app.UDF is required. The class must contain a public static method that performs the hashing logic. Below is an implementation using the standard Java security library to convert an input string into its MD5 hexadecimal representation.

import java.security.MessageDigest;import COM.ibm.db2.app.UDF;

public class DbHashUtil extends UDF {

    public static String generateMd5(String input) {
        if (input == null) {
            return null;
        }
        try {
            MessageDigest digest = MessageDigest.getInstance("MD5");
            byte[] hashBytes = digest.digest(input.getBytes());
            
            StringBuilder hexString = new StringBuilder();
            for (byte b : hashBytes) {
                String hex = Integer.toHexString(0xff & b);
                if (hex.length() == 1) {
                    hexString.append('0');
                }
                hexString.append(hex);
            }
            return hexString.toString();
        } catch (Exception e) {
            return e.getMessage();
        }
    }
}

Compilation and Packaging

Compile the Java source file and package the resulting class file into a JAR archive. Ensure the Java SDK version used for compilation is compatible with the DB2 instance version to avoid runtime errors.

javac DbHashUtil.java
jar cf DbHashUtil.jar DbHashUtil.class

Deploying the JAR to DB2

Connect to the target database and install the JAR file using the sqlj.install_jar stored procedure. This procedure copies the JAR into the DB2 function directory.

db2 connect to sample
db2 "CALL sqlj.install_jar('file:/path/to/DbHashUtil.jar', 'DB_HASH_JAR')"

Creating the SQL Function

Create a user-defined function that maps to the static Java method. The EXTERNAL NAME clause must specify the JAR ID, class name, and method name separated by colons.

CREATE FUNCTION SYS_MD5 (INPUT VARCHAR(200)) 
RETURNS VARCHAR(32) 
EXTERNAL NAME 'DB_HASH_JAR:DbHashUtil.generateMd5' 
FENCED 
VARIANT 
NO SQL 
EXTERNAL ACTION 
LANGUAGE JAVA 
PARAMETER STYLE JAVA;

Verification

Test the function by executing a select statement against the system dummy table.

SELECT SYS_MD5('test_string') FROM sysibm.sysdummy1;

Routine Management

DB2 provides specific procedures for managing deployed Java routines:

• Update: Use sqlj.replace_jar to update the JAR file without dropping the function definition.

db2 "CALL sqlj.replace_jar('file:/path/to/new/DbHashUtil.jar', 'DB_HASH_JAR')"

• Refresh: Execute SQLJ.REFRESH_CLASSES() to reload Java classes without restarting the database instance.

db2 "CALL SQLJ.REFRESH_CLASSES()"

• Removal: Use sqlj.remove_jar to uninstall the library.

db2 "CALL sqlj.remove_jar('DB_HASH_JAR')"