CREATE FUNCTION (SQL scalar)

This CREATE FUNCTION (SQL scalar) statement creates an SQL function at the current server. The function returns a single result.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
Database administrator authority

The privileges held by the authorization id of the statement must include at least one of the following:

For the SYSFUNCS catalog view and SYSPARMS catalog table:
- The INSERT privilege on the table, and
- The USAGE privilege on schema QSYS2
Database administrator authority

The privileges held by the authorization ID of the statement must also include at least one of the following:

The following system authorities:
- *USE to the Create Service Program (CRTSRVPGM) command or
Database administrator authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The USAGE privilege on the schema containing the distinct type
Database administrator authority

If the SECURED attribute is specified, or the function is secure and OR REPLACE is specified:

The authorization ID of the statement must have security administrator authority. See Administrative authority.

To replace an existing function, the privileges held by the authorization ID of the statement must include at least one of the following:

The following system authorities:
- The system authority of *OBJMGT on the service program object associated with the function
- All authorities needed to DROP the function
- The system authority *READ to the SYSFUNCS catalog view and SYSPARMS catalog table
Database administrator authority

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.

Syntax

function-definition

parameter-declaration

data-type1, data-type2

built-in-type

ccsid-clause

normalize-clause

default-clause

option-list

Notes:

¹ This clause and the clauses that follow in the option-list can be specified in any order. Each clause can be specified at most once.

SQL-routine-body

Description

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.

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 unless OR REPLACE is specified.

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:

If the value of the CURRENT SCHEMA special register is *LIBL, the function will be created in the current library (*CURLIB).
Otherwise, the function will be created in the current schema.

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 in CREATE FUNCTION.

(parameter-declaration,...)

Specifies the number of input parameters of the function and the data type of each parameter. Each parameter-declaration specifies an input parameter for the function. A maximum of 2000 parameters can be specified. A function can have zero or more input parameters. There must be one entry in the list for each parameter that the function expects to receive. All the parameters for a function are input parameters and are nullable. For more information, see Defining the parameters in CREATE FUNCTION.

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 TYPE). For more information about creating a distinct type, see CREATE TYPE (distinct).
If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.
array-type-name: Specifies an array type.
If the name of the array 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.

A parameter that is an array of XML or LOB type is read only.

default-clause

Specifies a default value for the parameter. The default can be a constant, a special register, a global variable, an expression, or the keyword NULL. The expression is any expression defined in Expressions, that does not include an aggregate function or column name. If a default value is not specified, the parameter has no default and cannot be omitted on invocation. The maximum length of the expression string is 64K.

The default expression must be assignment compatible to the parameter data type.

Any comma in the default expression that is intended as a separator of numeric constants in a list must be followed by a space.

All objects referenced in a default expression must exist when the function is created.

A default cannot be specified for a parameter of type array.

RETURNS

Specifies the result of the function.

data-type2

Specifies the expression that is to be returned for the function. The result data type of the expression must be assignable (using storage assignment rules) to the data type that is defined in the RETURNS clause. For more information, see Assignments and comparisons.

You can specify any built-in data type (except LONG VARCHAR, or LONG VARGRAPHIC), a distinct type, or an array 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

Specifies a unique name for the function. For more information on specific names, see Specifying a specific name for a function in CREATE FUNCTION.

PROGRAM NAME external-program-name

Specifies the unqualified name of the service program to be created for the function. external-program-name must be a valid system name. End of change

GLOBAL DETERMINISTIC or STATEMENT DETERMINISTIC or NOT DETERMINISTIC

Specifies whether the function returns the same results each time that the function is invoked with the same input arguments. The default is NOT DETERMINISTIC.

NOT DETERMINISTIC

Specifies that the function might not return the same result each time that the function is invoked with the same input arguments. The function depends on some state values that affect the results. The database manager uses this information during optimization of SQL statements. An example of a function that is not deterministic is one that generates random numbers.

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.

GLOBAL DETERMINISTIC

Specifies that the function always returns the same result each time that the function is invoked with the same input arguments. The database manager uses this information during optimization of SQL statements. The query optimizer may choose to cache global deterministic scalar function results.¹ An example of a global deterministic function is a function that calculates the square root of the input argument.

STATEMENT DETERMINISTIC

Specifies that the function might not return the same result each time that the function is invoked with the same input arguments, but multiple invocations of the function within a single SQL statement are considered deterministic. The query optimizer will not cache statement deterministic scalar function results.² An example of a statement deterministic function is a function that performs currency conversion.

EXTERNAL ACTION or NO EXTERNAL ACTION

Specifies whether the function takes an action that changes the state of an object that the database manager does not manage. An example of an external action is sending a message or writing a record to a stream file. The default is EXTERNAL ACTION.

EXTERNAL ACTION: Specifies that the function can take an action that changes the state of an object that the database manager does not manage. Thus, the function must be invoked with each successive function invocation. EXTERNAL ACTION should be specified if the function contains a reference to another routine 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.
NO EXTERNAL ACTION functions might perform better than EXTERNAL ACTION functions because they might not be invoked for each successive function invocation.

CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA

Specifies the classification of SQL statements and nested routines that the function can execute. The database manager verifies that the SQL statements issued by the function, and all routines locally invoked by the function, are consistent with this specification. The verification is not performed when nested remote routines are invoked. For the classification of each statement, see Characteristics of SQL statements. The default is READS SQL DATA. This option applies to any parameter default expressions.

READS SQL DATA: Specifies that the function can execute statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL. The function cannot execute SQL statements that modify data.
CONTAINS SQL: Specifies that the function can execute only SQL statements with a data access classification of CONTAINS SQL or NO SQL. The function cannot execute any SQL statements that read or 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. This specification means that the function must be coded to test 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.

DISALLOW DEBUG MODE, ALLOW DEBUG MODE, or DISABLE DEBUG MODE

Indicates whether the function is created so it can be debugged by the Unified Debugger. If DEBUG MODE is specified, a DBGVIEW option in the SET OPTION statement must not be specified.

DISALLOW DEBUG MODE: The function cannot be debugged by the Unified Debugger. When the DEBUG MODE attribute of the function is DISALLOW, the function can be subsequently altered to change the debug mode attribute.
ALLOW DEBUG MODE: The function can be debugged by the Unified Debugger. When the DEBUG MODE attribute of the function is ALLOW, the function can be subsequently altered to change the debug mode attribute.
DISABLE DEBUG MODE: The function cannot be debugged by the Unified Debugger. When the DEBUG MODE attribute of the function is DISABLE, the function cannot be subsequently altered to change the debug mode attribute.

If FENCED or ALLOW PARALLEL is specified for the function, the DEBUG MODE option will be ignored. DISALLOW DEBUG MODE will be used.

If DEBUG MODE is not specified, but a DBGVIEW option in the SET OPTION statement is specified, the function cannot be debugged by the Unified Debugger, but may be debugged by the system debug facilities. If neither DEBUG MODE nor a DBGVIEW option is specified, the debug mode used is from the CURRENT DEBUG MODE special register.

FENCED or NOT FENCED

Specifies whether the SQL function runs in an environment that is isolated from the database manager environment. FENCED is the default.

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.

ALLOW PARALLEL or DISALLOW PARALLEL

Specifies whether the function can be run in parallel.

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.

ALLOW PARALLEL: Specifies that the database manager can consider parallelism for the function. The database manager is not required to use parallelism on the SQL statement that invokes the function or on any SQL statement issued from within the function.
See the descriptions of NOT DETERMINISTIC, EXTERNAL ACTION, and MODIFIES SQL DATA for considerations that apply to specification of ALLOW PARALLEL.
DISALLOW PARALLEL: Specifies that the database manager must not use parallelism for the function.

CONCURRENT ACCESS RESOLUTION

Specifies whether the database manager should wait for data that is in the process of being updated. DEFAULT is the default.

DEFAULT: Specifies that the concurrent access resolution is not explicitly set for this function. The value that is in effect when the function is invoked will be used.
WAIT FOR OUTCOME: Specifies that the database manager is to wait for the commit or rollback of data in the process of being updated.
USE CURRENTLY COMMITTED: Specifies that the database manager is to use the currently committed version of the data when encountering data that is in the process of being updated.; When the lock contention is between a read transaction and a delete or update transaction, the clause is applicable to scans with isolation level CS (but not for CS KEEP LOCKS).

SYSTEM_TIME SENSITIVE

Determines whether references to system-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. YES is the default.

YES: References to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
NO: References to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.

NOT SECURED or SECURED

Specifies whether the function is considered secure for row access control and column access control.

NOT SECURED: Specifies that the function is considered not secure for row access control and column access control. This is the default.; When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled when the table is using active column access control.
SECURED: Specifies that the function is considered secure for row access control and column access control.; A function must be defined as secure when it is referenced in a row permission or a column mask.

WRAPPED obfuscated-statement-text

Specifies the encoded definition of the function. A CREATE FUNCTION statement can be encoded using the WRAP scalar function.

SET OPTION-statement

Specifies the options that will be used to create the function. These options also apply to any default value expressions. For example, to create a debuggable function, the following statement could be included:

SET OPTION DBGVIEW = *SOURCE

The default values for the options depend on the options in effect at create time. For information about the , see SET OPTION.

The options CNULRQD, CNULIGN, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE FUNCTION statement. The following options are used when processing default value expressions: ALWCPYDTA, CONACC, DATFMT, DATSEP, DECFLTRND, DECMPT, DECRESULT, DFTRDBCOL, LANGID, SQLCURRULE, SQLPATH, SRTSEQ, TGTRLS, TIMFMT, and TIMSEP.

SQL-routine-body

Specifies a single SQL-procedure-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 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.

Notes

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 a user profile with the same name as the schema into which the function is created exists, the owner of the function is that user profile.
Otherwise, the owner of the function is the user profile or group user profile of the thread executing the statement.

If system names were specified, the owner of the function is the user profile or group user profile of the thread 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.

REPLACE rules: When a function is recreated by REPLACE:

Any existing comment or label is discarded.
Authorized users are maintained. The object owner could change.
Current journal auditing is preserved.

If the function is replaced and the function signature or result data type is altered, the results from any function, materialized query table, procedure, trigger, or view that references the function may be unpredictable. Any referenced objects should be recreated.

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).

When an SQL function is created, the function's attributes are stored in the created service program object. If the *SRVPGM object is saved and then restored to this or another system, the attributes are used to update the catalogs.

Start of change If the PROGRAM NAME clause is provided, its name is used for the creation of the service program object. End of change Otherwise, 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.

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: When an SQL scalar function is inlined, 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 scalar function is an inline function if:

The SQL function is global deterministic.
The SQL-routine-body contains only a RETURN statement.
No input parameter is an array type.
The data type of the result is not XML or an array type.
All objects referenced in the function exist when the function is created.
The SQL-routine-body does not contain a common table expression that references an input parameter.
The SQL-routine-body does not contain a nested table expression without a preceding LATERAL keyword that references an input parameter.

An inline function is only copied (inlined) into a query if:

The query is eligible for the SQL Query Engine (SQE).
The function references an object and the authority attributes of the function and the query are compatible based on one of the following conditions:
- The function is defined to run under the user's authority (*USER).
- The query is running under the owner's authority (*OWNER) and the owner of the query is the same as the owner of the function.
- The query is running under the user's authority (*USER), and the user or the user's group profile is the same as the owner of the function.
Note: If the function is defined as FENCED, the query must not use adopted authority. If the query runs under the owner's authority (*OWNER) and the function runs under the user’s authority (*USER), the owner of the query must be the same as the user or the user's group profile.

When a function is inlined, some of the options specified when the function was created are ignored:

PARALLEL or NOT PARALLEL
MODIFIES SQL DATA
Commitment control level
CONCURRENT ACCESS RESOLUTION
ALWCPYDTA
ATOMIC or NOT ATOMIC

If a function is inlined and it contains a reference to a special register, the value of the special register will be the same as other references to the same special register in the query.

Returning an array from a function: If the return type of a function is an array type, the function can only be invoked from within an SQL function or SQL procedure routine body in one of the following contexts:

the select-list of a SELECT INTO
the select-list of a DECLARE CURSOR
the select-list of a scalar subselect on the right side of a SET statement

A select-list that contains a function that returns an array type cannot use the DISTINCT keyword. A query that contains this select list cannot use UNION, EXCEPT, or INTERSECT.

Obfuscated statements: A CREATE FUNCTION statement can be executed in obfuscated form. In an obfuscated statement, only the function name and parameters are readable followed by the WRAPPED keyword. The rest of the statement is encoded in such a way that it is not readable but can be decoded by a database server that supports obfuscated statements. Obfuscated statements can be produced by invoking the WRAP scalar function. Any debug options that are specified when the function is created from an obfuscated statement are ignored. A function that is created from an obfuscated statement cannot be restored to a release where obfuscation is not supported.

Setting of the default value: Parameters of a function that are defined with a default value are set to their default value when the function is invoked, but only if a value is not supplied for the corresponding argument, or the argument is specified as DEFAULT.

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. Unqualified function names, variable names, and type names will have a schema name of CURRENT PATH. If the name is not set to an actual 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:

The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC and DETERMINISTIC.
The keywords NULL CALL and NOT NULL CALL can be used as synonyms for CALLED ON NULL INPUT and RETURNS NULL ON NULL INPUT.
The keywords IS DETERMINISTIC may be used as a synonym for DETERMINISTIC.

Example

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.

¹ If the result of the function contains sensitive data, consider using STATEMENT DETERMINISTIC or the DETERMINISTIC_UDF_SCOPE QAQQINI option or create the function NOT DETERMINISTIC to prevent inadvertent access to the result. For more information, see the Database Performance and Query Optimization topic collection.

² The DETERMINISTIC_UDF_SCOPE QAQQINI option can be used to get this same behavior for a GLOBAL DETERMINISTIC function. For more information, see the Database Performance and Query Optimization topic collection.