CREATE FUNCTION (external scalar)

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

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

• The CREATEIN privilege for the schema or all schemas
• SYSADM or SYSCTRL authority

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

Syntax

Bold text indicates clauses or options that are supported only syntactically.

CREATE FUNCTION function-name ([parameter-declaration[,...]])
RETURNS {data-type2 [AS LOCATOR] | 
    data-type3 CAST FROM data-type4 [AS LOCATOR]} option-list

option-list:

[SPECIFIC specific-name] 
[EXTERNAL [NAME {'string' | identifier}] LANGUAGE {ASSEMBLE | C | COBOL | PLI}]
[PARAMETER STYLE DB2SQL [DETERMINISTIC | NOT DETERMINISTIC] [FENCED]]
[RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT] 
[READS SQL DATA | NO SQL | MODIFIES SQL DATA | CONTAINS SQL]
[EXTERNAL ACTION | NO EXTERNAL ACTION] 
[NO SCRATCHPAD | SCRATCHPAD [100 | length]]
[FINAL CALL | NO FINAL CALL]
[ALLOW PARALLEL | DISALLOW PARALLEL]
[DBINFO | NODBINFO]
[NO COLLID | COLLID collection-id]
[WLM ENVIRONMENT {name | (name)}]
[ASUTIME NO LIMIT | ASUTIME LIMIT integer]
[STAY RESIDENT NO | STAY RESIDENT YES]
[PROGRAM TYPE SUB | PROGRAM TYPE MAIN]
[SECURITY DB2 | SECURITY {USER | DEFINED}]
[STOP AFTER SYSTEM DEFAULT FAILURES | 
 STOP AFTER integer FAILURES | 
 CONTINUE AFTER FAILURE]
[INHERIT SPECIAL REGISTERS | DEFAULT SPECIAL REGISTERS]
[RUN OPTIONS run-time-options]

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

Description

function-name

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

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 is a long SQL identifier. The name must not be any of the following system-reserved keywords even if you specify them as delimited identifiers:

The unqualified function name is implicitly qualified with a schema name according to the following rules:

• If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.
• If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

The qualified form of function-name is a short SQL identifier (the schema name) followed by a period and a long SQL identifier.

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:

• If the statement is embedded in a program, the owner is the authorization ID of the owner of the plan or package.
• If the statement is dynamically prepared, the owner is the SQL authorization ID in the CURRENT SQLID special register.

The owner is implicitly given the EXECUTE privilege with the GRANT option for the function.

(parameter-declaration,...)

Identifies the number of input parameters of the function, and specifies the data type of each parameter. All the parameters for a function are input parameters. 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 a long 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.

CREATE FUNCTION SQRT(DECIMAL(15,0)) 
    RETURNS DECIMAL(15,0) CAST FROM DOUBLE 

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.
• A distinct type parameter is passed as the source type of the distinct type.

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.

The XDB Server operating environment does not support the LOB data type at this time.

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. 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 SYSTABLESPACES.

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:

• 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 SQLID 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.

XDB Server operating environment does not support transition table parameters at this time.

RETURNS

Identifies the output of the function. Consider this clause in conjunction with the optional CAST FROM clause.

CREATE FUNCTION SQRT(DECIMAL(15,0)) 
    RETURNS DECIMAL(15,0) CAST FROM DOUBLE 

The value of data-type4 must not be a distinct type and must be castable to data-type3. The value for data-type3 can be any built-in data type or distinct type.

SPECIFIC specific-name

Specifies 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 a long SQL identifier. The qualified form is a short SQL identifier (the schema name) followed by a period and a long 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.

EXTERNAL

Specifies that the function being registered is based on code that is written in an external programming language and adheres to the documented linkage conventions and interface of that language.

If you do not specify the NAME clause, 'NAME function-name' is implicit. In this case, function-name must not be longer than 8 characters.

NAME 'string' or identifier

Identifies the name of the MVS load module that contains the user-written code that implements the logic of the function. The name can be a string constant that is no longer than 8 characters or a short identifier, The name must conform to the naming conventions for MVS load modules.

DB2 loads the load module when the function is invoked. The load module is created when the program that contains the function body is compiled and link-edited. The load module does not need to exist when the CREATE FUNCTION statement is executed. However, it must exist and be accessible by the current server when the function is invoked.

You can specify the EXTERNAL clause in one of the following ways:

EXTERNAL
EXTERNAL NAME PKJVSP1
EXTERNAL NAME 'PKJVSP1'

If you specify an external program name, you must use the NAME keyword. For example, this syntax is not valid:

EXTERNAL PKJVSP1

LANGUAGE

Specifies the application programming language in which the function program is written. All programs must be designed to run in IBM's Language Environment environment.

PARAMETER STYLE DB2SQL

Specifies the linkage convention that the function program uses to receive input parameters from and pass return values to the invoking SQL statement.

DB2SQL indicates that parameters for indicator variables are associated with each input and return value to allow for null values. The parameters that are passed between the invoking SQL statement and the function include:

• The first n parameters are the input parameters that are specified for the function
• A parameter for the result of the function
• n parameters for the indicator variables for the input parameters
• A parameter for the indicator variable for the result
• The SQLSTATE to be returned to DB2
• The qualified name of the function
• The specific name of the function
• The SQL diagnostic string to be returned to DB2

Zero to three additional parameters might also be passed:

• The scratchpad, if SCRATCHPAD is specified
• The call type, if NO FINAL CALL is specified
• The DBINFO structure, if DBINFO is specified

NOT DETERMINISTIC or DETERMINISTIC

Specifies whether the function returns the same results for identical input arguments.

FENCED

This clause has no functional equivalent in the XDB Server operating environment.

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT

Specifies whether the function is called if any of the input arguments is null at execution time.

NO SQL, MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL

Indicates whether the function can execute any SQL statements and, if so, what type.

EXTERNAL ACTION or NO EXTERNAL ACTION

This clause has no functional equivalent in the XDB Server operating environment.

NO SCRATCHPAD or SCRATCHPAD

Specifies whether XDB provides a scratchpad for the function. It is strongly recommended that external functions be reentrant, and a scratchpad provides an area for the function to save information from one invocation to the next.

A scratchpad has the following characteristics:

• length must be between 1 and 32767. The default value is 100 bytes.
• XDB initializes the scratchpad to all binary zeros (X'00''s).
• The scope of a scratchpad is the SQL statement. For each reference to the function in an SQL statement, there is one scratchpad. For example, assuming that function UDFX was defined with the SCRATCHPAD keyword, three scratchpads are allocated for the three references to UDFX in the following SQL statement:

  SELECT A, UDFX(A) FROM TABLEB 
      WHERE UDFX(A) > 103 OR UDFX(A) < 19;

  If the function is run under parallel tasks, one scratchpad is allocated for each parallel task of each reference to the function in the SQL statement. This can lead to unpredictable results. For example, if a function uses the scratchpad to count the number of times that it is invoked, the count reflects the number of invocations done by the parallel task and not the SQL statement. Specify the DISALLOW PARALLEL clause for functions that will not work correctly with parallelism.

• The scratchpad is persistent. XDB preserves its content from one invocation of the function to the next. Any changes that the function makes to the scratchpad on one call are still there on the next call. XDB initializes the scratchpads when it begins to execute an SQL statement. XDB does not reset scratchpads when a correlated subquery begins to execute.
• The scratchpad can be a central point for the system resources that the function acquires. If the function acquires system resources, specify FINAL CALL to ensure that XDB calls the function one more time so that the function can free those system resources.

  Each time the function invoked, XDB passes an additional argument to the function that contains the address of the scratchpad.

NO FINAL CALL or FINAL CALL

ALLOW or DISALLOW PARALLEL

This clause has no functional equivalent in the XDB Server operating environment.

NO DBINFO or DBINFO

Specifies whether specific information that XDB knows is passed to the function when it is invoked.

NO COLLID or COLLID collection-id

Identifies the package collection that is used when the function is executed.

WLM ENVIRONMENT

This clause has no functional equivalent in the XDB Server operating environment.

ASUTIME

Specifies the total amount of processor time, in CPU service units, that a single invocation of the function can run. The value is unrelated to the ASUTIME column of the resource limit specification table.

When you are debugging a function, setting a limit can be helpful if the function gets caught in a loop.

The XDB Server operating environment only provides NO LIMIT at this time.

STAY RESIDENT

Specifies whether the load module for the function remains resident in memory when the function ends.

The XDB Server provides an operating environment where the function does not remain resident.

PROGRAM TYPE

Specifies whether the function program runs as a main routine or a subroutine.

SECURITY

This clause has no functional equivalent in the XDB Server operating environment.

STOP AFTER SYSTEM DEFAULT FAILURES, STOP AFTER integer FAILURES, or CONTINUE AFTER FAILURE

Specifies whether the routine is to be put in a stopped state after some number of failures.

• STOP AFTER SYSTEM DEFAULT FAILURES.
• STOP AFTER integer FAILURES.
• CONTINUE AFTER FAILURE.

This clause is only implemented syntactically in the XDB Server operating environment.

RUN OPTIONS run-time-options

This clause has no functional equivalent in the XDB Server operating environment.

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. 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, using the following data types for parameters is recommended:

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

For portability of functions across platforms that are not DB2 for OS/390, 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.

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, 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)) ...

Overriding a built-in function

Giving an external function the same name as a built-in function is not a recommended practice unless you are trying to change the functionality of the built-in function.

If you do intend to create an external 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, XDB might choose the wrong function when the function is invoked with an unqualified function name. Thus, the application might fail, or perhaps even worse, run successfully but provide an inappropriate result.