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 21: 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. Specifically, you can create the following types of user-defined functions:

External scalar
The function is written in a programming language and returns a scalar value. The external executable routine (package) is registered with a database server along with various attributes of the function. Each time that the function is invoked, the package executes one or more times. See CREATE FUNCTION (external scalar).
External table
The function is written in a programming language. It returns a table to the subselect from which it was started by returning one row at a time, each time that the function is started. The external executable routine (package) is registered with a database server along with various attributes of the function. Each time that the function is invoked, the package executes one or more times. See CREATE FUNCTION (external table).
Sourced
The function is implemented by invoking another function (either built-in, external, SQL, or sourced) that exists at the server. The function inherits the attributes of the underlying source function. A sourced function does not have an associated package. See CREATE FUNCTION (sourced).
SQL scalar
The function is written exclusively in SQL statements and returns a scalar value. The body of an SQL scalar function is written in the SQL procedural language (SQL PL). The function is defined at the current server along with various attributes of the function.
Db2 supports two types of SQL scalar functions, inlined and compiled:
  • Inlined SQL scalar functions contain a single RETURN statement, which returns the value of a simple expression. The function is not invoked as part of a query; instead, the expression in the RETURN statement of the function is copied (inlined) into the query itself. Therefore, a package is not generated for an inlined SQL scalar function.
  • Compiled SQL scalar functions support a larger set of functionality, including all of the SQL PL statements. A package is generated for a compiled SQL scalar function. It contains the body of the function, including control statements. It might also contain statements generated by Db2. Each time that the function is invoked, the package executes one or more times.

When a CREATE FUNCTION statement for an SQL scalar function is processed, Db2 attempts to create an inlined SQL scalar function. If the function cannot be created as an inlined function, Db2 attempts to create a compiled SQL scalar function. For more information on the syntax and rules for these types of functions, see CREATE FUNCTION (inlined SQL scalar) and CREATE FUNCTION (compiled SQL scalar).

To determine what type of SQL scalar function is created, refer to the INLINE column of the SYSIBM.SYSROUTINES catalog table.

SQL table
The function is written exclusively as an SQL RETURN statement and returns a set of rows. The body of an SQL table function is written in the SQL procedural language. The function is defined at the current server along with various attributes. The function is not invoked as part of a query. Instead, the expression in the RETURN statement of the function is copied (inlined) into the query itself. Therefore, a package is not generated for an SQL table function. See CREATE FUNCTION (SQL 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.