Description

function-name
Names the user-defined function. The combination of name, schema name, the number of parameters, and the data type of each parameter (without regard for any length, precision, scale, or CCSID attributes of the data type) must not identify a user-defined function that exists at the current server.

For SQL naming, the function will be created in the schema specified by the implicit or explicit qualifier.

For system naming, the function will be created in the schema that is specified by the qualifier. If no qualifier is specified:

In general, more than one function can have the same name if the function signature of each function is unique.

Certain function names are reserved for system use. For more information see Choosing the Schema and Function Name.

(parameter-declaration,...)
Specifies the number of parameters of the function and the data type of each parameter. Although not required, you can give each parameter a name.

The maximum number of parameters allowed is 90.

parameter-name
Names the parameter. The name is used to refer to the parameter within the body of the function. The name cannot be the same as any other parameter-name in the parameter list.
data-type1
Specifies the data type of the input parameter. The data type can be a built-in data type or a distinct data type.
built-in-type
Specifies a built-in data type. For a more complete description of each built-in data type, see CREATE TABLE.
distinct-type-name
Specifies a distinct type. The length, precision, or scale attributes for the parameter are those of the source type of the distinct type (those specified on CREATE DISTINCT TYPE). For more information on creating a distinct type, see CREATE DISTINCT TYPE.

If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.

If a CCSID is specified, the parameter will be converted to that CCSID prior to passing it to the function. If a CCSID is not specified, the CCSID is determined by the default CCSID at the current server at the time the function is invoked.

RETURNS TABLE
Specifies the output table of the function.

Assume the number of parameters is N. There must be no more than (247-(N*2))/2 columns.

column-name
Specifies the name of a column of the output table. Do not specify the same name more than once.
data-type2
Specifies the data type and attributes of the output.

You can specify any built-in data type (except LONG VARCHAR, or LONG VARGRAPHIC) or a distinct type.

If a CCSID is specified and the CCSID of the return data is encoded in a different CCSID, the data is converted to the specified CCSID.

If a CCSID is not specified the return data is converted to the CCSID of the job (or associated graphic CCSID of the job for graphic string return values), if the CCSID of the return data is encoded in a different CCSID. To avoid any potential loss of characters during the conversion, consider explicitly specifying a CCSID that can represent any characters that will be returned from the function. This is especially important if the data type is graphic string data. In this case, consider using CCSID 1200 or 13488 (Unicode graphic string data).

LANGUAGE SQL
Specifies that this is an SQL function.
SPECIFIC specific-name
Provides a unique name for the function. The name is implicitly or explicitly qualified with a schema name. The name, including the schema name, must not identify the specific name of another function or procedure that exists at the current server. If unqualified, the implicit qualifier is the same as the qualifier of the function name. If qualified, the qualifier must be the same as the qualifier of the function name.

If specific name is not specified, it is set to the function name. If a function or procedure with that specific name already exists, a unique name is generated similar to the rules used to generate unique table names.

DETERMINISTIC or NOT DETERMINISTIC
Specifies whether the function is deterministic.
NOT DETERMINISTIC
Specifies that the function will not always return the same result from successive function invocations with identical input arguments. NOT DETERMINISTIC should be specified if the function contains a reference to a special register, a non-deterministic function, or a sequence.
DETERMINISTIC
Specifies that the function will always return the same result from successive invocations with identical input arguments.
EXTERNAL ACTION or NO EXTERNAL ACTION
Specifies whether the function contains an external action.
EXTERNAL ACTION
The function performs some external action (outside the scope of the function program). Thus, the function must be invoked with each successive function invocation. EXTERNAL ACTION should be specified if the function contains a reference to another function that has an external action.
NO EXTERNAL ACTION
The function does not perform an external action. It need not be called with each successive function invocation.
CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA
Specifies whether the function can execute any SQL statements and, if so, what type. The database manager verifies that the SQL issued by the function is consistent with this specification. See Appendix B. Characteristics of SQL statements for a detailed list of the SQL statements that can be executed under each data access indication.
CONTAINS SQL
The function does not execute SQL statements that read or modify data.
READS SQL DATA
The function does not execute SQL statements that modify data.
MODIFIES SQL DATA
The function can execute any SQL statement except those statements that are not supported in any function.
RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT
Specifies whether the function is called if any of the input arguments is null at execution time.
RETURNS NULL ON INPUT
Specifies that the function is not invoked if any of the input arguments is null. The result is the null value.
CALLED ON NULL INPUT
Specifies that the function is to be invoked, if any, or all, argument values are null, making the function responsible for testing for null argument values. The function can return a null or nonnull value.
INHERIT SPECIAL REGISTERS
Specifies that existing values of special registers are inherited upon entry to the function.
STATIC DISPATCH
Specifies that the function is dispatched statically. All functions are statically dispatched.
FENCED or NOT FENCED
Specifies whether the SQL function runs in an environment that is isolated from the database manager environment.
FENCED
The function will run in a separate thread.

FENCED functions cannot keep SQL cursors open across individual calls to the function. However, the cursors in one thread are independent of the cursors in any other threads which reduces the possibility of cursor name conflicts.

NOT FENCED
The function may run in the same thread as the invoking SQL statement.

NOT FENCED functions can keep SQL cursors open across individual calls to the function. Since cursors can be kept open, the cursor position will also be preserved between calls to the function. However, cursor names may conflict since the UDF is now running in the same thread as the invoking SQL statement and other NOT FENCED UDFs.

NOT FENCED functions usually perform better than FENCED functions.

DISALLOW PARALLEL
Specifies that the function cannot be run in parallel. Table functions cannot run in parallel.
CARDINALITY integer
This optional clause provides an estimate of the expected number of rows to be returned by the function for optimization purposes. Valid values for integer range from 0 to 2 147 483 647 inclusive.

If the CARDINALITY clause is not specified for a table function, the database manager will assume a finite value as a default.

SET OPTION-statement
Specifies the options that will be used to create the function. For example, to create a debuggable function, the following statement could be included:
SET OPTION DBGVIEW = *SOURCE 
For more information, see SET OPTION.

The options CLOSQLCSR, CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE FUNCTION statement.

SQL-routine-body
Specifies a single SQL statement, including a compound statement. See SQL control statements for more information about defining SQL functions.

A call to a procedure that issues a CONNECT, SET CONNECTION, RELEASE, DISCONNECT, COMMIT, ROLLBACK and SET TRANSACTION statement is not allowed in a function.

If the SQL-routine-body is a compound statement, it must contain exactly one RETURN statement and it must be executed when the function is called.