CREATE FUNCTION (external table)

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 TABLE (column-name data-type [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]
    [DISALLOW PARALLEL]
    [DBINFO | NODBINFO]
    [CARDINALITY integer]
    [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}]
    [RUN OPTIONS run-time-options]
    [STOP AFTER SYSTEM DEFAULT FAILURES | 
     STOP AFTER integer FAILURES | 
     CONTINUE AFTER FAILURE]

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.

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

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

RETURNS TABLE(column-name data-type ...)

Identifies that the output of the function is a table. The parentheses that follow the keyword enclose the list of names and data types of the columns of the table.

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.

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

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

FINAL CALL or NO FINAL CALL

Specifies whether a first call and a final call are made to the function.

FINAL CALL

A first call and final call are made to the function in addition to one or more open, fetch, or close calls. FINAL CALL is the default.

NO FINAL CALL

A first call and final call are not made to the function.

The types of calls are:

• End of statement: When the cursor is closed for cursor-oriented statements, or the execution of the statement has completed.
• End of transaction: When normal end of statement processing does not occur. For example, the logic of an application, for some reason, bypasses closing the cursor.

If a commit operation occurs while a cursor defined as WITH HOLD is open, a final call is made when the cursor is closed or the application ends. If a commit occurs at the end of a parallel task, a final call is made regardless of whether a cursor defined as WITH HOLD is open.

If a commit, rollback, or abort operation causes the final call, the function cannot issue any SQL statements when it is invoked.

DISALLOW PARALLEL

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

NO DBINFO or DBINFO

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

CARDINALITY integer

Specifies an estimate of the expected number of rows that the function returns. The number is used for optimization purposes. The value of integer must range from 0 to 2147483647.

If you do not specify CARDINALITY, DB2 assumes a finite value. The finite value is the same value that DB2 assumes for tables for which the RUNSTATS utility has not gathered statistics.

If a function has an infinite cardinality--the function never returns the "end-of-table" condition and always returns a row, then a query that requires the "end-of-table" to work correctly will need to be interrupted. Thus, avoid using such functions in queries that involve GROUP BY and ORDER BY.

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.

RUN OPTIONS run-time-options

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.