Creating a user-defined function

You can extend the SQL functionality of DB2® by adding your own or third party vendor function definitions.

Before you begin

Set up the environment for user-defined functions, as described in Installation step 19: Configure DB2 for running stored procedures and user-defined functions.

About this task

A user-defined function is a small program that you can write to perform an operation, similar to a host language subprogram or function. However, a user-defined function is often the better choice for an SQL application because you can invoke it in an SQL statement. User-defined functions are created using the CREATE FUNCTION statement and registered to DB2 in the catalog.

A user-defined function is denoted by a function name followed by zero or more operands that are enclosed in parentheses. Like a built-in function, a user-defined function represents a relationship between a set of input values and a set of result values. The input values to a function are called parameters in the function definition. The input values to a function are called arguments when the function is invoked. For example, a function can be passed with two input arguments that have date and time data types and return a value with a timestamp data type as the result.

You can create several different types of user-defined functions, including external, SQL, and sourced user-defined functions. User-defined functions can also be categorized as scalar functions, which return a single value, or table functions, which return a table.

The environment for user-defined functions includes application address space, from which a program invokes a user-defined function; a DB2 system, where the packages from the user-defined function are run; and a WLM-established address space, where the user-defined function may be executed; as shown in the following figure.

Figure 1. The user-defined function environment
Begin figure summary.A set of boxes show the flow between the program address space, DB2, and the WLM address space. Detailed description available.

For information on Java user-defined functions, see Java stored procedures and user-defined functions. For user-defined functions in other languages, see the following instructions.

Procedure

To create a user-defined function:

  1. Write and prepare the user-defined function, as described in Writing an external user-defined function. This step is necessary only for an external user-defined function.
  2. Define the user-defined function to DB2 by issuing a CREATE FUNCTION statement that specifies the type of function that you want to create. For more information, see CREATE FUNCTION.
  3. Invoke the user-defined function from an SQL application, as described in Invoking a user-defined function.

Definition for an SQL user-defined scalar function

You can define an SQL user-defined function to calculate the tangent of a value by using the existing built-in SIN and COS functions:

CREATE FUNCTION TAN (X DOUBLE)
  RETURNS DOUBLE
  LANGUAGE SQL
  CONTAINS SQL
  DETERMINISTIC
  RETURN SIN(X)/COS(X);
The logic of the function is contained in the function definition as the following statement:
RETURN SIN(X)/COS(X)

What to do next

If you discover after you define the function that you need to change a part of the definition, you can use an ALTER FUNCTION statement to change the definition. You cannot use ALTER FUNCTION to change some of the characteristics of a user-defined function definition.