Java-based User-Defined Functions

This is an example of how to implement a Java-based User-Defined Function (UDF).

Define the function

CREATE FUNCTION db2admin.concatenate(VARCHAR(200))
   RETURNS VARCHAR(2000)
   SPECIFIC append
   EXTERNAL NAME 'AppendStrings.Append'
   LANGUAGE JAVA
   PARAMETER STYLE DB2GENERAL
   FINAL CALL
   EXTERNAL ACTION
;

Cautionary note

If you run the db2look command to list the DDL for the database, it will show you the following definition. If you drop your function and recreate it using this DDL, the function will no longer work. Be warned!!

CREATE FUNCTION "DB2ADMIN"."CONCATENATE"
		(
		  VARCHAR(200)
		)

		RETURNS  VARCHAR(2000)
		SPECIFIC APPEND
		EXTERNAL NAME 'AppendStrings!Append(Ljava/lang/String;Ljava/lang/String;)V'
		LANGUAGE JAVA
		PARAMETER STYLE DB2GENRL
		VARIANT
		FENCED THREADSAFE
		NOT NULL CALL
		READS SQL DATA
		EXTERNAL ACTION
		NO SCRATCHPAD
		FINAL CALL
		DISALLOW PARALLEL
		NO DBINFO;

Java-based implementation code

This sample code was created by IBM. Not sure why they didn’t use a StringBuffer, or even just a String, instead of the ScratchPad, and don’t get me started on the naming conventions. As a further aside, the scratchpad concept seems to be firmly ingrained in DB2 UDFs: the base class defines native setter/getter methods for a byte array property called scratchpad.

import java.lang.*;         // for String class
import java.io.*;

public class AppendStrings extends COM.ibm.db2.app.UDF{

private ScratchPad sp;

public void Append(
	String newLine,
	String intermResult) throws Exception
{

    switch(getCallType())
    {
      case SQLUDF_FIRST_CALL:
        // initialize data
        sp = new ScratchPad();
        sp.appendToScratchPad(newLine);
        break;
      case SQLUDF_NORMAL_CALL:
        // read data from SCRATCHPAD area
        sp.appendToScratchPad(newLine);
        break;
    }
    String out = sp.getScratchPad();
    set(2, out.substring(1,out.length()));
 }
}

class ScratchPad
{
  String sp;

  ScratchPad()
  {
    sp = "";
  }
  public String getScratchPad()
  {
    return sp;
  }

  public void appendToScratchPad(String nl)
  {
    	  sp = sp + "/" + nl;
  }

}

Telling DB2 to refresh its view of the classes

DB2 loads the classes from DB2_HOME/SQLLIB/FUNCTION. Upon subsequent file system updates, DB2 will not detect the new version of the class files until you execute the following command.

db2 CALL SQLJ.REFRESH_CLASSES()

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply