CREATE FUNCTION statement (sourced function)

This CREATE FUNCTION statement registers a user-defined function that is based on an existing scalar or aggregate function with a database server.

Invocation for CREATE FUNCTION (sourced)

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for CREATE FUNCTION (sourced)

The privilege set defined below must include at least one of the following:

  • The CREATEIN privilege on the schema
  • SYSADM or SYSCTRL authority
  • System DBADM
  • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

Start of changeIf the authorization ID that is used to create the function has the installation SYSADM authority or the installation SYSOPR authority and if the current SQLID is set to SYSINSTL, the function is identified as system-defined function.End of change

Additional privileges are required for the source function, and other privileges are also needed if the function uses a table as a parameter, or refers to a distinct type. These privileges are:

  • The EXECUTE privilege for the function that the SOURCE clause references.
  • The SELECT privilege on any table that is an input parameter to the function.
  • The USAGE privilege on each distinct type that the function references.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the owner is a role, the implicit schema match does not apply and this role needs to include one of the previously listed conditions.

If the statement is dynamically prepared and is not running in a trusted context for which the ROLE AS OBJECT OWNER clause is specified, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. If the schema name is not the same as the SQL authorization ID of the process, one of the following conditions must be met:

  • The privilege set includes SYSADM or SYSCTRL authority.
  • The SQL authorization ID of the process has the CREATEIN privilege on the schema.

Syntax for CREATE FUNCTION (sourced)

Read syntax diagramSkip visual syntax diagramCREATE FUNCTIONfunction-name( ,parameter-declaration )1RETURNSdata-type2AS LOCATOR2SPECIFICspecific-namePARAMETER CCSIDASCIIEBCDICUNICODESOURCEfunction-name(,parameter-type)SPECIFICspecific-name
Notes:
  • 1 RETURNS, SPECIFIC, and SOURCE can be specified in any order.
  • 2 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.

parameter-declaration:

Read syntax diagramSkip visual syntax diagramparameter-nameparameter-type

parameter-type:

Read syntax diagramSkip visual syntax diagramdata-typeAS LOCATOR1TABLE LIKEtable-nameview-nameAS LOCATOR2
Notes:
  • 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
  • 2 The TABLE LIKE name AS LOCATOR clause can only be specified for the parameter list of the function that is being defined.

data-type:

Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-name

built-in-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( integer, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDDATAGRAPHIC(1)( integer)VARGRAPHIC(integer)DBCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEBINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONEROWID

Description for CREATE FUNCTION (sourced)

function-name
Names the user-defined function. The name is implicitly or explicitly qualified by a schema name. For more information, see Choosing the schema and function names and Determining the uniqueness of functions in a schema in CREATE FUNCTION statement (overview).
(parameter-declaration,…)
Specifies the number of input parameters of the function and the data type of each parameter. All of 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()
parameter-name
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.
data-type
Specifies the data type of the input parameter. The data type can be a built-in data type or a distinct type.
built-in-type
The data type of the input parameter is a built-in data type.

For information on the data types, see built-in-type.

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.

distinct-type-name
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 of the parameters with a character or graphic 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:

  • A datetime type parameter is passed as a character data type, and the data is passed in ISO format.

    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.

  • A distinct type parameter is passed as the source type of the distinct type.

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). (For information on casting data types, see Casting between data types.) 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:

  • An empty set of parentheses can be used to indicate that the length, precision, or scale is the same as the source function.
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default values are used.
AS LOCATOR
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.

TABLE LIKE table-name or view-name AS LOCATOR
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. If a table is specified, the transition table includes columns that are defined as implicitly hidden in the table. 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.

table-name or view-name must identify a table or view that exists at the current server. A view cannot have columns of length 0. The name must not identify a declared temporary table. The table that is identified can contain XML columns; however, the function cannot reference those XML columns. 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:

  • If the CREATE FUNCTION statement is embedded in a program, the implicit qualifier is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not used, the implicit qualifier is the owner of the plan or package.
  • If the CREATE FUNCTION statement is dynamically prepared, the implicit qualifier is the SQL authorization ID in the CURRENT SCHEMA special register.

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.

For more information about using table locators, see Accessing transition tables in a user-defined function or stored procedure.

RETURNS
Identifies the output of the function.
data-type2
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 from the data type of the result of the source function. (For information on casting data types, see Casting between data types.)

AS LOCATOR
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.
SPECIFIC specific-name
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, Db2 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, DROP, GRANT, and REVOKE) and in Db2 commands (START FUNCTION, STOP FUNCTION, and DISPLAY FUNCTION). However, the function cannot be invoked by its specific name.

PARAMETER CCSID
Indicates whether the encoding scheme for character and graphic 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 character and graphic 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.

SOURCE
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:
  • A function that was defined with a CREATE FUNCTION statement
  • A cast function that was generated by a CREATE TYPE statement for a distinct type
  • A built-in function

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 (if a particular syntax is shown, only the indicated form cannot be specified):

  • ARRAY_AGG
  • ARRAY_DELETE
  • ARRAY_FIRST
  • ARRAY_LAST
  • ARRAY_NEXT
  • ARRAY_PRIOR
  • CARDINALITY
  • CHAR(datetime-expression, second-argument) where second-argument is ISO, USA, EUR, JIS, or LOCAL or if CHAR is specified with OCTETS, CODEUNITS16, or CODEUNITS32.
  • CHARACTER_LENGTH
  • CLOB if OCTETS, CODEUNITS16, or CODEUNITS32 is specified
  • COALESCE if a parameter is an array
  • COUNT(*)
  • COUNT_BIG(*)
  • Start of changeFL 504 CUME_DISTEnd of change
  • Start of changeFL 504 CUME_DIST (aggregate)End of change
  • DBCLOB if OCTETS, CODEUNITS16, or CODEUNITS32 is specified
  • DECODE
  • DECRYPT_BIT where the second argument is DEFAULT
  • DECRYPT_CHAR where the second argument is DEFAULT
  • DECRYPT_DB where the second argument is DEFAULT
  • Start of change DECRYPT_DATAKEY_BIGINTEnd of change
  • Start of change DECRYPT_DATAKEY_BITEnd of change
  • Start of change DECRYPT_DATAKEY_CLOBEnd of change
  • Start of changeDECRYPT_DATAKEY_DBCLOBEnd of change
  • Start of change DECRYPT_DATAKEY_DECIMALEnd of change
  • Start of change DECRYPT_DATAKEY_INTEGEREnd of change
  • Start of change DECRYPT_DATAKEY_VARCHAREnd of change
  • Start of change DECRYPT_DATAKEY_VARGRAPHICEnd of change
  • Start of change ENCRYPT_DATAKEYEnd of change
  • EXTRACT
  • Start of changeFL 504 FIRST_VALUEEnd of change
  • GETVARIABLE where the second argument is DEFAULT
  • Start of changeFL 502 GRAPHIC if OCTETS, CODEUNITS16, or CODEUNITS32 is specified, or if the first argument is numericEnd of change
  • IFNULL if a parameter is an array
  • INSERT if OCTETS, CODEUNITS16, or CODEUNITS32 is specified
  • Start of changeFL 504 LAGEnd of change
  • Start of changeFL 504 LAST_VALUEEnd of change
  • Start of changeFL 504 LEADEnd of change
  • LEFT if OCTETS, CODEUNITS16, or CODEUNITS32 is specified
  • Start of changeFL 501 LISTAGGEnd of change
  • LOCAL
  • LOCATE if OCTETS, CODEUNITS16, or CODEUNITS32 is specified
  • MAX
  • MAX_CARDINALITY
  • MIN
  • Start of changeFL 504 NTH_VALUEEnd of change
  • Start of changeFL 504 NTILEEnd of change
  • NULLIF
  • Start of changeFL 504 PERCENT_RANKEnd of change
  • Start of changeFL 504 PERCENT_RANK (aggregate)End of change
  • POSITION
  • Start of changeFL 504 RATIO_TO_REPORTEnd of change
  • Start of changeFL 504 REGEXP_COUNTEnd of change
  • Start of changeFL 504 REGEXP_INSTREnd of change
  • Start of changeFL 504 REGEXP_LIKEEnd of change
  • Start of changeFL 504 REGEXP_REPLACEEnd of change
  • Start of changeFL 504 REGEXP_SUBSTREnd of change
  • RID
  • RIGHT if OCTETS, CODEUNITS16, or CODEUNITS32 is specified
  • STRIP where multiple arguments are specified
  • SUBSTRING
  • TRIM where the first argument is BOTH, B, LEADING, L, TRAILING, T, or the first or second argument is FROM
  • TRIM_ARRAY
  • VARCHAR if OCTETS, CODEUNITS16, or CODEUNITS32 is specified
  • Start of changeFL 502 VARGRAPHIC if OCTETS, CODEUNITS16, or CODEUNITS32 is specified, or if the first argument is numeric.End of change
  • XMLAGG
  • XMLCONCAT
  • XMLELEMENT
  • XMLFOREST
  • 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.

function-name
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, Db2 searches the schemas of the SQL path. Db2 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.

function-name (parameter-type,...)
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. Db2 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 the function was defined with a table parameter (the LIKE TABLE name AS LOCATOR clause was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to uniquely identify the function. Instead, use one of the other syntax variations to identify the function with its function name, if unique, or its specific name.

If function-name() is specified, the identified function must have zero parameters.

function-name
Identifies the function name of the source function. If you specify an unqualified name, Db2 searches the schemas of the SQL path. Otherwise, Db2 searches for the function in the specified schema.
parameter-type,...
Identifies the parameters of the function.

If an unqualified distinct type name is specified, Db2 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:

  • Empty parentheses indicate that Db2 ignores the attribute when determining whether the data types match. For example, DEC() is considered a match for a parameter of a function that is defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parentheses because its parameter value indicates a specific data type (REAL or DOUBLE).
  • If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement. If the data type is FLOAT, the precision does not need to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

If you omit the FOR subtype DATA clause or the CCSID clause for data types with a subtype or encoding scheme attribute, Db2 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.

AS LOCATOR
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.
SPECIFIC specific-name
Identifies the function to be used as the source function by its specific name.

If you specify an unqualified specific-name, Db2 searches the SQL path to locate the schema. Db2 selects the first schema that contains a function with this specific name for which the user has EXECUTE authority. Db2 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, Db2 searches the named schema for the function. Db2 returns an error if it cannot find a function with the specific name.

Notes for CREATE FUNCTION (sourced)

Considerations for all types of user-defined functions:
For considerations that apply to all types of user-defined functions, see CREATE FUNCTION statement (overview).
Owner privileges for sourced functions:
For sourced functions, the owner is authorized to execute the function (EXECUTE privilege) in the following cases:
  • If the underlying function is a user-defined function, and the owner is authorized with the grant option to execute the underlying function, the privilege on the new function includes the grant option. Otherwise, the owner can execute the new function but cannot grant others the privilege to do so.
  • If the underlying function is a built-in function, the owner is authorized with the grant option to execute the underlying built-in function and the privilege on the new function includes the grant option.

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.

Rules for creating sourced functions:
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:
  • The unqualified names of the sourced function and source function can be different (NEWF and SOURCEF).
  • The number of input parameters for NEWF and SOURCEF must be the same.
  • When specifying the input parameters and output for NEWF, you can specify a value for the precision, scale, subtype, or encoding scheme for a data type with any of these attributes or use empty parentheses.

    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, Db2 checks the values against the corresponding input parameters and returned output of SOURCEF as described next.

  • When the CREATE FUNCTION statement is executed, Db2 checks the input parameters of NEWF against those of SOURCEF. The data type of each input parameter of NEWF function must be either the same as, or promotable to, the data type of the corresponding parameter of SOURCEF. (For information on the promotion of data types, see Casting between data types.)

    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.

  • When the CREATE FUNCTION statement is executed, Db2 checks the data type identified in the RETURNS clause of NEWF against the data type that SOURCEF returns. The data type that SOURCEF returns must be either the same as, or promotable to, the RETURNS data type of NEWF.

    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 the SOURCEF 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.

Secure functions:
The sourced user-defined function inherits the SECURED or NOT SECURED attribute from the source function in which only the topmost user-defined function is considered. If the topmost user-defined function is secure, any nested user-defined functions are also considered secure. Db2 does not validate whether those nested user-defined functions are secure. If those nested functions can access sensitive data, the security administrator needs to ensure that those functions are allowed to access sensitive data and should ensure that a change control audit procedure has been established for all changes to those functions.

If the sourced function is defined with the VERIFY_GROUP_FOR_USER or VERIFY_ ROLE_FOR_USER function as its source, the sourced function must specify only two input parameters.

Examples for CREATE FUNCTION (sourced)

Example 1
Assume that you created a distinct type HATSIZE, which you based on the built-in data type INTEGER. You want to have an AVG function to compute the average hat size of different departments. Create a sourced function that is based on built-in function AVG.
   CREATE FUNCTION AVE (HATSIZE) RETURNS HATSIZE
      SOURCE SYSIBM.AVG (INTEGER);

When you created distinct type HATSIZE, two cast functions were generated, which allow HATSIZE to be cast to INTEGER for the argument and INTEGER to be cast to HATSIZE for the result of the function.

Example 2
After Smith registered the external scalar function CENTER in his schema, you decide that you want to use this function, but you want it to accept two INTEGER arguments instead of one INTEGER argument and one FLOAT argument. Create a sourced function that is based on CENTER.
   CREATE FUNCTION MYCENTER (INTEGER, INTEGER)
      RETURNS FLOAT
      SOURCE SMITH.CENTER (INTEGER, FLOAT);