CREATE FUNCTION statement (external table function)
This CREATE FUNCTION statement registers a user-defined external table function with a database server. A user-defined external table function can be used in the FROM clause of a subselect. It returns a table to the subselect by returning one row at a time each time it is invoked.
Invocation for CREATE FUNCTION (external table)
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 (external table)
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
- Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.
If 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.
Additional privileges are required if the function uses a table as a parameter, refers to a distinct type, or is to run in a WLM (workload manager) environment. These privileges are:
- 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.
- Authority to create programs in the specified WLM environment. This authorization is obtained from an external security product, such as RACF®.
At least one of the following additional privileges is required if the SECURED option is specified
- SECADM authority
- CREATE_SECURE_OBJECT privilege
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 (external table)
- 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
- 2 This clause and the clauses that follow in the option-list can be specified in any order.
parameter-declaration:
parameter-type:
- 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
data-type:
built-in-type:
option-list: (The clauses in the option-list can be specified in any order.)
Description for CREATE FUNCTION (external table)
- 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
andDetermining the uniqueness of functions in a schema
in CREATE FUNCTION statement (overview). - (parameter-declaration,…)
- Identifies the number of input parameters of the function, and
specifies 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. The same name cannot be used for a parameter name and a column name.
- data-type
- Specifies
the data type of the input parameter. The data type can be a built-in
data type or a user-defined 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.
If you specify the name of the distinct type without a schema name, Db2 resolves the schema name by searching the schemas in the SQL path.
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. An error occurs if mixed data that actually contains DBCS characters is used as the value for an input parameter that is declared with an SBCS subtype.
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.
- 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 that is 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 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 an SQL identifier and must be unique within the RETURNS TABLE clause for the function.
- data-type
- Specifies the data type of the column. The column is nullable.
- 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.
- RETURNS GENERIC TABLE
- Specifies that the output of the function is a generic table. This option can only be specified if LANGUAGE C is also specified.
The names and data types of the columns must be declared when the table function is references using the typed-correlation-clause of the subselect.
For more information, see typed-correlation-clause.
- 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 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:
whereSQLxxxxxxxxxxxx
'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 or VARCHAR
-
- CCSID
- Indicates whether the encoding scheme for character
or 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 TABLE 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 or 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.
- VARCHAR
- Specifies that the representation of the values of varying length
character string-parameters, including, if applicable, the output
of the function, for functions which specify LANGUAGE C.
This option can only be specified if LANGUAGE C is also specified.
- NULTERM
- Specifies that variable length character string parameters are represented in a NUL-terminated string form.
- STRUCTURE
- Specifies that variable length character string parameters are represented in a VARCHAR structure form.
Using the PARAMETER VARCHAR clause, there is no way to specify the VARCHAR form of an individual parameter as these is with PARAMETER CCSID. The PARAMETER VARCHAR clause only applies to parameters in the parameter list of a function and in the RETURNS TABLE clause. It does not apply to system-generated parameters of the routine such as message tokens and DBINFO.
In a data sharing environment, you should not specify the PARAMETER VARCHAR clause until all members of the data sharing group support the clause. If some group members support this clause and others do not, and PARAMETER VARCHAR is specified in an external routine, the routine will encounter different parameter forms depending on which group member invokes the routine.
- 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 load module that contains the user-written
code that implements the logic of the function.
For other values of LANGUAGE, the name can be a string constant that is no longer than 8 characters. It must conform to the naming conventions for load modules. Alphabetical extenders for national languages can be used as the first character and as subsequent characters in the load module name.
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.
- ASSEMBLE
- The function is written in Assembler.
- C
- The function is written in C or C++. The VARCHAR clause can only be specified is LANGUAGE C is specified.
- COBOL
- The function is written in COBOL, including the object-oriented language extensions.
- PLI
- The function is written in PL/I.
- PARAMETER STYLE SQL
- Specifies
the linkage convention that the function program uses to receive input
parameters from and pass return values to the invoking SQL statement.
PARAMETER STYLE SQL specifies the parameter passing convention that supports passing null values both as input and for output.
If the RETURNS TABLE clause is specified, the parameters that are passed between the invoking SQL statement and the function include:- n parameters for the input parameters that are specified for the function
- m parameters for the result columns of the function that are specified on the RETURNS TABLE clause
- n parameters for the indicator variables for the input parameters
- m parameters for the indicator variables of the result columns of the function that are specified on the RETURNS TABLE clause
- 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
- The scratchpad, if SCRATCHPAD is specified
- The call type
- The DBINFO structure, if DBINFO is specified
If the RETURNS GENERIC TABLE clause is specified, the parameters that are passed between the invoking SQL statement and the function include:- n parameters for the input parameters that are specified for the function
- n parameters for the indicator variables for the input parameters
- m parameters for the result columns of the function that are specified on the RETURNS GENERIC TABLE clause
- A result table descriptor that contains the following:
- m result columns of the function that are specified in the typed-correlation-clause of the table-function-reference in a SELECT statement
- An array of m, 4-byte addresses to the values of the result columns
- An array of m, null indicators of the result columns
- 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
- The scratchpad, if SCRATCHPAD is specified
- The call type
- The DBINFO structure, if DBINFO is specified
For complete details about the structure of the parameter list that is passed, see DBINFO structure.
- NOT DETERMINISTIC or DETERMINISTIC
- Specifies whether the function
returns function returns the same results each time that the function
is invoked with the same input arguments.
- NOT DETERMINISTIC
- The function might not return the same results each time that
the function is invoked with the same input arguments. The function
depends on some state values that affect the results. Db2 uses this information to disable the merging
of views and table expressions when processing SELECT
and SQL data change statements that refer to this 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.
- DETERMINISTIC
- The function always returns the same result each time that the function is invoked with the same input arguments. An example of a deterministic function is a function that calculates the square root of the input. Db2 uses this information to enable the merging of views and table expressions for SELECT and SQL data change statements that refer to this function. DETERMINISTIC is not the default. If applicable, specify DETERMINISTIC to prevent non-optimal access paths from being chosen for SQL statements that refer to this function.
Db2 does not verify that the function program is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.
- FENCED
- Specifies
that the function runs in an external address space to prevent the
function from corrupting Db2 storage.
FENCED is the default.
- 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 NULL 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 INPUT is the default.
- CALLED ON NULL INPUT
- The function is called regardless of whether any of the input arguments are null, making the function responsible for testing for null argument values. The function can return an empty table, depending on its logic.
- READS SQL DATA, CONTAINS SQL, or NO SQL
- Specifies the classification of SQL statements and nested routines
that this routine can execute or invoke. The database manager verifies that the SQL statements
issued by the function, and all routines locally invoked by the routine, are consistent with this
specification; the verification is not performed when nested remote routines are invoked. For the
classification of each statement, see SQL statement data access classification for routines.
- READS SQL DATA
- Specifies that the function can execute statements with a data access indication of READS SQL DATA, CONTAINS SQL, or NO SQL. The function cannot execute SQL statements that modify data.
- CONTAINS SQL
- Specifies that the function can execute only SQL statements with an access indication of CONTAINS SQL or NO SQL. The function cannot execute statements that read or modify data.
- NO SQL
- Specifies that the function can execute only SQL statements with a data access classification of NO SQL.
- EXTERNAL ACTION or NO EXTERNAL ACTION
- Specifies whether the function
takes an action that changes the state of an object that Db2 does not manage. An example of an external
action is sending a message or writing a record to a file.
Because Db2 uses the RRS attachment for functions, Db2 can participate in two-phase commit with any other resource manager that uses RRS. For resource managers that do not use RRS, there is no coordination of commit or rollback operations on non-Db2 resources.
- EXTERNAL ACTION
- The function can take an action that changes the state of an object
that Db2 does not manage.
If you specify EXTERNAL ACTION, Db2:
- Materializes the views and table expressions in SELECT and SQL data change statements that refer to the function. This materialization can adversely affect the access paths that are chosen for the SQL statements that refer to this function. Do not specify EXTERNAL ACTION if the function does not have an external action.
- Does not move the function from one task control block (TCB) to another between FETCH operations.
- Does not allow another function or stored procedure to use the TCB until the cursor is closed. This is also applicable for cursors declared WITH HOLD.
The only changes to resources made outside of Db2 that are under the control of commit and rollback operations are those changes made under RRS control.
EXTERNAL ACTION is the default.
- NO EXTERNAL ACTION
- The function does not take any action that changes the state of
an object that Db2 does not
manage. Db2 uses this information
to enable the merging of views and table expressions for SELECT
and SQL data change statements that refer to this function. If
applicable, specify NO EXTERNAL ACTION to prevent non-optimal access
paths from being chosen for SQL statements that refer to this function.
Although the scope of global variables are beyond the scope of the routine, global variables can be set in the routine body when NO EXTERNAL ACTION is specified.
- NO PACKAGE PATH or PACKAGE PATH package-path
- Specifies
the packagecpath to use when the function is run. This is the list
of the possible package collections into which the DBRM this is associated
with the function is bound.
- NO PACKAGE PATH
- Specifies that the list of package collections for the function is the same as the list of package collection IDs for the program that invokes the function. If the program that invokes the function does not use a package, Db2 resolves the package by using the CURRENT PACKAGE PATH special register, the CURRENT PACKAGESET special register, or the PKLIST bind option (in this order). For information about how Db2 uses these three items, see Binding an application plan.
- PACKAGE PATH package-path
- Specifies a list of package collections, in the same format as
the SET CURRENT PACKAGE PATH special register.
If the COLLID clause is specified with PACKAGE PATH, the COLLID clause is ignored when the function is invoked.
The package-path value that is provided when the function is created is checked when the function is invoked. If package-path contains SESSION_USER (or USER), PATH, or PACKAGE PATH, an error is returned when the package-path value is checked.
- NO SCRATCHPAD or SCRATCHPAD
- Specifies
whether Db2 provides a scratchpad
for the function. It is strongly recommended that functions be reentrant,
and a scratchpad provides an area for the function to save information
from one invocation to the next.
- NO SCRATCHPAD
- Specifies that a scratchpad is not allocated and passed to the function. NO SCRATCHPAD is the default.
- SCRATCHPAD length
- Specifies that when the function is invoked for the first time, Db2 allocates memory for a scratchpad.
A scratchpad has the following characteristics:
- length must be in the range 1–32767. The default value is 100 bytes.
- Db2 initializes the scratchpad to all binary zeros (X'00''s).
- The scope of a scratchpad is the SQL statement. Each reference
to the function in an SQL statement has a scratchpad. For example,
assuming that function UDFX was defined with the SCRATCHPAD keyword,
two scratchpads are allocated for the two references to UDFX in the
following SQL statement:
SELECT * FROM TABLE (UDFX(A)), TABLE (UDFX(B));
- The scratchpad is persistent. Db2 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. Db2 initializes the scratchpads when it begins to execute an SQL statement. Db2 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 Db2 calls the function one more time so that the function can free those system resources.
Each time the function invoked, Db2 passes an additional argument to the function that contains the address of the scratchpad.
If you specify SCRATCHPAD, Db2:
- Does not move the function from one task control block (TCB) to another between FETCH operations.
- Does not allow another function or stored procedure to use the TCB until the cursor is closed. This is also applicable for cursors declared WITH HOLD.
- NO FINAL CALL or FINAL CALL
- Specifies
whether a first call and a final call are made to the
function.
- NO FINAL CALL
- A first call and final call are not made to the function. NO FINAL CALL is the default.
- FINAL CALL
- A first call and final call are made to the function in addition to one or more open, fetch, or close calls.
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 Db2, 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 Db2.
- 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 Db2 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 might 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 Db2, 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, rollback, or abort operation causes the final call, the function cannot issue any SQL statements when it is invoked.
- DISALLOW PARALLEL
- Specifies that Db2 does not consider parallelism for the function.
- NO DBINFO or DBINFO
- Specifies whether
additional status information 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. For details about the argument and its structure, see DBINFO structure.
- 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 0–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 theend-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 to be used when the function is executed.
This is the package collection into which the DBRM that is associated
with the function program is bound.
- 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, Db2 resolves the package by using the CURRENT PACKAGE PATH special register, the CURRENT PACKAGESET special register, or the PKLIST bind option (in this order). For details about how Db2 uses these three items, see the information on package resolution in Overriding the values that Db2 uses to resolve package lists.
NO COLLID is the default.
- COLLID collection-id
- The name of the package collection that is to be used when the external is executed.
- WLM ENVIRONMENT
- Identifies the WLM (workload manager) application environment in which the function is to run. The name of the WLM environment is an SQL identifier.
If you do not specify WLM ENVIRONMENT, the function runs in the WLM-established stored procedure address space that is specified at installation time.
- name
- The WLM environment in which the function must run. If another user-defined function or a stored procedure calls the function and that calling routine is running in an address space that is not associated with the WLM environment, Db2 routes the function request to a different address space.
- (name,*)
- When an SQL application program directly invokes the function, the WLM environment in which the function runs.
If another user-defined function or a stored procedure calls the function, the function runs in same environment that the calling routine uses. In this case, authorization to run the function in the WLM environment is not checked because the authorization of the calling routine suffices.
Users must have the appropriate authorization to execute functions in the specified WLM 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. For information on service units, see z/OS MVS Initialization and Tuning Guide.
- NO LIMIT
- There is no limit on the service units. NO LIMIT is the default.
- LIMIT integer
- The limit on the number of CPU service units is a positive integer in the range 1–2147483647. If the procedure uses more service units than the specified value, Db2 cancels the procedure. The CPU cycles that are consumed by parallel tasks in a procedure do not contribute towards the specified ASUTIME LIMIT.
- STAY RESIDENT
- Specifies
whether the load module for the function is to remain 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.
- 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
- Specifies
how the function interacts with an external security product, such
as RACF, to control access
to non-SQL resources.
- Db2
- The function does not require an external security environment.
If the function accesses resources that an external security product
protects, the access is performed using the authorization ID that
is associated with the WLM-established stored procedure address space.
Db2 is the default.
- USER
- An external security environment should be established for the function. If the function accesses resources that the external security product protects, the access is performed using the primary authorization ID of the process that invoked the function.
- DEFINER
- An external security environment should be established for the function. If the function accesses resources that the external security product protects, the access is performed using the authorization ID of the owner of the function.
- RUN OPTIONS runtime-options
- Specifies
the Language Environment run
time options to be used for the function. You must specify runtime-options as
a character string that is no longer than 254 bytes. If you do not
specify RUN OPTIONS or pass an empty string, Db2 does not pass any run time options to Language Environment,
and Language Environment uses
its installation defaults.
For a description of the Language Environment run time options, see Language Environment Programming Reference.
- INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS
- Specifies how special registers are set on entry to the routine.
- INHERIT SPECIAL REGISTERS
- Specifies that the values of special registers are inherited according to the rules listed in the table for characteristics of special registers in a user-defined function in Special registers in a user-defined function or a stored procedure.
- DEFAULT SPECIAL REGISTERS
- Specifies that special registers are initialized to the default values, as indicated by the rules in the table for characteristics of special registers in a user-defined function in Special registers in a user-defined function or a stored procedure.
- STATIC DISPATCH
- At function resolution time, Db2 chooses a function based on the static (or declared) types of the function parameters. STATIC DISPATCH is the default.
- STOP AFTER SYSTEM DEFAULT FAILURES, STOP AFTER nn 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
- Specifies that this routine should be placed in a stopped state after the number of failures indicated by the value of field MAX ABEND COUNT on installation panel DSNTIPX. This is the default.
- STOP AFTER nn FAILURES
- Specifies that this routine should be placed in a stopped state after nn failures. The value nn can be an integer 1–32767.
- CONTINUE AFTER FAILURE
- Specifies that this routine should not be placed in a stopped state after any failure.
- NOT SECURED or SECURED
- Specifies if the function is considered secure for row access
control and column access control.
- NOT SECURED
- Specifies that the function is not considered as secure for row
access control and column access control.
NOT SECURED is the default.
When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled when the table is using active column access control.
- SECURED
- Specifies that the function is considered secure for row access
control and column access control.
The function must be defined with SECURED when it is referenced in a row permission or a column mask.
Notes for CREATE FUNCTION (external table)
- Considerations for all types of user-defined functions:
- For considerations that apply to all types of user-defined functions, see CREATE FUNCTION statement (overview).
- Character string representation considerations:
- The PARAMETER VARCHAR clause is specific to LANGUAGE C functions because of the native use of NUL-terminated strings in C. VARCHAR structure representation is useful when character string data is known to contain embedded NUL-terminators. It is also useful when it cannot be guaranteed that character string data does not contain embedded NUL-terminators.
PARAMETER VARCHAR does not apply to fixed length character strings, VARCHAR FOR BIT DATA, CLOB, DBCLOB, or implicitly generated parameters. The clause does not apply to VARCHAR FOR BIT DATA because BIT DATA can contain X'00' characters, and its value representation starts with length information. It does not apply to LOB data because a LOB value representation starts with length information.
PARAMETER VARCHAR does not apply to optional parameters that are implicitly provided to an external function. For example, a CREATE FUNCTION statement for LANGUAGE C must also specify PARAMETER STYLE SQL, which returns an SQLSTATE NULL-terminated character string; that SQLSTATE will not be represented in VARCHAR structured form. Likewise, none of the parameters that represent the qualified name of the function, the specific name of the function, or the SQL diagnostic string that is returned to the database manager will be represented in VARCHAR structured form.
- Running external functions in WLM environments:
- You can use the WLM ENVIRONMENT clause to identify the address space in which a function or is to run. Using different WLM environments lets you isolate one group of programs from another. For example, you might choose to isolate programs based on security requirements and place all payroll applications in one WLM environment because those applications deal with data, such as employee salaries.
To prevent a user from defining functions in sensitive WLM environments, Db2 invokes the external security manager to determine whether the user has authorization to issue CREATE FUNCTION statements that refer to the specified WLM environment. The following example shows the RACF command that authorizes Db2 user DB2USER1 to register a function on Db2 subsystem DB2A that runs in the WLM environment named PAYROLL.
PERMIT DB2A.WLMENV.PAYROLL CLASS(DSNR) ID(DB2USER1) ACCESS(READ)
For more information, see Managing authorizations for creation of stored procedures in WLM environments.
- Determining if a table function is a generic table function:
- To identify if a table function is a generic table function, you can query the SYSIBM.SYSROUTINES catalog table. The function is a generic table function if the value of the RESULT_COLS column is 0 (zero) when the value of the ROUTINETYPE column if 'F' and the value of the FUNCTIONTYPE column is 'T'.
- Alternative syntax and synonyms:
- To provide compatibility with previously releases of Db2 or other products in the Db2 family, Db2 supports the following alternative
syntax:
- VARIANT as a synonym for NOT DETERMINISTIC
- NOT VARIANT as a synonym for DETERMINISTIC
- NOT NULL CALL as a synonym for RETURNS NULL ON NULL INPUT
- NULL CALL as a synonym for CALLED ON NULL INPUT
- PARAMETER STYLE DB2SQL as a synonym for PARAMETER STYLE SQL
- TIMEZONE can be specified as an alternative to TIME ZONE.
Example for CREATE FUNCTION (external table)
The following example 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 always returns the same table; therefore, it is defined as DETERMINISTIC. In addition, the DISALLOW PARALLEL keyword is added because table functions cannot operate in parallel.
CREATE FUNCTION DOCMATCH (VARCHAR(30), VARCHAR(255))
RETURNS TABLE (DOC_ID CHAR(16))
EXTERNAL NAME ABC
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
FENCED
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL
CARDINALITY 20;
CREATE FUNCTION tf6(p1 VARCHAR(10))
RETURNS GENERIC TABLE
EXTERNAL NAME 'tf6'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NO EXTERNAL ACTION
FENCED
SCRATCHPAD
FINAL CALL;
Note that LANGUAGE C must be specified, and the names and data type of the
result columns must be declared when the table function is referenced in the SELECT clause.