Application Development Guide

Description

This section describes how to write UDFs and methods. The coding conventions for UDFs and methods are the same, with the following differences:

• Since DB2 associates each method with a specific structured type, the first argument passed from DB2 to your method is always the instance of the structured type on which you invoked the method.
• Methods, unlike UDFs, cannot return tables. You cannot invoke a method as the argument for a FROM clause.

As the guidelines for writing UDFs and methods are the same, with the exception of the previously described difference, the remainder of the discussion on writing UDFs and methods refers to both UDFs and methods simply as UDFs.

For small UDFs such as UDFs that contain only a simple expression, consider using a SQL-bodied UDF. To create a SQL-bodied UDF, issue a CREATE FUNCTION or CREATE METHOD statement that includes a method body written using SQL, rather than pointing to an external UDF. SQL-bodied UDFs enable you to declare and define the UDF in a single step, without using an external language or compiler. SQL-bodied UDFs also offer the possibility of increased performance, because the method body is written using SQL accessible to the DB2 optimizer.

The following example demonstrates a simple CREATE FUNCTION statement that creates a SQL-bodied UDF:

   CREATE FUNCTION tan(double x)
   RETURNS double
   NO EXTERNAL ACTION
   DETERMINISTIC
   LANGUAGE SQL
   CONTAINS SQL
   RETURN sin(x) / cos(x);

For further information on SQL-bodied functions, refer to the SQL Reference.

After a preliminary discussion on the interface between DB2 and a UDF, the remaining discussion concerns how you implement UDFs. The information on writing the UDF emphasizes the presence or absence of a scratchpad as one of the primary considerations.

Some general considerations in using this section are:

• Important material on defining and using UDFs is presented in User-Defined Functions (UDFs) and Methods, and is not repeated here. This discussion concentrates on how you implement a UDF.
• To implement an external UDF written in C, C++ or Java, you must perform the following steps:
  • Write the UDF
  • Compile the UDF
  • Link the UDF
  • Register the UDF with a CREATE FUNCTION statement
  • Test and debug the UDF

  You can find information on compiling and linking UDFs in the Application Building Guide.

• You can invoke your UDF using OLE (Object Linking and Embedding) as described in Writing OLE Automation UDFs.
• You can define an OLE DB table function, which is a function that returns a table from an OLE DB data source, with just a CREATE FUNCTION statement. For more information on OLE DB table functions, see OLE DB Table Functions.

Note that a sourced UDF, which is different from an external UDF, does not require an implementation in the form of a separate piece of code. Such a UDF uses the same implementation as its source function, along with many of its other attributes.