DB2 10.5 for Linux, UNIX, and Windows

CREATE FUNCTION (SQL scalar, table, or row) statement

The CREATE FUNCTION (SQL scalar, table, or row) statement is used to define a user-defined SQL scalar, table, or row function.

A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid. A table function can be used in a FROM clause and returns a table. A row function can be used as a transform function and returns a row.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the function does not exist
  • CREATEIN privilege on the schema, if the schema name of the function refers to an existing schema
  • DBADM authority
and at least one of the following authorities on each table, view, or nickname identified in any fullselect:
  • CONTROL privilege on that table, view, or nickname
  • SELECT privilege on that table, view, or nickname
  • DATAACCESS authority

Group privileges other than PUBLIC are not considered for any table or view specified in the CREATE FUNCTION statement.

Authorization requirements of the data source for the table or view referenced by the nickname are applied when the function is invoked. The authorization ID of the connection can be mapped to a different remote authorization ID.

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the function body.

To replace an existing function, the authorization ID of the statement must be the owner of the existing function (SQLSTATE 42501).

If the SECURED option is specified, the authorization ID of the statement must include SECADM or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE--+------------+--FUNCTION--function-name-------------->
           '-OR REPLACE-'                            

>--(--+-------------------------------+--)--●------------------->
      | .-,-------------------------. |         
      | V                           | |         
      '---| parameter-declaration |-+-'         

>--RETURNS--+-| data-type2 |-----------------------------+------>
            '-+-ROW--| column-list |-------------------+-'   
              '-TABLE--+-| column-list |-------------+-'     
                       +-row-type-name---------------+       
                       +-| anchored-row-data-type |--+       
                       '-ELEMENT OF--array-type-name-'       

>--| option-list |--| SQL-function-body |----------------------><

parameter-declaration

   .-IN------.                                                           
|--+---------+--parameter-name--| data-type1 |--+--------------------+--|
   |     (1) |                                  '-| default-clause |-'   
   +-OUT-----+                                                           
   '-INOUT---'                                                           

data-type1, data-type2

|--+-| built-in-type |------+-----------------------------------|
   +-| anchored-data-type |-+   
   +-array-type-name--------+   
   +-cursor-type-name-------+   
   +-distinct-type-name-----+   
   +-REF--(--type-name--)---+   
   +-row-type-name----------+   
   '-structured-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-+-------------+-)-'          |  |              (2) | | |   
   | | |                           +-OCTETS------+              |  '-FOR BIT DATA-----' | |   
   | | |                           '-CODEUNITS32-'              |                       | |   
   | | '-+-VARCHAR----------------+--(integer-+-------------+-)-'                       | |   
   | |   '-+-CHARACTER-+--VARYING-'           +-OCTETS------+                           | |   
   | |     '-CHAR------'                      '-CODEUNITS32-'                           | |   
   | |                                  .-(1M)-----------------------------.            | |   
   | '-+-CLOB------------------------+--+----------------------------------+------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-+-------------+-)-'              |   
   |     '-CHAR------'                             +-K-+ +-OCTETS------+                  |   
   |                                               +-M-+ '-CODEUNITS32-'                  |   
   |                                               '-G-'                                  |   
   |            .-(1)------------------------.                                            |   
   +-+-GRAPHIC--+----------------------------+------+-------------------------------------+   
   | |          '-(integer-+-------------+-)-'      |                                     |   
   | |                     +-CODEUNITS16-+          |                                     |   
   | |                     '-CODEUNITS32-'          |                                     |   
   | +-VARGRAPHIC--(integer-+-------------+-)-------+                                     |   
   | |                      +-CODEUNITS16-+         |                                     |   
   | |                      '-CODEUNITS32-'         |                                     |   
   | |         .-(1M)-----------------------------. |                                     |   
   | '-DBCLOB--+----------------------------------+-'                                     |   
   |           '-(integer-+---+-+-------------+-)-'                                       |   
   |                      +-K-+ +-CODEUNITS16-+                                           |   
   |                      +-M-+ '-CODEUNITS32-'                                           |   
   |                      '-G-'                                                           |   
   |                                  .-(1)-------.                                       |   
   +-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+   
   | | | '-NATIONAL--+-CHAR------+-'  '-(integer)-'      |       |                        |   
   | | |             '-CHARACTER-'                       |       |                        |   
   | | '-+-NVARCHAR-------------------------+--(integer)-'       |                        |   
   | |   +-NCHAR VARYING--------------------+                    |                        |   
   | |   '-NATIONAL--+-CHAR------+--VARYING-'                    |                        |   
   | |               '-CHARACTER-'                               |                        |   
   | |                                      .-(1M)-------------. |                        |   
   | '-+-NCLOB---------------------------+--+------------------+-'                        |   
   |   +-NCHAR LARGE OBJECT--------------+  '-(integer-+---+-)-'                          |   
   |   '-NATIONAL CHARACTER LARGE OBJECT-'             +-K-+                              |   
   |                                                   +-M-+                              |   
   |                                                   '-G-'                              |   
   |                          .-(1M)-------------.                                        |   
   +-+-BLOB----------------+--+------------------+----------------------------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                                        |   
   |                                     +-K-+                                            |   
   |                                     +-M-+                                            |   
   |                                     '-G-'                                            |   
   +-+-DATE-------------------------+-----------------------------------------------------+   
   | +-TIME-------------------------+                                                     |   
   | |            .-(--6--)-------. |                                                     |   
   | '-TIMESTAMP--+---------------+-'                                                     |   
   |              '-(--integer--)-'                                                       |   
   +-XML----------------------------------------------------------------------------------+   
   +-BOOLEAN------------------------------------------------------------------------------+   
   +-CURSOR-------------------------------------------------------------------------------+   
   | .-SYSPROC.-.                   (3)                                                   |   
   '-+----------+--DB2SECURITYLABEL-------------------------------------------------------'   

anchored-data-type

           .-DATA TYPE-.  .-TO-.                                              
|--ANCHOR--+-----------+--+----+--+-variable-name1------------------------+--|
                                  +-table-name.column-name----------------+   
                                  |      .-OF-.                           |   
                                  '-ROW--+----+--+-table-name-----------+-'   
                                                 +-view-name------------+     
                                                 '-cursor-variable-name-'     

anchored-row-data-type

           .-DATA TYPE-.  .-TO-.                                              
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
                                  |      .-OF-.                           |   
                                  '-ROW--+----+--+-table-name-----------+-'   
                                                 +-view-name------------+     
                                                 '-cursor-variable-name-'     

default-clause

|--DEFAULT--+-NULL-------------+--------------------------------|
            +-constant---------+   
            +-special-register-+   
            +-global-variable--+   
            '-(--expression--)-'   

column-list

      .-,---------------------------.      
      V                             |      
|--(----column-name--| data-type3 |-+--)------------------------|

data-type3

|--+-| built-in type |----+-------------------------------------|
   +-distinct-type-name---+   
   +-REF--(--type-name--)-+   
   '-structured-type-name-'   

option-list

      .-LANGUAGE SQL-.                                        
|--●--+--------------+--●--+------------------------------+----->
                           '-PARAMETER CCSID--+-ASCII---+-'   
                                              '-UNICODE-'     

                                      .-NOT DETERMINISTIC-.   
>--●--+-------------------------+--●--+-------------------+----->
      '-SPECIFIC--specific-name-'     '-DETERMINISTIC-----'   

      .-EXTERNAL ACTION----.     .-READS SQL DATA--------.      
>--●--+--------------------+--●--+-----------------------+--●--->
      '-NO EXTERNAL ACTION-'     +-CONTAINS SQL----------+      
                                 |                   (4) |      
                                 '-MODIFIES SQL DATA-----'      

   .-STATIC DISPATCH-.     .-CALLED ON NULL INPUT-.      
>--+-----------------+--●--+----------------------+--●---------->

   .-INHERIT SPECIAL REGISTERS-.      
>--+---------------------------+--●----------------------------->

>--+---------------------------------------------------+-------->
   |                                               (5) |   
   '-PREDICATES--(--| predicate-specification |--)-----'   

   .-INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST-.   
>--+----------------------------------------------+------------->
   '-INHERIT ISOLATION LEVEL WITH LOCK REQUEST----'   

   .-NOT SECURED-.   
>--+-------------+----------------------------------------------|
   '-SECURED-----'   

SQL-function-body

|--+-RETURN----------------------+------------------------------|
   |                         (6) |   
   +-Compound SQL (compiled)-----+   
   '-Compound SQL (inlined)------'   

Notes:
  1. OUT and INOUT are valid only if RETURNS specifies a scalar result and the SQL-function-body is a compound SQL (compiled) statement.
  2. The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
  3. DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
  4. Valid only for compiled scalar function definition and an inlined table function definition. A compiled scalar function defined as MODIFIES SQL DATA can only be used as the only element on the right side of an assignment statement that is within a compound SQL (compiled) statement..
  5. Valid only if RETURNS specifies a scalar result (data-type2)
  6. The following apply to the specification of a compound SQL (compiled) statement: a) Must be used if the parameter data types or returned data types include a row type, array type, or cursor type; b) Must be used if the RETURNS TABLE clause specifies any syntax other than a column-list; c) Not supported if RETURNS ROW is specified; d) Not supported when defining a table function in a partitioned database environment.

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 can be specified only by the owner of the object. 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.

If the function is referenced in the definition of a row permission or a column mask, the function cannot be replaced (SQLSTATE 42893).

function-name
Names the function being defined. It is a qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

The name, including the implicit or explicit qualifiers, together with the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) must not identify a function described in the catalog (SQLSTATE 42723). The unqualified name, together with the number and data types of the parameters, while of course unique within its schema, need not be unique across schemas.

If a two-part name is specified, the schema-name cannot begin with 'SYS' (SQLSTATE 42939).

A number of names used as keywords in predicates are reserved for system use, and cannot be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.

The same name can be used for more than one function if there is some difference in the signature of the functions. Although there is no prohibition against it, an external user-defined table function should not be given the same name as a built-in function.

(parameter-declaration, ...)
Identifies the number of input parameters of the function, and specifies the mode, name, data type, and optional default value of each parameter. One entry in the list must be specified for each parameter that the function will expect to receive. No more than 90 parameters are allowed (SQLSTATE 54023).
It is possible to register a function that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example:
   CREATE FUNCTION WOOFER() ...

No two identically-named functions within a schema are permitted to have exactly the same type for all corresponding parameters. Lengths, precisions, and scales are not considered in this type comparison. Therefore, CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3), as well as DECFLOAT(16) and DECFLOAT(34). A weakly typed distinct type specified for a parameter is considered to be the same data type as the source type of the distinct type. For a Unicode database, CHAR(13) and GRAPHIC(8) are considered to be the same type. There is some further bundling of types that causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. A duplicate signature returns an error (SQLSTATE 42723).

If the data type for a parameter is a Boolean data type, array type, cursor type, or row type, the SQL function body can only reference the parameter within a compound SQL (compiled) statement (SQLSTATE 428H2).

IN | OUT | INOUT
Specifies the mode of the parameter. If an error is returned by the function, OUT parameters are undefined and INOUT parameters are unchanged. The default is IN.
IN
Identifies the parameter as an input parameter to the function. Any changes made to the parameter within the function are not available to the invoking context when control is returned.
OUT
Identifies the parameter as an output parameter for the function.
The function must be a scalar function that is defined with a compound SQL (compiled) statement (SQLSTATE 42613).
The function can be referenced only on the right side of an assignment statement that is in a compound SQL (compiled) statement, and the function reference cannot be part of an expression (SQLSTATE 42887).
INOUT
Identifies the parameter as both an input and output parameter for the function.
The function must be a scalar function that is defined with a compound SQL (compiled) statement (SQLSTATE 42613).
The function can be referenced only on the right side of an assignment statement that is in a compound SQL (compiled) statement, and the function reference cannot be part of an expression (SQLSTATE 42887).
parameter-name
Specifies a name for the parameter. The name cannot be the same as any other parameter-name in the parameter list (SQLSTATE 42734).
data-type1
Specifies the data type of the parameter.
built-in-type
Specifies a built-in data type. For a more complete description of each built-in data type except BOOLEAN and CURSOR, which cannot be specified for a table, see "CREATE TABLE".
BOOLEAN
For a Boolean.
CURSOR
For a reference to an underlying cursor.
anchored-data-type
Identifies another object used to define the parameter data type. The data type of the anchor object can be any of the data types explicitly allowed as data-type1. The data type of the anchor object has the same limitations that apply to specifying the data type directly, or in the case of a row, to creating a row type.
ANCHOR DATA TYPE TO
Indicates an anchored data type is used to specify the data type.
variable-name1
Identifies a global variable. The data type of the global variable is used as the data type for parameter-name.
table-name.column-name
Identifies a column name of an existing table or view. The data type of the column is used as the data type for parameter-name.
ROW OF table-name or view-name
Specifies a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name. The data type of parameter-name is an unnamed row type.
ROW OF cursor-variable-name
Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following elements (SQLSTATE 428HS):
  • A global variable with a strongly typed cursor data type
  • A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.
If the cursor type of the cursor variable is not strongly typed using a named row type, the data type of parameter-name is an unnamed row type.
array-type-name
Specifies the name of a user-defined array type. If array-type-name is specified without a schema name, the array type is resolved by searching the schemas in the SQL path.
cursor-type-name
Specifies the name of a cursor type. If cursor-type-name is specified without a schema name, the cursor type is resolved by searching the schemas in the SQL path.
distinct-type-name
Specifies the name of a distinct type. The length, precision, and scale of the parameter are, respectively, the length, precision, and scale of the source type of the distinct type. A distinct type parameter is passed as the source type of the distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.
REF (type-name)
Specifies a reference type without a scope. The specified type-name must identify a user-defined structured type (SQLSTATE 428DP). The system does not attempt to infer the scope of the parameter or result. Inside the body of the function, a reference type can be used in a dereference operation only by first casting it to have a scope. Similarly, a reference returned by an SQL function can be used in a dereference operation only by first casting it to have a scope. If a type name is specified without a schema name, the type-name is resolved by searching the schemas in the SQL path.
row-type-name
Specifies the name of a user-defined row type. The fields of the parameter are the fields of the row type. If row-type-name is specified without a schema name, the row type is resolved by searching the schemas in the SQL path.
structured-type-name
Specifies the name of a user-defined structured type. If structured-type-name is specified without a schema name, the structured type is resolved by searching the schemas in the SQL path.
DEFAULT
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 special registers that can be specified as the default are that same as those that can be specified for a column default (see default-clause in the CREATE TABLE statement). Other special registers can be specified as the default by using an expression.

The expression can be any expression of the type described in "Expressions". If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64K bytes.

The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).

A default cannot be specified in the following situations:
  • For INOUT or OUT parameters (SQLSTATE 42601)
  • For a parameter of type ARRAY, ROW, or CURSOR (SQLSTATE 429BB)
  • For a parameter to a function definition that also specified RETURNS ROW or a PREDICATES clause (SQLSTATE 42613)
RETURNS
This mandatory clause identifies the type of output of the function.

If the data type of the output of the function is a Boolean data type, array type, cursor type, or row type, the SQL function body must be a compound SQL (compiled) statement (SQLSTATE 428H2).

data-type2
Specifies the data type of the output.

In this statement, exactly the same considerations apply as for the parameters of SQL functions described previously in data-type1 for function parameters.

ROW
Specifies that the output of the function is a single row. If the function returns more than one row, an error is returned (SQLSTATE 21505).
This form of a row function can be used only as a transform function for a structured type (having one structured type as its parameter and returning only built-in data types).
column-list
The list of column names and data types returned for a ROW function. The column-list must include at least two columns (SQLSTATE 428F0).
column-name
Specifies the name of this column. The name cannot be qualified and the same name cannot be used for more than one column in the list.
data-type3
Specifies the data type of the column, and can be any data type supported by a parameter of the SQL function.

The same considerations apply as for the parameters of SQL functions described previously in data-type1 for function parameters. However, data-type3 does not support anchored-data-type, array-type-name, cursor-type-name, and row-type-name.

TABLE
Specifies that the output of the function is a table.
column-list
The list of column names and data types returned for a TABLE function
column-name
Specifies the name of this column. The name cannot be qualified and the same name cannot be used for more than one column in the list.
data-type3
Specifies the data type of the column, and can be any data type supported by a parameter of the SQL function.

The same considerations apply as for the parameters of SQL functions described previously in data-type1 for function parameters. However, data-type3 does not support anchored-data-type, array-type-name, cursor-type-name, and row-type-name.

row-type-name
Specifies a row type from which the fields are used to derive the column list. The field names of the row type are used as the column names.
anchored-row-data-type
Identifies row information from another object to use as the columns of the returned table.
ANCHOR DATA TYPE TO
Indicates an anchored data type is used to specify the data type.
variable-name
Identifies a global variable. The data type of the referenced variable must be a row type.
ROW OF table-name or view-name
Specifies a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name. The data types of the anchor object columns have the same limitations that apply to data-type3.
ROW OF cursor-variable-name
Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following objects (SQLSTATE 428HS):
  • A global variable with a strongly typed cursor data type.
  • A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.
ELEMENT OF array-type-name
Specifies an array type from which the element data type is used to derive the column list. If array-type-name identifies an array type with elements that are a row type, the field names of the row type are used as the column names. If the array-type-name identifies an array type with elements that are not row types, the single result column name is COLUMN_VALUE.
built-in-type
See "CREATE TABLE" for the description of built-in data types.
SPECIFIC specific-name
Provides a unique name for the instance of the function that is being defined. This specific name can be used when sourcing on this function, dropping the function, or commenting on the function. It can never be used to invoke the function. The unqualified form of specific-name is an SQL identifier. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another function instance that exists at the application server; otherwise an error is raised (SQLSTATE 42710).

The specific-name may be the same as an existing function-name.

If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error is raised (SQLSTATE 42882).

If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.

LANGUAGE SQL
Specifies that the function is written using SQL.
PARAMETER CCSID
Specifies the encoding scheme to use for all string data passed into and out of the function. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.
ASCII
Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031).
UNICODE
Specifies that character data is in UTF-8, and that graphic data is in UCS-2. If the database is not a Unicode database, PARAMETER CCSID UNICODE cannot be specified (SQLSTATE 56031).
DETERMINISTIC or NOT DETERMINISTIC
This optional clause specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC function must always return the same table from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC.
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 file. The default is EXTERNAL ACTION.
EXTERNAL ACTION
Specifies that the function takes an action that changes the state of an object that the database manager does not manage.
NO EXTERNAL ACTION
Specifies that the function does not take any action that changes the state of an object that the database manager does not manage. The database manager uses this information during optimization of SQL statements.
READS SQL DATA, CONTAINS SQL, or MODIFIES SQL DATA
Specifies the classification of SQL statements that the function can run. The database manager verifies that the SQL statements that the function issues are consistent with this specification.

For the classification of each statement, see SQL statements that can be executed in routines and triggers.

The default is READS SQL DATA.

READS SQL DATA
Specifies that the function can run statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL. The function cannot run SQL statements that modify data (SQLSTATE 42985).
CONTAINS SQL
Specifies that the function can run only SQL statements with a data access classification of CONTAINS SQL. The function cannot run any SQL statements that read or modify data (SQLSTATE 42985).
MODIFIES SQL DATA
Specifies that the function can run any SQL statement except those statements that are not supported in any function.
STATIC DISPATCH
This optional clause indicates that at function resolution time, a function is chosen based on the static types (declared types) of the parameters of the function.
CALLED ON NULL INPUT
This clause indicates that the function is called regardless of whether any of its arguments are null. It can return a null value or a non-null value. Responsibility for testing null argument values lies with the user-defined function.

The phrase NULL CALL may be used in place of CALLED ON NULL INPUT.

INHERIT SPECIAL REGISTERS
This optional clause indicates that updatable special registers in the function will inherit their initial values from the environment of the invoking statement. For a function that is invoked in the select-statement of a cursor, the initial values are inherited from the environment when the cursor is opened. For a routine that is invoked in a nested object (for example, a trigger or a view), the initial values are inherited from the runtime environment (not the object definition).

No changes to the special registers are passed back to the caller of the function.

Some special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore never inherited from the caller.

PREDICATES
For predicates using this function, this clause identifies those that can exploit the index extensions, and can use the optional SELECTIVITY clause for the predicate's search condition. If the PREDICATES clause is specified, the function must be defined as DETERMINISTIC with NO EXTERNAL ACTION (SQLSTATE 42613). If the PREDICATES clause is specified, and the database is not a Unicode database, PARAMETER CCSID UNICODE must not be specified (SQLSTATE 42613). PREDICATES cannot be specified if SQL-function-body is a compound SQL (compiled) statement (SQLSTATE 42613).
predicate-specification
For details on predicate specification, see "CREATE FUNCTION (External Scalar)".
INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT ISOLATION LEVEL WITH LOCK REQUEST
Specifies whether or not a lock request can be associated with the isolation-clause of the statement when the function inherits the isolation level of the statement that invokes the function. The default is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.
INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST
Specifies that, as the function inherits the isolation level of the invoking statement, it cannot be invoked in the context of an SQL statement which includes a lock-request-clause as part of a specified isolation-clause (SQLSTATE 42601).
INHERIT ISOLATION LEVEL WITH LOCK REQUEST
Specifies that, as the function inherits the isolation level of the invoking statement, it also inherits the specified lock-request-clause.
SQL-function-body
Specifies the body of the function. Parameter names can be referenced in the SQL-function-body. Parameter names may be qualified with the function name to avoid ambiguous references.

For RETURN statement, see: RETURN statement.

For Compound SQL (compiled), see: Compound SQL (compiled) statement.

For Compound SQL (inlined), see: Compound SQL (inlined) statement.

NOT SECURED or SECURED
Specifies whether the function is considered secure for row and column access control. The default is NOT SECURED.
NOT SECURED
Indicates that the function is not considered secure. When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled and column level access control is activated for its table (SQLSTATE 428HA). This rule applies to the non secure user-defined functions that are invoked anywhere in the statement.
SECURED
Indicates that the function is considered secure. The function must be secure when it is referenced in a row permission or a column mask (SQLSTATE 428H8).

Rules

Notes

Examples