This statement is used to register a user-defined external table function with an application server.
A table function may be used in the FROM clause of a SELECT, and returns a table to the SELECT by returning one row at a time.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
To create a not-fenced function, the privileges held by the authorization ID of the statement must also include at least one of the following:
To create a fenced function, no additional authorities or privileges are required.
If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised.
Syntax
>>-CREATE FUNCTION--function-name------------------------------->
>----(--+--------------------------------------------------------+---)->
| .-,------------------------------------------------. |
| V | |
'----+----------------+--data-type1--+-------------+--+--'
'-parameter-name-' '-AS LOCATOR--'
>----*---------------------------------------------------------->
.-,------------------------------------------.
V |
>----RETURNS TABLE--(-----column-name--data-type2--+-------------+--+---)->
'-AS LOCATOR--'
>----*----+--------------------------+--*----------------------->
'-SPECIFIC--specific-name--'
>----EXTERNAL--+-----------------------+---*-------------------->
'-NAME--+-'string'---+--'
'-identifier-'
(1)
>----LANGUAGE--+-C----+---------*------------------------------->
+-JAVA-+
'-OLE--'
>----PARAMETER STYLE--+-DB2SQL-----+---*------------------------>
'-DB2GENERAL-'
.-NOT DETERMINISTIC--. .-FENCED-----.
>-----+--------------------+--*----+------------+--*------------>
| (2) | '-NOT FENCED-'
'-DETERMINISTIC------'
.-RETURNS NULL ON NULL INPUT--.
>-----+-----------------------------+--*--NO SQL--*------------->
| (3) |
'-CALLED ON NULL INPUT--------'
.-EXTERNAL ACTION----. .-NO SCRATCHPAD----------.
>-----+--------------------+--*----+------------------------+--->
'-NO EXTERNAL ACTION-' | .-100----. |
'-SCRATCHPAD--+--------+-'
'-length-'
.-NO FINAL CALL--.
>----*----+----------------+--*---DISALLOW PARALLEL---*--------->
'-FINAL CALL-----'
.-NO DBINFO--.
>-----+------------+--*----+-----------------------+--*--------->
'-DBINFO-----' '-CARDINALITY--integer--'
>-----+------------------------------+-------------------------><
'-TRANSFORM GROUP--group-name--'
Notes:
Description
The name, including the implicit or explicit qualifiers, together with the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) must not identify a function described in the catalog (SQLSTATE 42723). The unqualified name, together with the number and data types of the parameters, while of course unique within its schema, need not be unique across schemas.
If a two-part name is specified, the schema-name cannot begin with "SYS" (SQLSTATE 42939).
A number of names used as keywords in predicates are reserved for system use, and may not be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH and the comparison operators as described in Basic Predicate.
The same name can be used for more than one function if there is some difference in the signature of the functions. Although there is no prohibition against it, an external user-defined table function should not be given the same name as a built-in function.
It is possible to register a function that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example,
CREATE FUNCTION WOOFER() ...
No two identically-named functions within a schema are permitted to have exactly the same type for all corresponding parameters. Lengths, precisions and scales are not considered in this type comparison. Therefore CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3). There is some further bundling of types that causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. A duplicate signature raises an SQL error (SQLSTATE 42723).
For example, given the statements:
CREATE FUNCTION PART (INT, CHAR(15)) ... CREATE FUNCTION PART (INTEGER, CHAR(40)) ... CREATE FUNCTION ANGLE (DECIMAL(12,2)) ... CREATE FUNCTION ANGLE (DEC(10,7)) ...
the second and fourth statements would fail because they are considered to be a duplicate functions.
Here is an example which illustrates the use of the AS LOCATOR clause in parameter definitions:
CREATE FUNCTION foo ( CLOB(10M) AS LOCATOR, IMAGE AS LOCATOR)
...
which assumes that IMAGE is a distinct type based on one of the LOB types.
Note also that for argument promotion purposes, the AS LOCATOR clause has no effect. In the example the types are considered to be CLOB and IMAGE respectively, which would mean that a CHAR or VARCHAR argument could be passed to the function as the first argument. Likewise, the AS LOCATOR has no effect on the function signature, which is used in matching the function (a) when referenced in DML, by a process called "function resolution", and (b) when referenced in a DDL statement such as COMMENT ON or DROP. In fact the clause may or may not be used in COMMENT ON or DROP with no significance.
An error (SQLSTATE 42601) is raised if AS LOCATOR is specified for a type other than a LOB or a distinct type based on a LOB.
If the function is FENCED, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
The valid types for use with this clause are discussed on page ***.
The specific-name may be the same as an existing function-name.
If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error (SQLSTATE 42882) is raised.
If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.
If NAME clause is not specified "NAME function-name" is assumed.
The 'string' option is a string constant with a maximum of 254 characters. The format used for the string is dependent on the LANGUAGE specified.
The string specified is the library name and function within library, which the database manager invokes to execute the user-defined function being CREATEd. The library (and the function within the library) do not need to exist when the CREATE FUNCTION statement is performed. However, when the function is used in an SQL statement, the library and function within the library must exist and be accessible from the database server machine.
>>-'--+-library_id-------+---+-------------+---'---------------><
'-absolute_path_id-' '-!--func_id--'
Extraneous blanks are not permitted within the single quotes.
If 'myfunc' were the library_id in a UNIX-based system it would cause the database manager to look for the function in library /u/production/sqllib/function/myfunc, provided the database manager is being run from /u/production.
For OS/2, and Windows 32-bit operating systems, the database manager will look in the LIBPATH or PATH if the library_id is not located in the function directory.
In OS/2 the library_id should not contain more than 8 characters.
In a UNIX-based system, for example, '/u/jchui/mylib/myfunc' would cause the database manager to look in /u/jchui/mylib for the myfunc function.
In OS/2, and Windows 32-bit operating systems 'd:\mylib\myfunc' would cause the database manager to load the myfunc.dll file from the d:\mylib directory.
In OS/2 the last part of this specification (i.e. the name of the dll), should not contain more than 8 characters.
In a UNIX-based system, for example, 'mymod!func8' would direct the database manager to look for the library $inst_home_dir/sqllib/function/mymod and to use entry point func8 within that library.
In OS/2, and Windows 32-bit operating systems, 'mymod!func8' would direct the database manager to load the mymod.dll file and call the func8() function in the dynamic link library (DLL).
If the string is not properly formed, an error (SQLSTATE 42878) is raised.
In any case, the body of every external function should be in a directory that is available on every partition of the database.
The string specified contains the optional jar file identifier, class identifier and method identifier, which the database manager invokes to execute the user-defined function being CREATEd. The class identifier and method identifier do not need to exist when the CREATE FUNCTION statement is performed. If a jar_id is specified, it must exist when the CREATE FUNCTION statement is executed. However, when the function is used in an SQL statement, the method identifier must exist and be accessible from the database server machine.
>>-'----+------------+--class_id---+-.-+--method_id--'---------><
'-jar_name :-' '-!-'
Extraneous blanks are not permitted within the single quotes.
The string specified is the OLE programmatic identifier (progid) or class identifier (clsid), and method identifier, which the database manager invokes to execute the user-defined function being CREATEd. The programmatic identifier or class identifier, and method identifier do not need to exist when the CREATE FUNCTION statement is performed. However, when the function is used in an SQL statement, the method identifier must exist and be accessible from the database server machine, otherwise an error (SQLSTATE 42724) is raised.
>>-'--+-progid-+---!--method_id--'-----------------------------><
'-clsid--'
Extraneous blanks are not permitted within the single quotes.
progid is not interpreted by the database manager but only forwarded to the OLE APIs at run time. The specified OLE object must be creatable and support late binding (also called IDispatch-based binding).
{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}
where 'n' is an alphanumeric character. clsid is not interpreted by the database manager but only forwarded to the OLE APIs at run time.
LANGUAGE OLE is only supported for user-defined functions stored in DB2 for Windows 32-bit operating systems.
Refer to CREATE FUNCTION (OLE DB External Table) for creating LANGUAGE OLEDB external table functions.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
If a function is registered as FENCED, the database manager insulates its internal resources (e.g. data buffers) from access by the function. Most functions will have the option of running as FENCED or NOT FENCED. In general, a function running as FENCED will not perform as well as a similar one running as NOT FENCED.
| Warning: | Use of NOT FENCED for functions not adequately coded, reviewed and tested can
compromise the integrity of DB2. DB2 takes some precautions against
many of the common types of inadvertent failures that might occur, but cannot
guarantee complete integrity when NOT FENCED user defined functions are
used.
Note that, while the use of FENCED does offer a greater degree of protection for database integrity, a FENCED UDF that has not been adequately coded, reviewed and tested can cause an inadvertent failure of DB2. |
Most user-defined functions should be able to run either as FENCED or NOT FENCED. Only FENCED can be specified for a function with LANGUAGE OLE (SQLSTATE 42613).
To change from FENCED to NOT FENCED, the function must be re-registered (by first dropping it and then re-creating it). Either SYSADM authority, DBADM authority or a special authority (CREATE_NOT_FENCED) is required to register a user-defined function as NOT FENCED.
If the function is FENCED, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
If RETURNS NULL ON NULL INPUT is specified, and if, at table function OPEN time, any of the function's arguments are null, then the user-defined function is not called. The result of the attempted table function scan is the empty table (a table with no rows).
If CALLED ON NULL INPUT is specified, then regardless of whether any arguments are null, the user-defined function is called. It can return a null value or a normal (non-null) value. But responsibility for testing for null argument values lies with the UDF.
The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatibility. Similarly, NOT NULL CALL may be used as a synonym for RETURNS NULL ON NULL INPUT.
If SCRATCHPAD is specified, then at first invocation of the user-defined function, memory is allocated for a scratchpad to be used by the external function. This scratchpad has the following characteristics:
SELECT A.C1, B.C2
FROM TABLE (UDFX(:hv1)) AS A,
TABLE (UDFX(:hv1)) AS B
WHERE ...
If NO FINAL CALL is specified or defaulted, then the external table function should clean up any such resources on the CLOSE call, as DB2 will re-initialize the scratchpad on each OPEN call. This determination of FINAL CALL or NO FINAL CALL and the associated behavior of the scratchpad could be an important consideration, particularly if the table function will be used in a subquery or join, since that is when multiple OPEN calls can occur during the execution of a statement.
(As outlined above, the FINAL CALL/NO FINAL CALL keyword is used to control the re-initialization of the scratchpad, and also dictates when the external table function should release resources anchored in the scratchpad.)
If SCRATCHPAD is specified, then on each invocation of the user-defined function an additional argument is passed to the external function which addresses the scratchpad.
If NO SCRATCHPAD is specified then no scratchpad is allocated or passed to the external function.
For external table functions, the call-type argument is ALWAYS present, regardless of which option is chosen. See Application Development Guide for more information about this argument and its values.
A description of the table UDF processing of these calls when errors occur is included in the Application Development Guide.
If DBINFO is specified, then a structure is passed to the UDF which contains the following information:
Please see the Application Development Guide for detailed information on the structure and how it is passed to the UDF.
If the CARDINALITY clause is not specified for a table function, DB2 will assume a finite value as a default- the same value assumed for tables for which the RUNSTATS utility has not gathered statistics.
Warning: if a function does in fact have infinite cardinality, i.e. it returns a row every time it is called to do so, never returning the "end-of-table" condition, then queries which require the "end-of-table" condition to correctly function will be infinite, and will have to be interrupted. Examples of such queries are those involving GROUP BY and ORDER BY. The user is advised to not write such UDFs.
Notes
Examples
Example 1: 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 UDF will always return the same table, and therefore it is defined as DETERMINISTIC. Note the RETURNS clause which defines the output from DOCMATCH. FINAL CALL must be specified for each table function. In addition, the DISALLOW PARALLEL keyword is added as 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 the DB2 optimizer.
CREATE FUNCTION DOCMATCH (VARCHAR(30), VARCHAR(255))
RETURNS TABLE (DOC_ID CHAR(16))
EXTERNAL NAME '/common/docfuncs/rajiv/udfmatch'
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
NOT FENCED
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL
CARDINALITY 20
Example 2: The following registers an OLE table function that is used to retrieve message header information and the partial message text of messages in Microsoft Exchange. For an example of the code that implements this table function, see the Application Development Guide.
CREATE FUNCTION MAIL()
RETURNS TABLE (TIMERECIEVED DATE,
SUBJECT VARCHAR(15),
SIZE INTEGER,
TEXT VARCHAR(30))
EXTERNAL NAME 'tfmail.header!list'
LANGUAGE OLE
PARAMETER STYLE DB2SQL
NOT DETERMINISTIC
FENCED
CALLED ON NULL INPUT
SCRATCHPAD
FINAL CALL
NO SQL
EXTERNAL ACTION
DISALLOW PARALLEL