• For each table, view, or nickname identified in any fullselect:
  • CONTROL privilege on that table, view, or nickname, or
  • SELECT privilege on that table, view, or nickname
  and at least one of the following:
  • 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
• SYSADM or DBADM authority

>>-CREATE FUNCTION--function-name------------------------------->

>--(--+--------------------------------+--)--*------------------>
      | .-,--------------------------. |
      | V                            | |
      '---parameter-name--data-type1-+-'

>--RETURNS--+-data-type2-----------------+--*------------------->
            '-+-ROW---+--| column-list |-'
              '-TABLE-'

                                   .-LANGUAGE SQL-.
>--+-------------------------+--*--+--------------+--3 *---------->
   '-SPECIFIC--specific-name-'

                                        .-NOT DETERMINISTIC-.
>--+------------------------------+--*--+-------------------+--->
   '-3 PARAMETER CCSID--+-3 ASCII---+-'     '-DETERMINISTIC-----'
                      '-3 UNICODE-'

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

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

   .-INHERIT SPECIAL REGISTERS-.
>--+---------------------------+--*----------------------------->

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

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

>--| SQL-function-body |---------------------------------------><

column-list:

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

SQL-function-body:

|--+-RETURN Statement-----------+-------------------------------|
   '-dynamic-compound-statement-'

1. 4 Valid only if RETURNS specifies a table (TABLE column-list)
2. Valid only if RETURNS specifies a scalar result (data-type2)

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 (with a maximum length of 18). 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-name

A name that is distinct from the names of all other parameters in this function.

data-type1

Specifies the data type of the parameter:

• SQL data type specifications and abbreviations that may be specified in the data-type1 definition of a CREATE TABLE statement.
• REF may be specified, but that REF is unscoped. 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.
• LONG VARCHAR and LONG VARGRAPHIC data types may not be used (SQLSTATE 42815).

RETURNS

This mandatory clause identifies the type of output of the function.

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 above under data-type1 for function parameters.

ROW column-list

Specifies that the output of the function is a single row. If the function returns more than one row, an error is raised (SQLSTATE 21505). The column-list must include at least two columns (SQLSTATE 428F0). A row function can only be used as a transform function for a structured type (having one structured type as its parameter and returning only base types).

TABLE column-list

Specifies that the output of the function is a table.

column-list

The list of column names and data types returned for a ROW or 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 of the row.

data-type3

Specifies the data type of the column, and can be any data type supported by a parameter of the SQL function.

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 (with a maximum length of 18). 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.

3 3

PARAMETER CCSID

3

Specifies the encoding scheme to use for all string data 3 passed into and out of the function. 3 If the PARAMETER CCSID clause is not specified, the default is 3 PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID 3 ASCII for all other databases. 3 3

3

ASCII

3

Specifies that string data is encoded in the database 3 code page. 3 If the database is a Unicode database, PARAMETER CCSID ASCII cannot 3 be specified (SQLSTATE 56031). 3

3

UNICODE

3

Specifies that character data is in UTF-8, and that graphic 3 data is in UCS-2. 3 If the database is not a Unicode database, PARAMETER CCSID UNICODE 3 cannot be specified (SQLSTATE 56031). 3

3

3

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. NOT DETERMINISTIC must be explicitly or implicitly specified if the body of the function accesses a special register or calls another non-deterministic function (SQLSTATE 428C2).

NO EXTERNAL ACTION or EXTERNAL ACTION

This optional clause specifies whether or not the function takes some action that changes the state of an object not managed by the database manager. By specifying NO EXTERNAL ACTION, the system can use certain optimizations that assume functions have no external impacts. EXTERNAL ACTION must be explicitly or implicitly specified if the body of the function calls another function that has an external action (SQLSTATE 428C2).

4 4

CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA

4

Indicates what type of SQL statements can be executed. 4 4

4

CONTAINS SQL

4

Indicates that SQL statements that neither read nor modify SQL data 4 can be executed by the function (SQLSTATE 42985). 4

4

READS SQL DATA

4

Indicates that SQL statements that do not modify SQL 4 data can be executed by the function (SQLSTATE 42985). 4

4 4

MODIFIES SQL DATA

4

Indicates that all SQL statements supported in 4 dynamic-compound-statement can be executed by the function. 4

4 4

4

STATIC DISPATCH

This optional clause indicates that at function resolution time, DB2 chooses a function 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). 3 If the PREDICATES clause is specified, and the database 3 is not a Unicode database, PARAMETER CCSID UNICODE must not be 3 specified (SQLSTATE 42613).

predicate-specification

For details on predicate specification, see "CREATE FUNCTION (External Scalar)".

7 7

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT ISOLATION LEVEL WITH LOCK REQUEST

7

Specifies whether or not a lock request can be associated with 7 the isolation-clause of the statement when the function inherits the 7 isolation level of the statement that invokes the function. 7 The default is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST. 7 7

7

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST

7

Specifies that, as the function inherits the isolation level of 7 the invoking statement, it cannot be invoked in the context of an SQL 7 statement which includes a lock-request-clause as part of a specified 7 isolation-clause (SQLSTATE 42601). 7

7

INHERIT ISOLATION LEVEL WITH LOCK REQUEST

7

Specifies that, as the function inherits the isolation level of 7 the invoking statement, it also inherits the specified 7 lock-request-clause. 7

7

7

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. If the SQL-function-body is a dynamic compound statement, it must contain at least one RETURN statement, and a RETURN statement must be executed when the function is called (SQLSTATE 42632). If the function is a table or row function, it can contain only one RETURN statement, which must be the last statement in the dynamic compound statement (SQLSTATE 429BD).

• Resolution of function calls inside the function body is done according to the function path that is effective for the CREATE FUNCTION statement and does not change after the function is created.
• If an SQL function contains multiple references to any of the date or time special registers, all references return the same value, and it will be the same value returned by the register invocation in the statement that called the function.
• The body of an SQL function cannot contain a recursive call to itself or to another function or method that calls it, since such a function could not exist to be called.
• The following rules are enforced by all statements that create functions or methods:
  • A function may not have the same signature as a method (comparing the first parameter-type of the function with the subject-type of the method).
  • A function and a method may not be in an overriding relationship. That is, if the function were a method with its first parameter as subject, it must not override, or be overridden by, another method. For more information about overriding methods, see the "CREATE TYPE (Structured)" statement.
  • Because overriding does not apply to functions, it is permissible for two functions to exist such that, if they were methods, one would override the other.
  For the purpose of comparing parameter-types in the above rules:
  
  • Parameter-names, lengths, AS LOCATOR, and FOR BIT DATA are ignored.
  • A subtype is considered to be different from its supertype.
• Table access restrictions If a function is defined as READS SQL DATA, no statement in the function can access a table that is being modified by the statement that invoked the function (SQLSTATE 57053). For example, suppose the user-defined function BONUS() is defined as READS SQL DATA. If the statement UPDATE EMPLOYEE SET SALARY = SALARY + BONUS(EMPNO) is invoked, no SQL statement in the BONUS function can read from the EMPLOYEE table.
  7 If a function defined with MODIFIES SQL DATA contains 7 nested CALL statements, read access to the tables being modified by 7 the function (by either the function definition or the statement that 7 invoked the function) is not allowed (SQLSTATE 57053).
  
• Privileges The definer of a function always receives the EXECUTE privilege on the function, as well as the right to drop the function. The definer of a function is also given the WITH GRANT OPTION on the function if the definer has WITH GRANT OPTION on all privileges required to define the function, or if the definer has SYSADM or DBADM authority.
  The definer of a function only acquires privileges if the privileges from which they are derived exist at the time the function is created. The definer must have these privileges either directly, or because PUBLIC has the privileges. Privileges held by groups of which the function definer is a member are not considered. When using the function, the connected user's authorization ID must have the valid privileges on the table or view that the nickname references at the data source.
  
• Compatibilities
  • 3 For compatibility with DB2 UDB for OS/390 and z/OS: 3 3
    3
    • The following syntax is accepted as the default behavior: 3 3
      3
      • CCSID UNICODE in a Unicode database
      3
      • CCSID ASCII in a non-Unicode database
  • For compatibility with previous versions of DB2:
    • NULL CALL can be specified in place of CALLED ON NULL INPUT

CREATE FUNCTION TAN (X DOUBLE)
     RETURNS DOUBLE
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN SIN(X)/COS(X)       

CREATE FUNCTION FROMPERSON (P PERSON)
     RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN VALUES (P..NAME, P..FIRSTNAME) 

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
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
         FROM EMPLOYEE
         WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO 

CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
     RETURNS VARCHAR(4000)
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     BEGIN ATOMIC
     DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
     DECLARE LEN INT;
     IF INSTR IS NULL THEN
     RETURN NULL;
     END IF;
     SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
     WHILE LEN > 0 DO
     SET (REVSTR, RESTSTR, LEN) 
       = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, 
       SUBSTR(RESTSTR, 2, LEN - 1),
       LEN - 1);
     END WHILE;
     RETURN REVSTR;
   END 

CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
4      RETURNS TABLE (EMPNO CHAR(6),
4                     LASTNAME VARCHAR(15),
4                     FIRSTNAME VARCHAR(12))
4      LANGUAGE SQL
4      MODIFIES SQL DATA
4      NO EXTERNAL ACTION
4      DETERMINISTIC
4      BEGIN ATOMIC
4        INSERT INTO AUDIT 
4        VALUES (USER, 
4                'Table: EMPLOYEE Prd: DEPTNO = ' CONCAT DEPTNO);
4        RETURN
4          SELECT EMPNO, LASTNAME, FIRSTNME
4            FROM EMPLOYEE
4            WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
4      END