DB2GENERAL UDFs

You can create and use UDFs in Java™ just as you would in other languages. After you code the UDF, you register it with the database. You can then refer to it in your applications.

In general, if you declare a UDF taking arguments of SQL types t1, t2, and t3, returning type t4, it is called as a Java method with the expected Java signature:

     public void name ( T1 a, T2 b, T3 c, T4 d)   {.....}

For example, given a UDF called sample!test3 that returns INTEGER and takes arguments of type CHAR(5), BLOB(10K), and DATE, the database manager expects the Java implementation of the UDF to have the following signature:

     import COM.ibm.db2.app.*;
     public class sample extends UDF {
        public void test3(String arg1, Blob arg2, String arg3,
                          int result)  {... }
     }

Java routines that implement table functions require more arguments. Beside the variables that represent the input, an additional variable appears for each column in the resulting row. For example, a table function can be declared as:

     public void test4(String arg1, int result1, 
                       Blob result2, String result3);

SQL NULL values are represented by Java variables that are not initialized. These variables have a value of zero if they are primitive types, and Java null if they are object types, in accordance with Java rules. To tell an SQL NULL apart from an ordinary zero, you can call the function isNull for any input argument:

     { ....                                               
        if (isNull(1)) { /* argument #1 was a SQL NULL */ }
        else           { /* not NULL */ }
     }

In this example, the argument numbers start at one. The isNull() function, like the other functions that follow, are inherited from the COM.ibm.db2.app.UDF class.

To return a result from a scalar or table UDF, use the set() method in the UDF, as follows:

     { .... 
        set(2, value); 
     } 

Where '2' is the index of an output argument, and value is a literal or variable of a compatible type. The argument number is the index in the argument list of the selected output. In the first example, the int result variable has an index of 4; in the second, result1 through result3 have indices of 2 through 4.

Like C modules that are used in UDFs and stored procedures, you cannot use the Java standard I/O streams (System.in, System.out, and System.err) in Java routines.

All the Java class files (or the JARs that contain the classes) that you use to implement a routine must reside in the sqllib/function directory, or in a directory that is specified in the database manager's CLASSPATH.

Typically, the database manager calls a UDF many times. The UDF can be called once for each row of an input or result set in a query. If SCRATCHPAD is specified in the CREATE FUNCTION statement of the UDF, the database manager recognizes that some "continuity" is needed between successive invocations of the UDF, and therefore the implementing Java class is not instantiated for each call, but generally speaking once per UDF reference per statement. Generally it is instantiated before the first call and used thereafter, but can for table functions be instantiated more often. If, however, NO SCRATCHPAD is specified for a UDF, either a scalar or table function, then a clean instance is instantiated for each call to the UDF.

A scratchpad can be useful for saving information across calls to a UDF. While Java and OLE UDFs can either use instance variables or set the scratchpad to achieve continuity between calls, C and C++ UDFs must use the scratchpad. Java UDFs access the scratchpad with the getScratchPad() and setScratchPad() methods available in COM.ibm.db2.app.UDF.

For Java table functions that use a scratchpad, control when you get a new scratchpad instance by using the FINAL CALL or NO FINAL CALL option on the CREATE FUNCTION statement.

The ability to achieve continuity between calls to a UDF by means of a scratchpad is controlled by the SCRATCHPAD and NO SCRATCHPAD option of CREATE FUNCTION, regardless of whether the database scratchpad or instance variables are used.

For scalar functions, you use the same instance for the entire statement.

Note that every reference to a Java UDF in a query is treated independently, even if the same UDF is referenced multiple times. This is the same as what happens for OLE, C, and C++ UDFs as well. At the end of a query, if you specify the FINAL CALL option for a scalar function then the object's close() method is called. If you do not define a close() method for your UDF class, then a stub function takes over and the event is ignored.

If you specify the ALLOW PARALLEL clause for a Java UDF in the CREATE FUNCTION statement, the database manager might elect to evaluate the UDF in parallel. If the database manager elects to evaluate the UDF in parallel, several distinct Java objects can be created on different partitions. Each object receives a subset of the rows.

As with other UDFs, Java UDFs can be FENCED or NOT FENCED. NOT FENCED UDFs run inside the address space of the database engine; FENCED UDFs run in a separate process. Although Java UDFs cannot inadvertently corrupt the address space of their embedding process, they can terminate or slow down the process. Therefore, when you debug UDFs written in Java, it is suggested that you run UDFs as FENCED UDFs.

You can define an external Java table function that can output tables of different schemas based on input arguments to the function. An external Java table function that can output tables of different schema is known as a generic table function. To define an external Java generic table function, specify PARAMETER STYLE DB2GENERAL in the CREATE FUNCTION statement and declare the shape of the output table function when the external generic table function is referenced.