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
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.
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:
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);
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.