User Defined Functions in DB2
by Rosmarie Peter, Trivadis AG
User Defined Functions (UDFs) enable users to write their own functions which can be used in SQL
statements. This article describes how this option can be used for «DB2 UDB for z/OS and OS/390».
The difference between UDFs for DB2 mainframe installations and DB2 in Linux, Unix and the
Windows environment is small and is mostly restricted to differences in the operating systems.
Current practice is to develop UDFs and stored procedures for both environments. This article focuses
on those issues where the external UDFs differ from the stored procedures (SFs).
1.1 What are functions?
Functions vastly enhance the power of SQL. They are invoked with SQL language elements, in other
words, from within the SELECT clause, the FROM clause, or the WHERE clause, depending on the
type of function.
Functions can be classified differently:
Built-in functions: These are called built-in since they are incorporated in the supplied DB2
code. Examples of built-in functions are MAX and SUBSTR.
User Defined Functions (UDFs): These are functions written by the user which can be used in
SQL statements. They are written by customers, or by IBM itself. Examples of UDFs supplied
by IBM are the MQ functions, or the functions included in extenders.
Functions can also be classified in another way:
The input for Column functions is a collection of column values; they return a single value.
Examples are SUM, MIN, MAX. They are used in the SELECT clause. In the WHERE clause,
these functions must be embedded in subselects. Users cannot write their own new column
functions. They can only be made available as sourced UDFs, typically for User Defined
Scalar functions have one or more input values as function parameters. The function returns a
scalar value. SUBSTR is an example of this type of function. Scalar functions can be used in
the SELECT or WHERE clause wherever a single value is permitted. Most built