This CREATE FUNCTION (SQL Scalar) statement creates an SQL function at the current server. The function returns a single result.
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-' >--(--+---------------------------+--)--RETURNS -data-type2-----> | .-,---------------------. | | V | | '---parameter-declaration-+-' >--option-list--+----------------------+--SQL-routine-body----->< '-SET OPTION-statement-' parameter-declaration |--parameter-name--data-type1-----------------------------------| data-type |--+-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----------------'
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 is 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.
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).
A function that is not deterministic might return incorrect results if the function is executed by parallel tasks. Specify the DISALLOW PARALLEL clause for these functions.
NOT DETERMINISTIC should be specified if the function contains a reference to a special register, a non-deterministic function, or a sequence.
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.
SET OPTION DBGVIEW = *SOURCE
The default values for the options depend the options
in effect at create time.
For information about the , see SET OPTION. The options CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE FUNCTION statement.
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 at least one RETURN statement and a RETURN statement 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: For general information about defining user-defined functions, see CREATE FUNCTION.
SQL path and function resolution: Resolution of function invocations inside the function body is done according to the SQL path that is in effect for the CREATE FUNCTION statement and does not change after the function is created.
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.
If a function is specified in the select-list of a select-statement and if the function specifies EXTERNAL ACTION or MODIFIES SQL DATA, the function will only be invoked for each row returned. Otherwise, the UDF may be invoked for rows that are not selected.
Inline functions: In cases of very
simple SQL functions, instead of invoking the function as part of
a query, the expression in the RETURN statement of the function
may be copied (inlined) into the query itself. Such a function is
called an inline function. A function is an inline function if:
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:
Example 1: Define a scalar function that returns the tangent of a value using the existing SIN and COS built-in functions.
CREATE FUNCTION TAN
(X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(X)/COS(X)
Notice that a parameter name (X) is specified for the input parameter to function TAN. The parameter name is used within the body of the function to refer to the input parameter. The invocations of the SIN and COS functions, within the body of the TAN user-defined function, pass the parameter X as input.
Example 2: Define a scalar function that returns a date formatted as mm/dd/yyyy followed by a string of up to 3 characters:
CREATE FUNCTION BADPARM
(INP1 DATE,)
USA VARCHAR(3))
RETURNS VARCHAR(20)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN CHAR(INP1,USA)CONCAT USA
Assume that the function is invoked as in the following statement:
SELECT BADPARM(BIRTHDATE,'ISO')
FROM EMPLOYEE WHERE EMPNO='000010'
The result is '08/24/1933ISO'. Notice that parameter names (INP1 and USA) are specified for the input parameters to function BADPARM. Although there is an input parameter named USA, the instance of USA in the parameter list for the CHAR function is taken as the keyword parameter for the built-in CHAR function and not the parameter named USA.