Function designators in Db2 for z/OS

This topic describes SQL syntax for fragments that identify built-in or user-defined functions in statements such as ALTER FUNCTION, GRANT, and REVOKE. The function-designator identifies the SQL function by its name, a function signature that uniquely identifies the function, or its specific name.

Syntax for function-designator (ALTER FUNCTION )

function-designator:
Read syntax diagramSkip visual syntax diagramFUNCTIONfunction-name(,parameter-type)SPECIFIC FUNCTIONspecific-name
parameter-type:
Read syntax diagramSkip visual syntax diagramdata-type AS LOCATOR1
data-type:
Read syntax diagramSkip visual syntax diagrambuilt-in-type 2distinct-type-namearray-type-name3
Notes:
  • 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
  • 2 For the syntax diagram for this fragment, see Syntax for built-in-type.
  • 3 array-type-name is supported only for compiled SQL scalar functions.

Syntax for function-designator (GRANT or REVOKE)

function-designator:
Read syntax diagramSkip visual syntax diagramFUNCTION,function-name(,parameter-type)*SPECIFIC FUNCTION,specific-name
parameter-type:
Read syntax diagramSkip visual syntax diagramdata-type AS LOCATOR1
data-type:
Read syntax diagramSkip visual syntax diagrambuilt-in-type 2distinct-type-namearray-type-name3
Notes:
  • 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
  • 2 For the syntax diagram for this fragment, see Syntax for built-in-type.
  • 3 array-type-name is supported only for compiled SQL scalar functions.

Syntax for built-in-type

For descriptions of the built-in data types in the following diagram, see Data types in Db2 for z/OS.

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(length)DBCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEBINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONEROWIDXML1
Notes:
  • 1 The XML data type is not supported for external functions.
FUNCTION function-name

Identifies the function by its name. The function can have any number of parameters defined for it.

The function-name must identify exactly one function in the specified or implicit schema. If there is more than one function in the schema, or the schema does not contain a function with function-name, an error is returned.

For an inlined scalar function or compiled SQL scalar function, only the first 30 parameters determine the uniqueness of the function.

FUNCTION function-name (parameter-type,...)
Identifies the SQL function by its function signature, which uniquely identifies the function.
function-name
Gives the function name of the inlined SQL scalar function.

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

(parameter-type,...)
Specifies the number of input parameters of the function and the name and data type of each parameter.
(data-type,...)

Identifies the number of input parameters of the function and the data type of each parameter. The data type of each parameter must match the data type that was specified in the CREATE FUNCTION statement for the parameter in the corresponding position.

For data types that have a length, precision, or scale attribute, you can use a set of empty parentheses, specify a value, or accept the default values:

  • Empty parentheses indicate that Db2 is to ignore the attribute when determining whether the data types match.

    For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2). Similarly DECFLOAT() will be considered a match for DECFLOAT(16) or DECFLOAT(34).

    FLOAT cannot be specified with empty parentheses because its parameter value indicates different data types (REAL or DOUBLE).

  • If you use a specific value for a length, precision, or scale attribute, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

    The specific value for FLOAT(n) does not have to exactly match the defined value of the source function because 1<=n<=21 indicates REAL and 22<=n<=53 indicates DOUBLE. Matching is based on whether the data type is REAL or DOUBLE.

  • If length, precision, or scale is not explicitly specified and empty parentheses are not specified, the default length of the data type is implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

For data types with a subtype or encoding scheme attribute, specifying the FOR subtype DATA clause or the CCSID clause is optional. Omission of either clause indicates that Db2 is to ignore the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.

built-in-type
For descriptions of the built-in data types, see Data types and the description for built-in-type in CREATE TABLE statement.
distinct-type-name
The name of a distinct type. For more information, see Distinct types.

For more information on specification of the parameter list, see CREATE FUNCTION statement (overview).

A function with the function signature must exist in the explicitly or implicitly specified schema.

SPECIFIC FUNCTION specific-name
Identifies the function by its specific name. The name is implicitly or explicitly qualified with a schema name. The specific-name must identify a specific function that exists at the current server.