The CREATE FUNCTION Statement

There are basically three different types of UDFs. SQL functions, as the name implies, are written in SQL PL. The function can be coded to return a scalar value, a single row (LUW only), or a table of data (LUW and iSeries only). External functions are defined in the database with references to object code libraries that are written in other languages such as C or Java. Sourced functions are registered functions that reference to another built-in or user-defined function. They are useful for supporting existing functions with user-defined types. This book covers only SQL functions.

Note

Differences in the CREATE FUNCTION statement for iSeries and zSeries platforms are discussed at the end of this chapter.

Like most database objects in DB2, SQL functions are created with a CREATE statement. Complete syntax of the CREATE FUNCTION statement is shown in Figure 9.1.

Figure 9.1. The CREATE FUNCTION statement syntax for LUW.

 >>-CREATE FUNCTION--function-name-------------------------------> >--(--+--------------------------------+--)--*------------------>       | .-,--------------------------. |       | V                            | |       '---parameter-name--data-type--+-' >--RETURNS--+-data-type------------------+--*------------------->             '-+-ROW---+--| column-list |-'               '-TABLE-'                                    .-LANGUAGE SQL-. >--+-------------------------+--*--+--------------+--*---------->    '-SPECIFIC--specific-name-'    .-NOT DETERMINISTIC-.     .-EXTERNAL ACTION----. >--+-------------------+--*--+--------------------+--*---------->    '-DETERMINISTIC-----'     '-NO EXTERNAL ACTION-'    .-READS SQL DATA---------.     .-STATIC DISPATCH-. >--+------------------------+--*--+-----------------+--*-------->    +-CONTAINS SQL-----------+    |                        |    '-MODIFIES SQL DATA------'    .-CALLED ON NULL INPUT-.     .-INHERIT SPECIAL REGISTERS-. >--+----------------------+--*--+---------------------------+---> >--*--+----------------------------------------------------+---->       |                                                    |       '-PREDICATES--(--| predicate-specification |--)-------' >--| SQL-function-body |--------------------------------------->< column-list:       .-,-----------------------.       V                         | |--(----column-name--data-type--+--)----------------------------| SQL-function-body: |--+-RETURN Statement-----------+-------------------------------|    '-dynamic-compound-statement-'

Function Name and Parameters

The function name specified in the CREATE FUNCTION statement is limited to 18 characters on LUW and 128 characters on iSeries and zSeries. It must begin with a letter, followed by zero or more letters, digits, or underscore characters (_).

A function is said to be qualified if it is referenced by a two-part name that is made up of a schema and a function name. The restrictions for schema name on each platform include the following:

On LUW, the schema of a user-defined function cannot begin with SYS
On iSeries, the schema name cannot be QSYS2, QSYS, QTEMP, or SYSIBM.
On zSeries, the schema name can begin with SYS only if the schema is SYSADM or SYSTOOLS, or if the user issuing the statement has SYSADM or SYSCTRL privileges.

Unqualified functions are resolved in the same manner as the unqualified procedures. Refer to Chapter 2, "Basic SQL Procedure Structure," for details.

To uniquely identify a function, DB2 relies on the function signature. A function signature is a combination of a schema name, a function name, a list of parameters, and the data types of the parameters. DB2 allows all data types to be used in SQL UDFs, with the exception of LONG VARCHAR and LONG VARGRAPHIC on LUW.

If any parameter is to be used for a UDF, you need to specify the parameter name and its data type inside the parentheses. Use a comma to separate multiple parameters. Figure 9.2 shows an example of functions with zero or more parameters.

Figure 9.2. Examples of functions with zero or more parameters.

 CREATE FUNCTION functest(). . . CREATE FUNCTION prod.sum_abc(p_type INTEGER, p_value DECIMAL). . . CREATE FUNCTION test.getsalary(p_name VARCHAR(10), p_age SMALLINT). . .

It is valid to define more than one function with the same name in a database as long as they have unique function signatures. For example, two functions with the same name in the same schema are unique if the number of parameters or the data types of the parameters are different. These functions are known as overloaded functions. Figure 9.3 shows some examples of overloaded functions.

Figure 9.3. Examples of overloaded functions.

 CREATE FUNCTION db2admin.functest(v_int INT, v_vc VARCHAR(100)) ... CREATE FUNCTION db2admin.functest(v_double DOUBLE, v_vc VARCHAR(100)) ... CREATE FUNCTION db2admin.functest(v_vc VARCHAR(100), v_ts TIMESTAMPS) ... CREATE FUNCTION db2admin.functest(v_vc VARCHAR(100), v_ts TIMESTAMPS, v_si SMALLINT) ...

Functions with the same number of parameters and the same data types of the parameters are considered to have the same signature, even if the length or the precision of the parameters are different:

 CREATE FUNCTION DB2ADMIN.FUNCTEST(V_VC VARCHAR(100))... CREATE FUNCTION DB2ADMIN.FUNCTEST(V_VC VARCHAR(200))...

These functions are considered to have the same signature, even though one uses VARCHAR(100) and the other uses VARCHAR(200). They cannot be overloaded.

As you may have noticed, the functions are overloaded differently from the procedures. While functions with different number of parameters or with the same number of parameters but different parameter data types can both be overloaded, only procedures with different number of parameters can be overloaded. Refer to Chapter 2, "Basic SQL Procedure Structure," for more details on overloaded procedures.

Note

Overloaded SQL functions are supported on zSeries, but overloaded SQL procedures are not.

Returns Function Output

The type of data a UDF can return is not restricted only to a single value but also a row (LUW only) or a table of data (LUW and iSeries only). If you want to return a scalar value, simply specify its data type in the RETURNS clause, like this:

 CREATE FUNCTION functest () RETURNS INTEGER . . .

For row and table functions specify the ROW or TABLE keyword in the RETURNS clause. Following that, you need to provide the name and data type of each column to be returned. Here are some, examples:

 CREATE FUNCTION functest () RETURNS ROW (name VARCHAR(10), age INTEGER) . . . CREATE FUNCTION functest () RETURNS TABLE (prod_id SMALLINT, price DECIMAL) . . .

Note

On iSeries, the DISALLOW PARALLEL clause must be specified for UDFs that return TABLE. Refer to the iSeries section at the end of this chapter for more details.

Specific Name

A specific name is used to uniquely identify a UDF, and it is particularly useful when using overloaded functions. The specific name can be used with UDFs in the same manner as with the SQL procedures. Refer to Chapter 2, "Basic SQL Procedure Structure," for more details.

Note

The specific name is supported on zSeries for UDFs, but not for SQL procedures.

Language SQL

On LUW and zSeries, the LANGUAGE SQL clause is optional, and if omitted it is assumed to be an SQL UDF. On iSeries, LANGUAGE SQL must be specified as the first clause following the parameter list and RETURNS clause.

Tip

For portability, always use LANGUAGE SQL as the first clause after RETURNS.

DETERMINISTIC or NOT DETERMINISTIC

This clause allows you to specify if the function is DETERMINISTIC or NOT DETERMINISTIC. A UDF is deterministic if it returns the same results for each invocation of identical input parameters. On the other hand, a UDF is not deterministic if the results depend on the input values and/or other values that may change, such as the current date or time. Whenever possible, identifying a function as DETERMINISTIC allows DB2 to perform additional optimizations to improve performance.

The default is NOT DETERMINISTIC, which is typically the case for most UDFs.

EXTERNAL ACTION or NO EXTERNAL ACTION

This optional clause specifies whether or not the function takes some action that changes the state of an object not managed by the database manager. By specifying NO EXTERNAL ACTION, the system can use certain optimizations that assume functions have no external impacts. The default is EXTERNAL ACTION.

CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA

This clause restricts the type of SQL statements that can be executed by the function. On top of the standard DB2 security model on database objects, this clause provides extra security control over the function body.

When CONTAINS SQL is specified, only statements that do not read or modify data are allowed in the function. Examples of such statements are DECLARE variables, SET variables, and SQL control statements.

READS SQL DATA is the default option. It can be specified if the function contains only statements that do not modify SQL data. There is one exception case where tables might not be accessible from a function even if READS SQL DATA is specified. Consider this statement:

 UPDATE EMPLOYEE SET SALARY = SALARY + BONUS (EMPNO)

BONUS is a UDF defined with READS SQL DATA. SQL statements that read from the EMPLOYEE table are not allowed in the UDF. This is due to conflict operations on the table where the statement invoking the function is trying to update EMPLOYEE and BONUS only allows READ access.

For nested UDFs (and in fact nested UDFs, triggers, and procedures), data access in the nested object is allowed to be more restrictive, but will fail if the nested object is defined with a less restrictive level.

On LUW, MODIFIES SQL DATA is supported for SQL table functions only. All SQL statements supported in the dynamic compound SQL statement block in the function body are allowed. Refer to the section later in this chapter for an introduction to dynamic compound SQL statements.

On iSeries, MODIFIES SQL DATA is supported for all SQL function types.

On zSeries, MODIFIES SQL DATA is not supported.

STATIC DISPATCH

This optional clause indicates that at function resolution time, DB2 chooses a function based on the static types (declared types) of the parameters of the function. This behavior is the default and the only value that can be specified. This clause is optional.

CALLED ON NULL INPUT

This clause indicates that the function will always be invoked even if its input parameters are null. This behavior is the default and is the only value that can be specified. This clause is optional.

Note

On iSeries, a RETURN NULL ON NULL INPUT option is supported, in addition to the CALLED ON NULL INPUT clause. It specifies that if the function is invoked with any NULL parameters, a NULL is returned.

INHERIT SPECIAL REGISTERS (LUW and iSeries Only)

The INHERIT SPECIAL REGISTERS option works the same with UDFs as with the SQL procedures. Refer to Chapter 2, "Basic SQL Procedure Structure," for more details.

PREDICATES (LUW only)

PREDICATES is an optional clause that is valid for scalar functions only. It allows the predicates of an SQL statement that will be using the function to exploit index extensions. An index extension is an index object for use with indexes that have structured type or user-defined type columns.

SQL Function Body

The function body contains the logic of the SQL function. It is made up of a RETURN statement or a dynamic compound SQL statement. The RETURN statement is used to return the result of a function. The syntax of the statement is shown in Figure 9.4.

Figure 9.4. RETURN statement syntax.

 >>-RETURN--+---------------------------------------------------+-><            +-expression----------------------------------------+            +-NULL----------------------------------------------+            '-+-----------------------------------+--fullselect-'              |      .-,-----------------------.  |              |      V                          | |              '-WITH----common-table-expression-+-'

Depending on whether the function output type is a scalar value, a row, or a table of data, different options are supported in the body of the RETURN statement.

An expression specifies a value which can be a constant, a special register, CASE expression, or another function invocation. Data type of the expression result must match what is specified in the RETURNS clause of the CREATE FUNCTION statement. An expression is supported for all three UDF types.

Unlike SQL procedures, you can return a NULL value from SQL functions. Because both the expression and NULL clauses return only a single value, they can only be specified in scalar SQL functions.

A compound statement combines one or more other SQL statements into one execution block. It is supported on LUW and iSeries only. For more information on compound statement support on LUW, refer to Appendix B, "Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows."