You can create and use UDFs in Java just as you would in other languages, with only a few minor differences. After you code the UDF, you register it with the database using the CREATE FUNCTION statement. Refer to the SQL Reference for information on registering a Java UDF using this statement. You can then refer to it in the SQL of your application. The UDF can be FENCED or NOT FENCED, and you can also use options to modify how the UDF is run. See Changing How a Java UDF Runs.
Some sample Java UDF method bodies are provided in the UDFsrv.java sample. You can find the associated CREATE FUNCTION statements and examples of calling those UDFs in the UDFcli.java and UDFclie.sqlj samples. See the sqllib/samples/java directory for the samples and README instructions for compiling and running the samples.
In general, if you declare a UDF taking arguments of SQL types t1, t2, and t3, returning type t4, it will be called as a Java method with the expected Java signature:
public void name ( T1 a, T2 b, T3 c, T4 d) { .....}
Where:
For example, given a UDF called sample!test3 that returns INTEGER and takes arguments of type CHAR(5), BLOB(10K), and DATE, DB2 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 UDFs that implement table functions require more arguments. Beside the variables representing the input, an additional variable appears for each column in the resulting row. For example, a table function may 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 the above 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 in this section, the int result variable has an index of 4; in the second, result1 through result3 have indices of 2 through 4. An output argument that is not set before the UDF returns will have a NULL value.
Like C modules used in UDFs and stored procedures, you cannot use the Java standard I/O streams (System.in, System.out, and System.err) in Java UDFs. For an example of a Java UDF, see the file DB2Udf.java in the sqllib/samples/java directory.
Remember that all Java class files that you use to implement a UDF must reside in the sqllib/function directory or an appropriate subdirectory. See Where to Put Java Classes.
Typically, DB2 calls a UDF many times, 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, DB2 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 may for table functions be instantiated more often. For more information, see the NO FINAL CALL execution model in the subsection which follows this one.
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 may 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, as indicated by the execution models in Table Function Execution Model for Java.
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 DB2 scratchpad or instance variables are used.
For scalar functions, you use the same instance for the entire statement.
Please 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. For table functions the close() method will always be invoked as indicated in the subsection which follows this one. 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, DB2 may elect to evaluate the UDF in parallel. If this occurs, several distinct Java objects may 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, you should run them as FENCED UDFs.
See COM.ibm.db2.app.UDF for a description of the COM.ibm.db2.app.UDF interface. This interface describes other useful calls that you can make within a UDF, such as setSQLstate and getDBinfo.
For table functions written in Java, it is important to
understand what happens at each point in DB2's processing of a given
statement which is significant to the table function. The table which
follows details this information. The bottom part of each box hints
what the code might be written to do for a typical table function which pulls
some information in from the Web. Covered are both the NO FINAL CALL
and the FINAL CALL cases, assuming SCRATCHPAD in both cases.
| Point in scan time |
NO FINAL CALL LANGUAGE JAVA SCRATCHPAD |
FINAL CALL LANGUAGE JAVA SCRATCHPAD |
|---|---|---|
| Before the first OPEN for the table function | No calls. |
|
| At each OPEN of the table function |
|
|
| At each FETCH for a new row of table function data |
|
|
| At each CLOSE of the table function |
|
|
| After the last CLOSE of the table function | No calls. |
|
Notes: