CREATE FUNCTION (external table)

This CREATE FUNCTION statement registers a user-defined external table function with an application server.

A scalar function returns a single value each time it is invoked.

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:

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:

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

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
CHAR CHAR(1)
GRAPHIC GRAPHIC(1)
DECIMAL DECIMAL(5,0)
FLOAT DOUBLE (length of 8)
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.
column-name Specifies the name of the column. The name is a long identifier and must be unique within the RETURNS TABLE clause for the function.
data-type Specifies the data type of the column. The data type can be any built-in data type, except LONG VARCHAR or LONG VARGRAPHIC. The data type can also be any distinct type.
AS LOCATOR Specifies that the function returns a locator to the value rather than the actual value. You can specify AS LOCATOR only for a LOB data type or a distinct type based on a LOB data type.
ASSEMBLE The function is written in Assembler. (Not currently available in XDB Server.)
C The function is written in C or C++. (Not currently available in XDB Server.)
COBOL The function is written in COBOL, including the object-oriented language extensions.
PLI The function is written in PL/I. (Not currently available in XDB Server.)
NOT DETERMINISTIC The function might not return the same result for identical input arguments. The function depends on some state values that affect the results. XDB uses this information when processing a SELECT, UPDATE, DELETE, or INSERT statement to disable merging of views that refer to the function. An example of a function that is not deterministic is one that generates random numbers, or any function that contains SQL statements.

NOT DETERMINISTIC is the default.

Some functions that are not deterministic can receive incorrect results if the function is executed by parallel tasks. Specify the DISALLOW PARALLEL clause for these functions.

DETERMINISTIC The function always returns the same result for identical input arguments. XDB can use this information to optimize view processing for SELECT, UPDATE, DELETE, or INSERT statements. An example of a deterministic function is a function that calculates the square root of the input.

XDB does not verify that the function program is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.

RETURNS NULL ON INPUT The function is not called if any of the input arguments is null. The result is an empty table, which is a table with no rows. RETURNS NULL ON NULL INPUT is the default.
CALLED ON NULL INPUT The function is called regardless of whether any of the input arguments is null, making the function responsible for testing for null argument values. The function can return an empty table, depending on its logic.
NO SQL The function cannot execute any SQL statements.
READS SQL DATA The function cannot execute any SQL statements that modify data. SQL statements that are not supported in any function return a different error. READS SQL DATA is the default.
CONTAINS SQL The stored procedure cannot execute any SQL statements that read or modify data. SQL statements that are not supported in any function return a different error.
NO SCRATCHPAD A scratchpad is not allocated and passed to the function. NO SCRATCHPAD is the default.
SCRATCHPAD length When the function is invoked for the first time, XDB allocates memory for a scratchpad. A scratchpad has the following characteristics:
First call A first call occurs only if the function was defined with FINAL CALL. Before a first call, the scratchpad is set to binary zeros. Argument values are passed to the function, and the function might acquire memory or perform other one-time only resource initialization. However, the function should not return any data to XDB, but it can set return values for the SQL-state and diagnostic-message arguments.
Open call An open call occurs unless the function returns an error. The scratchpad is set to binary zeros only if the function was defined with NO FINAL CALL. Argument values are passed to the function, and the function might perform any one-time initialization actions that are required. However, the function should not return any data to XDB.
Fetch call A fetch call occurs unless the function returns an error during the first call or open call. Argument values are passed to the function, and XDB expects the function to return a row of data or the end-of-table condition. If a scratchpad is also passed to the function, it remains untouched from the previous call.
Close call A close call occurs unless the function returns an error during the first call, open call, or fetch call. No SQL-argument or SQL-argument-ind values are passed to the function, and if the function attempts to examine these values, unpredictable results may occur. If a scratchpad is also passed to the function, it remains untouched from the previous call.

The function should not return any data to XDB, but it can set return values for the SQL-state and diagnostic-message arguments. Also on close call, a function that is defined with NO FINAL CALL should release any system resources that it acquired. (A function that is defined with FINAL CALL should release any acquired resources on the final call.)

Final The final call balances the first call, and like the first call, occurs only if the function was defined with FINAL CALL. The function can set return values for the SQL-state and diagnostic-message arguments. The function should also release any system resources that it acquired. A final call occurs at these times:
NO DBINFO No additional information is passed. NO DBINFO is the default.
DBINFO An additional argument is passed when the function is invoked. The argument is a structure that contains information such as the application run-time authorization ID, the schema name, the name of a table or column that the function might be inserting into or updating, and identification of the database server that invoked the function.
NO COLLID The package collection for the function is the same as the package collection of the program that invokes the function. If a trigger invokes the function, the collection of the trigger package is used. If the invoking program does not use a package, XDB resolves the package by using the CURRENT PACKAGE PATH special register or the CURRENT PACKAGESET special register.
COLLID collection-id The name of the package collection that is used when the function is executed.
NO LIMIT There is no limit on the service units. NO LIMIT is the default.
LIMIT integer The limit on the service units is a positive integer in the range of 1 to 2GB. If the function uses more service units than the specified value, the function is canceled.
NO The load module is deleted from memory after the function ends. Use NO for non-reentrant functions. NO is the default.
YES The load module remains resident in memory after the function ends. Use YES for reentrant functions.
SUB The function runs as a subroutine. SUB is the default.
MAIN The function runs as a main routine.

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.

column-name Specifies the name of the column. The name is a long identifier and must be unique within the RETURNS TABLE clause for the function.
data-type Specifies the data type of the column. The data type can be any built-in data type, except LONG VARCHAR or LONG VARGRAPHIC. The data type can also be any distinct type.
AS LOCATOR Specifies that the function returns a locator to the value rather than the actual value. You can specify AS LOCATOR only for a LOB data type or a distinct type based on a LOB data 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.

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.

ASSEMBLE The function is written in Assembler. (Not currently available in XDB Server.)
C The function is written in C or C++. (Not currently available in XDB Server.)
COBOL The function is written in COBOL, including the object-oriented language extensions.
PLI The function is written in PL/I. (Not currently available in XDB Server.)

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.

NOT DETERMINISTIC The function might not return the same result for identical input arguments. The function depends on some state values that affect the results. XDB uses this information when processing a SELECT, UPDATE, DELETE, or INSERT statement to disable merging of views that refer to the function. An example of a function that is not deterministic is one that generates random numbers, or any function that contains SQL statements.

NOT DETERMINISTIC is the default.

Some functions that are not deterministic can receive incorrect results if the function is executed by parallel tasks. Specify the DISALLOW PARALLEL clause for these functions.

DETERMINISTIC The function always returns the same result for identical input arguments. XDB can use this information to optimize view processing for SELECT, UPDATE, DELETE, or INSERT statements. An example of a deterministic function is a function that calculates the square root of the input.

XDB does not verify that the function program is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.

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.

RETURNS NULL ON INPUT The function is not called if any of the input arguments is null. The result is an empty table, which is a table with no rows. RETURNS NULL ON NULL INPUT is the default.
CALLED ON NULL INPUT The function is called regardless of whether any of the input arguments is null, making the function responsible for testing for null argument values. The function can return an empty table, depending on its logic.

NO SQL, READS SQL DATA, or CONTAINS SQL

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

NO SQL The function cannot execute any SQL statements.
READS SQL DATA The function cannot execute any SQL statements that modify data. SQL statements that are not supported in any function return a different error. READS SQL DATA is the default.
CONTAINS SQL The stored procedure cannot execute any SQL statements that read or modify data. SQL statements that are not supported in any function return a different error.

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.

NO SCRATCHPAD A scratchpad is not allocated and passed to the function. NO SCRATCHPAD is the default.
SCRATCHPAD length When the function is invoked for the first time, XDB allocates memory for a scratchpad. 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.

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:

First call A first call occurs only if the function was defined with FINAL CALL. Before a first call, the scratchpad is set to binary zeros. Argument values are passed to the function, and the function might acquire memory or perform other one-time only resource initialization. However, the function should not return any data to XDB, but it can set return values for the SQL-state and diagnostic-message arguments.
Open call An open call occurs unless the function returns an error. The scratchpad is set to binary zeros only if the function was defined with NO FINAL CALL. Argument values are passed to the function, and the function might perform any one-time initialization actions that are required. However, the function should not return any data to XDB.
Fetch call A fetch call occurs unless the function returns an error during the first call or open call. Argument values are passed to the function, and XDB expects the function to return a row of data or the end-of-table condition. If a scratchpad is also passed to the function, it remains untouched from the previous call.
Close call A close call occurs unless the function returns an error during the first call, open call, or fetch call. No SQL-argument or SQL-argument-ind values are passed to the function, and if the function attempts to examine these values, unpredictable results may occur. If a scratchpad is also passed to the function, it remains untouched from the previous call.

The function should not return any data to XDB, but it can set return values for the SQL-state and diagnostic-message arguments. Also on close call, a function that is defined with NO FINAL CALL should release any system resources that it acquired. (A function that is defined with FINAL CALL should release any acquired resources on the final call.)

Final The final call balances the first call, and like the first call, occurs only if the function was defined with FINAL CALL. The function can set return values for the SQL-state and diagnostic-message arguments. The function should also release any system resources that it acquired. A final call occurs at these times:
  • 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.

NO DBINFO No additional information is passed. NO DBINFO is the default.
DBINFO An additional argument is passed when the function is invoked. The argument is a structure that contains information such as the application run-time authorization ID, the schema name, the name of a table or column that the function might be inserting into or updating, and identification of the database server that invoked the function.

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.

NO COLLID The package collection for the function is the same as the package collection of the program that invokes the function. If a trigger invokes the function, the collection of the trigger package is used. If the invoking program does not use a package, XDB resolves the package by using the CURRENT PACKAGE PATH special register or the CURRENT PACKAGESET special register.
COLLID collection-id The name of 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.

NO LIMIT There is no limit on the service units. NO LIMIT is the default.
LIMIT integer The limit on the service units is a positive integer in the range of 1 to 2GB. If the function uses more service units than the specified value, the function is canceled.

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.

NO The load module is deleted from memory after the function ends. Use NO for non-reentrant functions. NO is the default.
YES The load module remains resident in memory after the function ends. Use YES for reentrant functions.

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.

SUB The function runs as a subroutine. SUB is the default.
MAIN The function runs as a main routine.

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.

Example:

The following registers a table function written to return a row consisting of a single document identifier column for each known document in a text management system. The first parameter matches a given subject area and the second parameter contains a given string.

Within the context of a single session, the table function will always return the same table; therefore, it is defined as DETERMINISTIC. In addition, the DISALLOW PARALLEL keyword is added because table functions cannot operate in parallel.

Although the size of the output for DOCMATCH is highly variable, CARDINALITY 20 is a representative value, and is specified to help DB2. 

CREATE FUNCTION DOCMATCH (VARCHAR(30), VARCHAR(255))
        RETURNS TABLE (DOC_ID CHAR(16))
    EXTERNAL NAME ABC
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    NO SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    FENCED
    SCRATCHPAD
    FINAL CALL
    DISALLOW PARALLEL
    CARDINALITY 20;
Parent topic: SQL Statements