Creating a User Defined Function (UDF) in Java for IBM DB2 9.7

Intro

One of the great features of DB2 is that it can be extended with custom code using SQL, C/C++, Java and COBOL. One of the great new features added to DB2 in 9.7 is the ability to run native Oracle PL/SQL code, that capability opens up a world of possibilities, and at the same time, the need to extend DB2 with functions that might have been available on the Oracle install and now need to be replicated on DB2. In this short article I describe how to extend DB2 9.7 with Java UDFs.

Prerequisites

  • Check that Java is properly installed and configured on DB2:
    db2 get dbm config|grep JDK

    The output should show the path to the JDK installation in DB2. If that isn’t the case, you need to consult the DB2 install manual and make sure Java is installed and configured for DB2.
  • Make sure that your user has access to java and javac, and that it is the same version or compatible with DB2’s JDK
  • Set the database manager parameter KEEPFENCED to NO, so that the UDF will be reloaded in each call.

Writing the test code

  • Use your favorite text editor or IDE and create a file called udftest.java with the sample code:
    public class udftest {
            public static String udftest(String domainName) {
                    return "Domain Name: " + domainName;
            }
    }
  • Compile the java program:
    javac udftest.java
  • Create a jar file:
    jar cvf udftest.jar udftest.class
  • Register the jar file with DB2:
    db2 "CALL sqlj.install_jar('file:/home/bnagy/dev/java/udf/udftest.jar','UDFTESTJAR')"

Creating the UDF in DB2

  • Use your favorite text editor or IDE and create a file called udftest-create.sql with the sample code:
    CONNECT TO SAMPLE;
    CREATE FUNCTION udftest(VARCHAR(255)) RETURNS VARCHAR(255)
    FENCED
    EXTERNAL NAME 'UDFTESTJAR:udftest.udftest'
    NOT VARIANT NO SQL PARAMETER STYLE java LANGUAGE java
    NO EXTERNAL ACTION;
    CONNECT RESET;
  • Run the scritpt in DB2:
    db2 -tvf udftest-create.sql

Testing

To check that the function works, run the following command on the command line:
db2 "select udftest('test.com') from sysibm.sysdummy1"

You should get a regular SELECT result set with the expected values. If something goes wrong, you can look at the db2diag.log that will contain any Java stack trace that would be generated.

References

  • Anonymous

    Thanks for the very helpful article, Balazs. I have two quick questions regarding the prerequisites you listed.

    First, you said that KEEPFENCED should be set to NO. I’m going to be processing ~200M rows, and it seems to me that it should be set to YES. Am I missing something?

    The second question relates to Java versions. I’ve loaded Java 1.6 on my machine and use it for development work. I don’t know how to tell if it’s compatible with the IBM version.

    Thanks for your assistance.

    David

    • Regarding KEEPFENCED, the UDF runs faster when it is not fenced. However, it is of course more risky, as you can crash the instance more easily.

      Regarding the Java version, if you have Data Studio (or Optim Development Studio) installed than you automatically get the IBM version of Java. You just need to select the right JVM for within the IDE. (Personally I never has a compatibility issue, however, you might be dealing with Java 1.5 running on the DB2 database server, and in that case you obviously need to be careful not to use extensions of the Java language that are not available in 1.5.