How to Write own Java user-defined functions in DB2 & call UDFs in DB2 SQL with Example Step by Steps

            User Defined Functions (UDFs) are own functions written by the user which can be used in SQL, DDL or DML statements. UDfs can be written in two ways on DB2 Universal Database. One is SQL UDFs which is based on procedural extensions to the SQL language, highly used by other DBMS. The Other one is External UDFs which is written host languages like Java and others. Now let us write a user defined function in Java which can be used in in SQL. For example, In oracle INITCAP function returns the first letter of each word in uppercase, all other letters will be in lowercase. This function may not be available in DB2. Now we can write this function in Java can be used in DB2 .

Steps involved to write, deploy and run the UDFs

1. Write program for initCaps in Java

import java.io.*;
import java.io.*;
public class initCaps
{
public static String firstCaps(String line)  throws IOException 
{
 StringReader in = new StringReader(line); 
 boolean isNextCharSpace = true;
 StringBuffer proper = new StringBuffer();    
int i=0;
     while((i=in.read())!=-1) 
 {      
      char c = (char)i;      
      if (c == ' ')  {      proper.append(c);      isNextCharSpace = true;     } 
......
.....
}
}

Now compile the above program .

E:\DB2\IBM\SQLLIB\java\jdk\bin> javac initCaps.java

You can have more function in the same java file. Please ensure the java file is compiled and running using the same java version.

Covert the class file to Jar file by using the following command

jar cf myjar6.jar initCaps.class

Now register the jar file in DB2 using the following steps

Open the DB2 command prompt , then run the following command

CALL SQLJ.INSTALL_JAR('file:E:\DB2\IBM\SQLLIB\java\jdk\bin\myjar6.jar',testjar);

      By running the above command, the testjar.jar is file is copied to E:\DB2\IBM\SQLLIB\FUNCTION\jar\Schema Name\

Now Create a user-defined function in Db2 using Create Function

CREATE FUNCTION firstCaps(source varchar(3000)) RETURNS varchar(3000) LANGUAGE JAVA DETERMINISTIC NO SQL NOT FENCED EXTERNAL NAME 'testjar:initCaps!firstCaps' PARAMETER STYLE JAVA NO EXTERNAL ACTION

              where firstCaps is the function name which accepts string variable and returns a string variable. testjar is the jarfile name. initCaps is the Class file name. Again firstCaps used in External Name is the Java Method name. The string variable passed to the firstCaps function is passed to Java Method then processed and returns the Proper case of the string.

Now let us write the SQL query using the above user defined function.

select SchemaName.firstCaps(empname) from emp

Some of the Trouble shooting :

         You may get the error : SQL4301N Java or .NET interpreter startup or communication failed, reason code "0". SQLSTATE=58004

          Please check JDK_PATH by the command

In windows :             db2 get dbm cfg | find "JDK".
In Linux :               db2 get dbm cfg | grep -i JDK


The JDK_PATH is one level up to bin directory. So Update the path using the following command.

db2 update dbm cfg using JDK_PATH = E:\DB2\IBM\SQLLIB\java\jdk

           If the above error with reaon code "4" means you can check JAVA_HEAP_SZ. It should be enough to handle the User Defined function / Stored Procedure. 512 may be enough to handle. You can change the value using the following command.

db2 update dbm cfg using JAVA_HEAP_SZ 4096

Java stored procedure or user-defined function could not load Java class reason code "1". SQLSTATE=42724

Class Name mentioned in the Create Function may be mis-spelled. This error may be generated using java.lang.UnsupportedClassError. You have to ensure you are compiling and running the java class file using the same jdk version. Error may be seen E:\DB2\IBM\SQLLIB\DB2\db2dialog.log.

If No errors, then the statement select SchemaName.firstCaps(empname) from emp will give the output. Suppose the database having the employee names akash kumar, Micky jen, jackson Durai.

The output will be

Akash Kumar

Micky Jen

Jackson Durai