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

• Up and Running With DB2 for Linux (Ibm Redbooks.)