This CREATE FUNCTION (SQL table) statement creates an SQL table function at the current server. The function returns a single result table.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization id of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must also include at least one of the following:
If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
To replace an existing function, the privileges
held by the authorization ID of the statement must include at least
one of the following:

The system authority *READ to the SYSFUNCS catalog
view and SYSPARMS catalog table
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.
>>-CREATE--+------------+--FUNCTION--function-name--------------> '-OR REPLACE-' >--(--+---------------------------+--)--------------------------> | .-,---------------------. | | V | | '---parameter-declaration-+-' .-,-----------------------. V | >--RETURNS TABLE--(----column-name--data-type2-+--)-------------> >--option-list--+----------------------+--SQL-routine-body----->< '-SET OPTION-statement-' parameter-declaration |--parameter-name--data-type1-----------------------------------| data-type1, data-type2 |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+-------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+----------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+-------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+-----------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+---------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+------------------------------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID----------------------------------------------------------------------------------------------+ '---XML------------------------------------------------------------------------------------------------' ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------|
option-list .-LANGUAGE SQL-. (1) |--+--------------+------+-------------------------+------------> '-SPECIFIC--specific-name-' .-NOT DETERMINISTIC . .-EXTERNAL ACTION----. >--+------------------------+--+--------------------+-----------> '-DETERMINISTIC----------' '-NO EXTERNAL ACTION-' .-READS SQL DATA----. .-CALLED ON NULL INPUT ------. >--+-------------------+--+----------------------------+--------> +-CONTAINS SQL------+ '-RETURNS NULL ON NULL INPUT-' '-MODIFIES SQL DATA-' .-INHERIT SPECIAL REGISTERS-. .-STATIC DISPATCH-. >--+---------------------------+--+-----------------+-----------> .-FENCED-----. >--+------------+--+-------------------+------------------------> '-NOT FENCED-' +-ALLOW PARALLEL----+ '-DISALLOW PARALLEL-' >--+---------------------------------------------------------------+--> | .-DEFAULT---------------------. | '-CONCURRENT ACCESS RESOLUTION--+-+-USE CURRENTLY COMMITTED-+-+-' | '-U-----------------------' | '-+-WAIT FOR OUTCOME-+--------' '-W----------------' >--+----------------------+-------------------------------------| '-CARDINALITY--integer-'
OR REPLACE
Specifies to replace the definition
for the function if one exists at the current server. The existing
definition is effectively dropped before the new definition is replaced
in the catalog with the exception that privileges that were granted
on the function are not affected. This option is ignored if a definition
for the function does not exist at the current server. To replace
an existing function, the specific-name and function-name of
the new definition must be the same as the specific-name and function-name of
the old definition, or the signature of the new definition must match
the signature of the old definition. Otherwise, a new function is
created.
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.
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.
Assume the number of parameters is N. There must be no more than (247-(N*2))/2 columns.
You can specify any built-in data type (except LONG VARCHAR, or LONG VARGRAPHIC) or a distinct type. When the function is invoked the results are assigned to these data types (using storage assignment rules).
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).
NO EXTERNAL ACTION functions might perform better than EXTERNAL ACTION functions because they might not be invoked for each successive function invocation.
The default is READS SQL DATA.
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 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.
The default is DISALLOW PARALLEL if one or more of the following clauses are specified: NOT DETERMINISTIC, EXTERNAL ACTION, or MODIFIES SQL DATA. Otherwise, ALLOW PARALLEL is the default.
See the descriptions of NOT DETERMINISTIC, EXTERNAL ACTION, and MODIFIES SQL DATA for considerations that apply to specification of ALLOW PARALLEL.
CONCURRENT ACCESS RESOLUTION
Specifies whether the database manager should wait for data that
is in the process of being updated. DEFAULT is the default.
If the CARDINALITY clause is not specified for a table function, the database manager will assume a finite value as a default.
SET OPTION DBGVIEW = *SOURCE
The default values for the options depend the options
in effect at create time.
For more information, see SET OPTION. The options CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE FUNCTION statement. CLOSQLCSR(*ENDACTGRP) is always used for SQL table 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.
ALTER PROCEDURE (SQL), ALTER FUNCTION (SQL Scalar), and ALTER FUNCTION (SQL Table) with a REPLACE keyword are not allowed in an SQL-routine-body.
General considerations for defining user-defined functions: See SQL control statements for general information about defining user-defined functions.
Function ownership: If SQL names were specified:
If system names were specified, the owner of the function is the user profile or group user profile of the job executing the statement.
Function authority: If SQL names are used, functions are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, functions are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.
If the owner of the function is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the function.


Creating the function: When an SQL function is created, the database manager creates a temporary source file that will contain C source code with embedded SQL statements. A *SRVPGM object is then created using the CRTSRVPGM command. The SQL options used to create the service program are the options that are in effect at the time the CREATE FUNCTION statement is executed. The service program is created with ACTGRP(*CALLER).
The specific name is used to determine the name of the source file member and *SRVPGM object. If the specific name is a valid system name, it will used as the name of member and program. If the member already exists, it will be overlaid. If a program already exists in the specified library, a unique name is generated using the rules for generating system table names. If the specific name is not a valid system name, a unique name is generated using the rules for generating system table names.
The function's attributes are saved in the associated service program object. If the *SRVPGM object is saved and then restored to this or another system, the catalogs are automatically updated with those attributes.
During restore of the function:
Invoking the function: When an SQL function is invoked, it runs in the activation group of the calling program.
Dependent objects: An SQL routine is dependent on objects that are referenced in the SQL-routine-body. The names of the dependent objects are stored in catalog view SYSROUTINEDEP. If the object reference in the SQL-routine-body is a fully qualified name or, in SQL naming, if an unqualified name is qualified by the current schema, then the schema name of the object in SYSROUTINEDEP will be set to the specified name or the value of the current schema. Otherwise, the schema name is not set to a specific schema name. If a name is not set to a specific schema name, then DROP and ALTER statements will not be able to determine whether the routine is dependent on the object being altered or dropped.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
Define a table function that returns the employees in a specified department number.
CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3)) RETURNS TABLE (EMPNO CHAR(6), LASTNAME VARCHAR(15), FIRSTNAME VARCHAR(12)) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC DISALLOW PARALLEL RETURN SELECT EMPNO,LASTNAME,FIRSTNME FROM EMPLOYEE WHERE EMPLOYEE.WORKDEPT =DEPTEMPLOYEES.DEPTNO