CREATE FUNCTION statement (overview)

Notes for all CREATE FUNCTION types

The following considerations apply for creating all types of functions:

Owner privileges:

For all functions except for sourced functions, the owner is authorized to execute the function (EXECUTE privilege) and has the ability to grant these privileges to others. For more information, see GRANT statement (function or procedure privileges). For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.

Choosing the schema and function name:

The combination of name, schema name, the number of parameters, and the data type of each parameter (without regard for any length, precision, scale, subtype or encoding scheme attributes of the data type) must not identify a user-defined function that exists at the current server. If the function has more than 30 parameters, only the first 30 parameters are used to determine whether the function is unique.

You can use the same name for more than one function if the function signature of each function is unique.

• The unqualified form of function-name must not be any of the following system-reserved keywords even if you specify them as delimited identifiers:

  ALL                     LIKE                     UNIQUE
  AND                     MATCH                    UNKNOWN
  ANY                     NOT                      =
  BETWEEN                 NULL                     ¬=
  DISTINCT                ONLY                     <
  EXCEPT                  OR                       <=
  EXISTS                  OVERLAPS                 ¬<
  FALSE                   SIMILAR                  >
  FOR                     SOME                     >=
  FROM                    TABLE                    ¬>
  IN                      TRUE                     <>
  IS                      TYPE                                          

The schema name can be 'SYSTOOLS' or 'SYSFUN' if the privilege set includes the SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM'.

Defining the parameters

The input parameters for the function are specified as a list within parentheses.

A function can have no input parameters. In this case, an empty set of parentheses must be specified, for example:

CREATE FUNCTION WOOFER()

The data type of the result of the function is specified in the RETURNS clause for the function.

Choosing data types for parameters:

When you choose the data types of the input and output parameters for your function, consider the rules of promotion that can affect the values of the parameters. (See Promotion of data types). For example, a constant that is one of the input arguments to the function might have a built-in data type that is different from the data type that the function expects, and more significantly, might not be promotable to that expected data type. Based on the rules of promotion, consider using the following data types for parameters:

• INTEGER instead of SMALLINT
• DOUBLE instead of REAL
• VARCHAR instead of CHAR
• VARGRAPHIC instead of GRAPHIC
• VARBINARY instead of BINARY

For portability of functions across platforms that are not Db2 for z/OS®, do not use the following data types, which might have different representations on different platforms:

• FLOAT. Use DOUBLE or REAL instead.
• NUMERIC. Use DECIMAL instead.

Specifying AS LOCATOR for a parameter:

Passing a locator instead of a value can result in fewer bytes being passed in or out of the function. This can be useful when the value of the parameter is very large. The AS LOCATOR clause specifies that a locator to the value of the parameter is passed instead of the actual value. Specify AS LOCATOR only for parameters that have a LOB data type or a distinct type that is based on a LOB data type and only when LANGUAGE JAVA is not in effect.

The AS LOCATOR clause has no effect on determining whether data types can be promoted, nor does it affect the function signature, which is used in function resolution.

AS LOCATOR must not be specified for a sourced or SQL function.

AS LOCATOR must not be specified if the function is defined with NO SQL.

Considerations for a function that is defined using a TABLE LIKE name AS LOCATOR clause:

If a function is defined with a table parameter (the TABLE LIKE name AS LOCATOR clause was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), no ALTER FUNCTION statement that specifies a parameter list as part of the alteration can change the function. For example, a parameter list is required as part of the routine specification when adding or replacing a version of a function. In such cases, the function must be dropped a re-created..

Determining the uniqueness of functions in a schema:

At the current server, the function signature of each function, which is the qualified function name combined with the number and data types of the input parameters, must be unique. If the function has more than 30 input parameters, only the data types of the first 30 are used to determine uniqueness. This means that two different schemas can each contain a function with the same name that have the same data types for all of their corresponding data types. However, a single schema must not contain multiple functions with the same name that have the same data types for all of their corresponding data types.

When determining whether corresponding data types match, Db2 does not consider any length, precision, or scale attributes in the comparison. Db2 considers the synonyms of data types as a match. For example, REAL and FLOAT, and DOUBLE and FLOAT are considered a match. Therefore, CHAR(8) and CHAR(35) are considered to be the same, as are DECIMAL(11,2), DECIMAL(4,3), DECFLOAT(16) and DECFLOAT(34), TIMESTAMP(6) and TIMESTAMP(9), TIMESTAMP(6) WITH TIME ZONE and TIMESTAMP(9) WITH TIME ZONE. Furthermore, the character and graphic types, and the timestamp types are considered to be the same. For example, the following are considered to be the same type: CHAR and GRAPHIC, VARCHAR and VARGRAPHIC, CLOB and DBCLOB, TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE. CHAR(13) and GRAPHIC(8) are considered to be the same type. An error is returned if the signature of the function being created is a duplicate of a signature for an existing user-defined function with the same name and schema.

Assume that the following statements are executed to create four functions in the same schema. The second and fourth statements fail because they create functions that are duplicates of the functions that the first and third statements created.

  CREATE FUNCTION PART (INT, CHAR(15)) …
  CREATE FUNCTION PART (INTEGER, CHAR(40)) …
  CREATE FUNCTION ANGLE (DECIMAL(12,2)) …
  CREATE FUNCTION ANGLE (DEC(10,7)) …

Specifying a specific name for a function:

When defining multiple functions with the same name and schema (with different parameter lists), it is recommended that a specific name also be specified.

The specific name can be used to uniquely identify the function such as when sourcing on this function, dropping the function, or commenting on the function. However, the function cannot be invoked by its specific name.

The specific name is implicitly or explicitly qualified with a schema name. If a schema name is not specified on CREATE FUNCTION, it is the same as the explicit or implicit schema name of the function name ( function-name ). If a schema name is specified, it must be the same as the explicit or implicit schema name of the function name. The name, including the schema name must not identify the specific name of another function or procedure that exists at the current server.

If the SPECIFIC clause is not specified, a specific name is generated.

Extending or overriding a built-in function

Giving a user-defined external function the same name as a built-in function is not a recommended practice unless the functionality of the built-in function needs to be extended or overridden.

If you do intend to create a function with the same name as a built-in function, be careful to maintain the uniqueness of its function signature. If your function has the same name and data types of the corresponding parameters of the built-in function but implements different logic, Db2 might choose the wrong function when the function is invoked with an unqualified function name. For example, If the schema for the new function appears in the SQL path before the system schemas, Db2 might choose a user-defined function rather than the built-in function. An application that uses the unqualified name and was previously successful using the built-in function of that name might fail. It might also appear to run successfully but provide a different result if Db2 chooses the built-in function instead of the built-in function. This situation can occur with dynamic SQL statements, or when static SQL statements are rebound.

Extending the functionality of existing built-in functions

Create the new user-defined function with the same name as the built-in function, and a unique function signature. For example, a user-defined function similar to the built-in function ROUND that accepts the distinct type MONEY as input rather than the built-in numeric types might be necessary. In this case, the signature for the new user-defined function named ROUND is different from all the function signatures supported by the built-in ROUND function.

Overriding a built-in function:

Create the new user-defined function with the same name and signature as a built-in function. The new function has the same name and data type as the corresponding parameters of the built-in function but implements different logic.

For example, it might be useful to use different rules for rounding that the built-in ROUND function. In this case, the signature for the new user-defined function named ROUND will be the same as a signature that is supported by the built-in ROUND function.

Another case for overriding a built-in function is to use its functionality when an argument is not a built-in data type. Instead of explicitly casting the argument to a built-in data type, you can define a sourced function that accepts the user-defined data type argument and passes it to the underlying built-in function. A sourced function is defined with a reference to a built-in function or another user-defined function. For more information about sourced functions, see CREATE FUNCTION statement (sourced function).

The DISTINCT keyword can be passed on the invocation of a user-defined function that is sourced on one of the built-in aggregate functions. For example, assume that MY_AVG is a user-defined function that is sourced on built-in AVG function. The user-defined function could be invoked with 'MY_AVG (DISTINCT expression)' This results in the underlying built-in AVG function being invoked with the DISTINCT keyword.

Special registers in functions

The settings of the special registers of the invoker are inherited by the function on invocation and restored upon return to the invoker. Special registers may be changed in a function that can execute SQL statements, but these changes do not affect the caller.

Global variables in functions:

The content of global variables that are referenced in a function are inherited from the invoking environment.

Scrollable cursors specified with user-defined functions:

A row can be fetched more than once with a scrollable cursor. Therefore, if a SELECT statement of a scrollable cursor invokes a function that is not deterministic in the select list, a row can be fetched multiple times with different results for each fetch. Similarly, if the SELECT statement of a scrollable cursor invokes a user-defined function defined with external action, the action is executed with every fetch.

Considerations for secure functions:

To create a secure function, the security administrator usually examines the data that is accessed by a function, ensures that it is secure, and grants the CREATE_SECURE_OBJECT privilege to someone who currently requires the privileges to create a secure user-defined function. After the function is created, they revoke the CREATE_SECURE_OBJECT privilege from the function owner.

A sourced function cannot be created as a secure function.

If a row permission or a column mask definition references a user-defined function, the user-defined function must be secure because the sensitive data might be passed as arguments to the function. The SECURE column in the DSN_FUNCTION_TABLE indicates whether a user-defined function is considered secure.

If a secure user-defined function invokes other user-defined functions, Db2 does not validate whether those nested user-defined functions have the SECURED attribute. If those nested functions can access sensitive data, the security administrator must ensure that those functions are allowed to access the sensitive data and should ensure that a change control audit procedure is established for all changes to those functions.