DB2 user-defined functions (UDFs)

A user-defined function (UDF) is an extension or addition to the existing built-in functions of SQL, such as those provided in the SYSFUN schema. user-defined functions and stored procedures are sometimes referred to collectively as routines.

The CREATE FUNCTION statement creates a user-defined function. After you define a user-defined function, you can refer to it in SQL statements as follows:

See the topic "Routines in application development" and related topics in the DB2 Information Center or the DB2 Application Development Guide for more detailed information on DB2 user-defined functions and how to use them in applications.

Using user-defined functions, you can add function definitions that are applied in the database engine. By adding functions to the engine, you can save the effort of retrieving rows from the database and applying similar functions on the retrieved data. UDFs let the database exploit the same engine functions that are used by applications. UDFs provide synergy between applications and the database and encourage code reuse.

See Authorities for DB2 stored procedures and user-defined functions for a list of DB2 privileges and authorities that are required to create and work with stored procedures. See the DB2 Application Development Guide, SQL Reference, and other DB2 documentation for additional restrictions and information about developing user-defined functions.

Developing DB2 user-defined functions
The workbench provides numerous features to help you develop DB2 UDFs.

Using DB2 user-defined functions in applications
After you create a DB2 user-defined function (UDF), you can refer to the function in SQL statements.

Creating DB2 user-defined functions using a wizard
You can use the New UDF wizard to create DB2 user-defined functions (UDFs) in SQL.

Creating DB2 user-defined functions for WebSphere MQ
You can create a DB2 user-defined function that reads or receives all messages from a WebSphere MQ message queue. The New WebSphere MQ User-Defined Function wizard simplifies the process of creating the user-defined function. The wizard also works with earlier versions of IBM's messaging product, known as MQSeries.

User-defined function catalog by DB2 Universal Database server
The catalog where user-defined functions are defined depends on the DB2 UDB server that you are using, as described in the following table.

User-defined function support by type
You can create several different types of UDFs (UDFs) using the workbench.

Stored procedure and user-defined function support by DB2 Universal Database server type
The following table summarizes the types of stored procedures and user-defined functions (UDFs) that are supported on various DB2 UDB servers.

Related reference
Authorities for DB2 UDB stored procedures and user-defined functions