This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
The privilege set defined below must include at least one of the following:
The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.
CREATE FUNCTION function-name ([parameter-declaration[,...]]) RETURNS data-type2 [LANGUAGE SQL] option-list RETURN-statement
parameter-declaration:
[parameter-name] parameter-type
parameter-type:
{data-type [AS LOCATOR] | TABLE LIKE {table-name | view-name} [AS LOCATOR]}
data-type:
{built-in-data-type | distinct-type-name}
built-in-data-type:
SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer)
| VARCHAR (integer) }
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] }
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] |
{DATE | TIME | TIMESTAMP} |
ROWID
Names the user-defined function. The name is implicitly or explicitly qualified by a schema name. The combination of name, schema name, the number of parameters, and the data type 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 is sourced on an existing function to enable the use of the existing function with a distinct type, the name can be the same name as the existing function. In general, more than one function can have the same name if the function signature of each function is unique.
The 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 unqualified function name is implicitly qualified with a schema name according to the following rules:
The schema name can be "SYSTOOLS" if the user who executes the CREATE statement has SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with "SYS" unless the schema name is "SYSADM".
The owner of the function is determined by how the CREATE FUNCTION statement is invoked:
The owner is implicitly given the EXECUTE privilege with the GRANT option for the function.
Specifies the number of input parameters of the function and the data type of each parameter. All the parameters for a function are input parameters and are nullable. There must be one entry in the list for each parameter that the function expects to receive. Although not required, you can give each parameter a name.
A function can have no parameters. In this case, you must code an empty set of parentheses, for example:
CREATE FUNCTION WOOFER()
Specifies the name of the input parameter. The name is an SQL identifier, and each name in the parameter list must not be the same as any other name.
Specifies the data type of the input parameter. The data type can be a built-in data type or a distinct type.
The data type of the input parameter is a built-in data type.
You can use the same built-in data types as for the CREATE TABLE statement.
For parameters with a character or graphic data type, the PARAMETER CCSID clause or CCSID clause indicates the encoding scheme of the parameter. If you do not specify either of these clauses, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.
The data type of the input parameter is a distinct type. Any length, precision, scale, subtype, or encoding scheme attributes for the parameter are those of the source type of the distinct type.
The implicitly or explicitly specified encoding scheme of all the parameters with a string data type must be the same - either all ASCII, all EBCDIC, or all UNICODE.
Although parameters with a character data type have an implicitly or explicitly specified subtype (BIT, SBCS, or MIXED), the function program can receive character data of any subtype. Therefore, conversion of the input data to the subtype of the parameter might occur when the function is invoked.
Parameters with a datetime data type or a distinct type are passed to the function as a different data type:
The encoding scheme for a datetime type parameter is the same as the implicitly or explicitly specified encoding scheme of any character or graphic string parameters. If no character or graphic string parameters are passed, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.
You can specify any built-in data type or distinct type that matches or can be cast to the data type of the corresponding parameter of the source function (the function that is identified in the SOURCE clause). Length, precision, or scale attributes do not have be specified for data types with these attributes. When specifying data types with these attributes, follow these rules:
Specifies that a locator to the value of the parameter is passed to the function instead of the actual value. Specify AS LOCATOR only for parameters with a LOB data type or a distinct type based on a LOB data type. Passing locators instead of values can result in fewer bytes being passed to the function, especially when the value of the parameter is very large.
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.
The XDB operating environment does not support the LOB data type.
Specifies that the parameter is a transition table. However, when the function is invoked, the actual values in the transition table are not passed to the function. A single value is passed instead. This single value is a locator to the table, which the function uses to access the columns of the transition table. A function with a table parameter can only be invoked from the triggered action of a trigger.
The use of TABLE LIKE provides an implicit definition of the transition table. It specifies that the transition table has the same number of columns as the identified table or view. The columns have the same data type, length, precision, scale, subtype, and encoding scheme as the identified table or view, as they are described in catalog tables SYSCOLUMNS and SYSTABLESPACE. The number of columns and the attributes of those columns are determined at the time the CREATE FUNCTION statement is processed. Any subsequent changes to the number of columns in the table or the attributes of those columns do not affect the parameters of the function.
The name specified after TABLE LIKE must identify a table or view that exists at the current server. The name must not identify a declared temporary table. The name does not have to be the same name as the table that is associated with the transition table for the trigger. An unqualified table or view name is implicitly qualified according to the following rules:
When the function is invoked, the corresponding columns of the transition table identified by the table locator and the table or view identified in the TABLE LIKE clause must have the same definition. The data type, length, precision, scale, and encoding scheme of these columns must match exactly. The description of the table or view at the time the CREATE FUNCTION statement was executed is used.
Additionally, a character FOR BIT DATA column of the transition table cannot be passed as input for a table parameter for which the corresponding column of the table specified at the definition is not defined as character FOR BIT DATA. (The definition occurs with the CREATE FUNCTION statement.) Likewise, a character column of the transition table that is not FOR BIT DATA cannot be passed as input for a table parameter for which the corresponding column of the table specified at the definition is defined as character FOR BIT DATA.
The XDB operating environment does not support transition table parameters.
Specifies the data type of the output. The output is nullable.
You can specify any built-in data type or distinct type that can be cast to from the data type of the source function's result.
Specifies that the function returns a locator to the value rather than the actual value. You can specify AS LOCATOR only if the output from the function has a LOB data type or a distinct type based on a LOB data type.
The XDB operating environment does not support the LOB data type.
Provides a unique name for the function. The name is implicitly or explicitly qualified with a schema name. The name, including the schema name, must not identify the specific name of another function that exists at the current server.
The unqualified form of specific-name is an SQL identifier. The qualified form is an SQL identifier (the schema name) followed by a period and an SQL identifier.
If you do not specify a schema name, it is the same as the explicit or implicit schema name of the function name (function-name). If you specify a schema name, it must be the same as the explicit or implicit schema name of the function name.
If you do not specify the SPECIFIC clause, the default specific name is the name of the function. However, if the function name does not provide a unique specific name or if the function name is a single asterisk, XDB generates a specific name in the form of:
SQLxxxxxxxxxxxx
where xxxxxxxxxxxx is a string of 12 characters that make the name unique.
The specific name is stored in the SPECIFIC column of the SYSROUTINES catalog table. The specific name can be used to uniquely identify the function in several SQL statements (such as ALTER FUNCTION, COMMENT ON, DROP, GRANT, and REVOKE). However, the function cannot be invoked by its specific name.
Indicates whether the encoding scheme for string parameters is ASCII, EBCDIC, or UNICODE. The default encoding scheme is the value specified in the CCSID clauses of the parameter list or RETURNS clause, or in the field DEF ENCODING SCHEME on installation panel DSNTIPF.
This clause provides a convenient way to specify the encoding scheme for all string parameters. If individual CCSID clauses are specified for individual parameters in addition to this PARAMETER CCSID clause, the value specified in all of the CCSID clauses must be the same value that is specified in this clause.
This clause also specifies the encoding scheme to be used for system-generated parameters of the routine such as message tokens and DBINFO.
Specifies that the new function is being defined as a sourced function. A sourced function is implemented by another function (the source function). The source function must be a scalar or aggregate function that exists at the current server, and it must be one of the following types of functions:
If the source function is not a built-in function, the particular function can be identified by its name, function signature, or specific name.
If the source function is a built-in function, the SOURCE clause must include a function signature for the built-in function. The source function must not be any of the built-in functions shown in the table below. If a particular syntax is shown, only the indicated form cannot be specified.
| Type of Function | Restricted Functions |
|---|---|
| Aggregate | COUNT(*) |
| COUNT_BIG(*) | |
| Scalar function | CHAR(datetime-expression, second-argument) where second-argument is ISO, USA, EUR, JIS, or LOCAL |
| COALESCE | |
| DECRYPT_CHAR where second argument is DEFAULT | |
| GETVARIABLE where second argument is DEFAULT | |
| MAX | |
| NULLIF | |
| XML2CLOB | |
| XMLELEMENT | |
| XMLNAMESPACES |
If you base the sourced function directly or indirectly on an external scalar function, the sourced function inherits the attributes of the external scalar function. This can involve several layers of sourced functions. For example, assume that function A is sourced on function B, which in turn is sourced on function C. Function C is an external scalar function. Functions A and B inherit all of the attributes that are specified on the EXTERNAL clause of the CREATE FUNCTION statement for function C.
Identifies the function that is to be used as the source function. The source function can be defined with any number of parameters. If more than one function is defined with the specified name in the specified or implicit schema, an error is returned.
If you specify an unqualified function-name, XDB searches the schemas of the SQL path. XDB selects the first schema that has only one function with this name on which the user has EXECUTE authority. An error is returned if a function is not found or a schema has more than one function with this name.
Identifies the function that is to be used as the source function by its function signature, which uniquely identifies the function. Thefunction-name (parameter-type,...) must identify a function with the specified signature. The specified parameters must match the data types in the corresponding position that were specified when the function was created. XDB uses the number of data types and the logical concatenation of the data types to identify the specific function instance. Synonyms for data types are considered a match.
If function-name() is specified, the identified function must have zero parameters.
To use a built-in function as the source function, this syntax variation must be used. You cannot use this form of the syntax if the source function was defined with a table parameter (the LIKE TABLE was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table). Instead, identify the function with its function name, if unique, or with its specific name.
Identifies the function name of the source function. If you specify an unqualified name, XDB searches the schemas of the SQL path. Otherwise, XDB searches for the function in the specified schema.
Identifies the parameters of the function.
If an unqualified distinct type name is specified, XDB searches the SQL path to resolve the schema name for the distinct type.
Empty parentheses are allowed for some data types that are specified in this context. For data types that have a length, precision, or scale attribute, use one of the following specifications:
If you omit the FOR subtype DATA clause or the CCSID clause for data types with a subtype or encoding scheme attribute, XDB is to ignore the attribute when determining whether the data types match.An exception to ignoring the attribute is FOR BIT DATA. A character FOR BIT DATA parameter of the new function cannot correspond to a parameter of the source function that is not defined as character FOR BIT DATA. Likewise, a character parameter of the new function that is not FOR BIT DATA cannot correspond to a parameter of the source function that is defined as character FOR BIT DATA.
The number of input parameters in the function that is being created must be the same as the number of parameters in the source function. If the data type of each input parameter is not the same as or castable to the corresponding parameter of the source function, an error occurs. The data type of the final result of the source function must match or be castable to the result of the sourced function.
Specifies that the function is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or distinct type that is based on a LOB.
Identifies the function to be used as the source function by its specific name.
If you specify an unqualified specific-name, XDB searches the SQL path to locate the schema. XDB selects the first schema that contains a function with this specific name for which the user has EXECUTE authority. XDB returns an error if it cannot find a function with the specific name in one of the schemas in the SQL path.
If you specify a qualified specific-name, XDB searches the named schema for the function. XDB returns an error if it cannot find a function with the specific name.
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. 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, we recommend using the following data types for parameters:
For portability of functions across platforms that are not DB2 UDB for z/OS, do not use the following data types, which might have different representations on different platforms:
The implicitly or explicitly specified encoding scheme of all the parameters with a string data type (both input and output parameters) must be the same--either all ASCII, all EBCDIC, or all UNICODE.
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. 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 schema must not contain two 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, XDB does not consider any length, precision, scale, subtype or encoding scheme attributes in the comparison. XDB considers the synonyms of data types (DECIMAL and NUMERIC, REAL and FLOAT, and DOUBLE and FLOAT) a match. Therefore, CHAR(8) and CHAR(35) are considered to be the same, as are DECIMAL(11,2), DECIMAL(4,3), and NUMERIC(4,2).
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)) ...
For the discussion in this section, assume that the function that is being created is named NEWF and the source function is named SOURCEF. Consider the following rules when creating a sourced function:
Empty parentheses, such as VARCHAR(), indicate that the value of the attribute is the same as the attribute for the corresponding parameter of SOURCEF, or that is determined by data type promotion. If you specify any values for the attributes, XDB checks the values against the corresponding input parameters and returned output of SOURCEF as described next.
This checking does not guarantee that an error will not occur when NEWF is invoked. For example, an argument that matches the data type and length or precision attributes of a NEWF parameter might not be promotable if the corresponding SOURCEF parameter has a shorter length or less precision. In general, do not define the parameters of a sourced function with length or precision attributes that are greater than the attributes of the corresponding parameters of the source function.
This checking does not guarantee that an error will not occur when NEWF is invoked. For example, the value of a result that matches the data type and length or precision attributes of those specified for SOURCEF's result might not be promotable if the RETURNS data type of NEWF has a shorter length or less precision. Consider the possible effects of defining the RETURNS data type of a sourced function with length or precision attributes that are less than the attributes defined for the data type returned by source function.
A row can be fetched more than once with a scrollable cursor. Therefore, if a scrollable cursor is defined with a nondeterministic function in the select list of the cursor, a row can be fetched multiple times with different results for each fetch. Similarly, if a scrollable cursor is defined with a user-defined function with external action, the action is executed with every fetch.