Java UDFs

You can write UDFs for DB2 in Java. Again, there are two parameter styles: DB2GENERAL and JAVA, where the latter follows the SQLJ Routines standard. We will focus on the latter again, because it is less proprietary, and generally has nicer syntax. All UDFs with this parameter style are static methods that have their input arguments in the method signature, and their output as the return value. For example, let's say you want to delete any leading or trailing spaces in a character column. You can define a function called TRIM that does just that as follows:

public class JavaUDF {
   public static String trim(String input) {
      return input.trim();
   }
}

After you have compiled this class, you place the JavaUDF.class file into the sqllib/function directory of the DB2 instance that will use this function, and register the function as follows:

create function trim(input varchar(100))
returns varchar(100)
fenced variant no sql no external action
language java parameter style java
external name 'JavaUDF!trim';

After you have registered the UDF, you can easily test it by calling it with a VALUES statement. Try it out. Issue the VALUES (trim(' 123 ')) statement and verify that the UDF strips the leading and trailing white space.