Writing UDFs as external functions

 

You can write the executable code of a user-defined function (UDF) in a language other than SQL.

While this method is slightly more cumbersome than an SQL function, it provides the flexibility for you to use whatever language is most effective for you. The executable code can be contained in either a program or service program.

External functions can also be written in Java™.

• Registering UDFs
  A user-defined function (UDF) must be registered in the database before the function can be recognized and used by SQL.

• Passing arguments from DB2 to external functions
  DB2^® provides storage for all parameters that are passed to a user-defined function (UDF). Therefore, parameters are passed to an external function by address.

• Table function considerations
  An external table function is a user-defined function (UDF) that delivers a table to the SQL statement in which it is referenced. A table function reference is valid only in a FROM clause of a SELECT statement.

• Error processing for UDFs
  When an error occurs in processing a user-defined function (UDF), the system follows a specified model to handle the error.

• Threads considerations
  A user-defined function (UDF) that is defined as FENCED runs in the same job as the SQL statement that calls the function. However, the UDF runs in a system thread, separate from the thread that is running the SQL statement.

• Parallel processing
  A user-defined function (UDF) can be defined to allow parallel processing.

• Fenced or unfenced considerations
  When you create a user-defined function (UDF), consider whether to make the UDF an unfenced UDF.

• Save and restore considerations
  When an external function associated with an ILE external program or service program is created, an attempt is made to save the attributes of the function in the associated program or service program object.

 

Parent topic:

Using user-defined functions

 

Related concepts

Java SQL routines