This statement is used to register a user-defined external scalar function with an application server. A scalar function returns a single value each time it is invoked, and is in general valid wherever an SQL expression is valid
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--'
>----*---------------------------------------------------------->
>----RETURNS--+-data-type2--+-------------+------------------------+>
| '-AS LOCATOR--' |
'-data-type3--CAST FROM--data-type4--+-------------+-'
'-AS LOCATOR--'
>----*----+--------------------------+--*----------------------->
'-SPECIFIC--specific-name--'
>-----EXTERNAL--+----------------------+---*-------------------->
'-NAME--+-'string'---+-'
'-identifier-'
(1)
>----LANGUAGE--+-C----+---------*------------------------------->
+-JAVA-+
'-OLE--'
>----PARAMETER STYLE--+-DB2SQL-----+--*------------------------->
+-DB2GENERAL-+
'-JAVA-------'
.-NOT DETERMINISTIC--. .-FENCED-----.
>-----+--------------------+--*----+------------+--------------->
| (2) | '-NOT FENCED-'
'-DETERMINISTIC------'
.-RETURNS NULL ON NULL INPUT--. (3)
>-----+-----------------------------+---------*---NO SQL--*----->
'-CALLED ON NULL INPUT--------'
.-EXTERNAL ACTION----. .-NO SCRATCHPAD----------.
>-----+--------------------+--*----+------------------------+--->
'-NO EXTERNAL ACTION-' | .-100----. |
'-SCRATCHPAD--+--------+-'
'-length-'
.-NO FINAL CALL--. .-ALLOW PARALLEL----.
>----*----+----------------+--*----+-------------------+--*----->
'-FINAL CALL-----' '-DISALLOW PARALLEL-'
.-NO DBINFO--.
>-----+------------+--*----+------------------------------+--*-->
'-DBINFO-----' '-TRANSFORM GROUP--group-name--'
>-----+-------------------------------------------------+------><
'-PREDICATES--(--| predicate-specification |--)---'
predicate-specification
|---WHEN--+- = -+---+-constant------------------------+-------->
+- <> -+ '-EXPRESSION AS--expression-name--'
+- < -+
+- > -+
+- <= -+
'- >= -'
>-----+-| data-filter |--+-------------------------+-+----------|
| '-| index-exploitation |--' |
'-| index-exploitation |--+------------------+-'
'-| data-filter |--'
data-filter
|---FILTER USING--+-function-invocation-+-----------------------|
'-case-expression-----'
index-exploitation
|---SEARCH BY--+--------+---INDEX EXTENSION--index-extension-name-->
'-EXACT--'
.--------------------------.
V |
>-------| exploitation-rule |---+-------------------------------|
exploitation-rule
|---WHEN KEY--(--parameter-name1--)----------------------------->
.-,------------------.
V |
>----USE--search-method-name--(-----parameter-name2---+---)-----|
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 or method 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". Otherwise, an error (SQLSTATE 42939) is raised.
A number of names used as keywords in predicates are reserved for system use, and may not be used as a function-name. 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. Failure to observe this rule will lead to an error (SQLSTATE 42939).
In general, 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 function should not be given the same name as a built-in function, unless it is an intentional override. To give a function having a different meaning the same name (for example, LENGTH, VALUE, MAX), with consistent arguments, as a built-in scalar or column function, is to invite trouble for dynamic SQL statements, or when static SQL applications are rebound; the application may fail, or perhaps worse, may appear to run successfully while providing a different result.
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 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).
In this case, exactly the same considerations apply as for the parameters of external functions described above under data-type1 for function parameters.
This form of the RETURNS clause is used to return a different data type to the invoking statement from the data type that was returned by the function code. For example, in
CREATE FUNCTION GET_HIRE_DATE(CHAR(6))
RETURNS DATE CAST FROM CHAR(10)
...
the function code returns a CHAR(10) value to the database manager, which, in turn, converts it to a DATE and passes that value to the invoking statement. The data-type4 must be castable to the data-type3 parameter. If it is not castable, an error (SQLSTATE 42880) is raised (for the definition of castable, see Casting Between Data Types).
Since the length, precision or scale for data-type3 can be inferred from data-type4, it not necessary (but still permitted) to specify the length, precision, or scale for parameterized types specified for data-type3. Instead empty parentheses may be used (for example VARCHAR() may be used). FLOAT() cannot be used (SQLSTATE 42601) since parameter value indicates different data types (REAL or DOUBLE).
Distinct types and structured types are not valid as the type specified in data-type4 (SQLSTATE 42815).
The cast operation is also subject to run-time checks that might result in conversion errors being raised.
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, otherwise an error (SQLSTATE 42724) is raised.
>>-'--+-library_id-------+---+-------------+---'---------------><
'-absolute_path_id-' '-!--func_id--'
Extraneous blanks are not permitted within the single quotes.
'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 shared library.
In OS/2, and Windows 32-bit operating systems, 'd:\mylib\myfunc' would cause the database manager to load dynamic link library, 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.
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, otherwise an error (SQLSTATE 42724) is raised.
>>-'----+----------+--class_id----+-.-+--method_id--'----------><
'-jar_id :-' '-!-'
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.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
Refer to Application Development Guide for details on passing parameters.
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 than NOT FENCED, a FENCED UDF that has not been adequately coded, reviewed and tested can also 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).
If the function is FENCED, the AS LOCATOR clause cannot be specified (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 RETURNS NULL ON NULL INPUT is specified, and if, at execution time, any one of the function's arguments is null, then the user-defined function is not called and the result is the null value.
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, UDFX(A) FROM TABLEB
WHERE UDFX(A) > 103 OR UDFX(A) < 19
If ALLOW PARALLEL is specified or defaulted to, then the scope is different from the above. If the function is executed in multiple partitions, a scratchpad would be assigned in each partition where the function is processed, for each reference to the function in the SQL statement. Similarly, if the query is executed with intra-partition parallelism enabled, more than three scratchpads may be assigned.
(In such a case where system resource is acquired, the FINAL CALL keyword should also be specified; this causes a special call to be made at end-of-statement to allow the external function to free any system resources acquired.)
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.
SCRATCHPAD is not supported for PARAMETER STYLE JAVA functions.
If a commit operation occurs while a cursor defined as WITH HOLD is open, a final call is made at the subsequent close of the cursor or at the end of the application.
If NO FINAL CALL is specified then no "call type" argument is passed to the external function, and no final call is made.
A description of the scalar UDF processing of these calls when errors occur is included in the Application Development Guide.
FINAL CALL is not supported for PARAMETER STYLE JAVA functions.
In any case, the body of every external function should be in a directory that is available on every partition of the database.
The syntax diagram indicates that the default value is ALLOW PARALLEL. However, the default is DISALLOW PARALLEL if one or more of the following options is specified in the statement:
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 user-defined function.
The transform functions, both FROM SQL and TO SQL, whether designated or implied, must be SQL functions which properly transform between the structured type and its built in type attributes.
This function can use any parameter-name, the expression-name, or constants as arguments (SQLSTATE 42703), and returns an integer (SQLSTATE 428E4). A return value of 1 means the row is kept, otherwise it is discarded.
This function must also:
If an argument invokes another function or method, these four rules are also enforced for this nested function or method. However, system generated observer methods are allowed as arguments to the filter function (or any function or method used as an argument), as long as the argument evaluates to a built-in data type.
Subqueries cannot be used anywhere in the case-expression (SQLSTATE 428E4).
The case expression must return an integer (SQLSTATE 428E4). A return value of 1 in the result-expression means that the row is kept, otherwise it is discarded.
If EXACT is not specified, then the original user-defined predicate is applied after index lookup. If the index is expected to provide only an approximation of the predicate, do not specify the EXACT option.
If the index lookup is not used, then the filter function and the original predicate have to be applied.
The data type of parameter-name1 must match that of the source key specified in the index extension (SQLSTATE 428EY). The match must be exact for built-in and distinct data types and within the same structured type hierarchy for structured types.
This clause is true when the values of the named parameter are columns that are covered by an index based on the index extension specified.
Notes
Examples
Example 1: Pellow is registering the CENTRE function in his PELLOW schema. Let those keywords that will default default, and let the system provide a function specific name:
CREATE FUNCTION CENTRE (INT,FLOAT)
RETURNS FLOAT
EXTERNAL NAME 'mod!middle'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
Example 2: Now, McBride (who has DBADM authority) is registering another CENTRE function in the PELLOW schema, giving it an explicit specific name for subsequent data definition language use, and explicitly providing all keyword values. Note also that this function uses a scratchpad and presumably is accumulating data there that affects subsequent results. Since DISALLOW PARALLEL is specified, any reference to the function is not parallelized and therefore a single scratchpad is used to perform some one-time only initialization and save the results.
CREATE FUNCTION PELLOW.CENTRE (FLOAT, FLOAT, FLOAT)
RETURNS DECIMAL(8,4) CAST FROM FLOAT
SPECIFIC FOCUS92
EXTERNAL NAME 'effects!focalpt'
LANGUAGE C PARAMETER STYLE DB2SQL
DETERMINISTIC FENCED NOT NULL CALL NO SQL NO EXTERNAL ACTION
SCRATCHPAD NO FINAL CALL
DISALLOW PARALLEL
Example 3: The following is the C language user-defined function program written to implement the rule:
output = 2 * input - 4
returning NULL if and only if the input is null. It could be written even more simply (that is, without the null checking), if the CREATE FUNCTION statement had used NOT NULL CALL. Further examples of user-defined function programs can be found in the Application Development Guide. The CREATE FUNCTION statement:
CREATE FUNCTION ntest1 (SMALLINT)
RETURNS SMALLINT
EXTERNAL NAME 'ntest1!nudft1'
LANGUAGE C PARAMETER STYLE DB2SQL
DETERMINISTIC NOT FENCED NULL CALL
NO SQL NO EXTERNAL ACTION
The program code:
#include "sqlsystm.h"
/* NUDFT1 IS A USER_DEFINED SCALAR FUNCTION */
/* udft1 accepts smallint input
and produces smallint output
implementing the rule:
if (input is null)
set output = null;
else
set output = 2 * input - 4;
*/
void SQL_API_FN nudft1
(short *input, /* ptr to input arg */
short *output, /* ptr to where result goes */
short *input_ind, /* ptr to input indicator var */
short *output_ind, /* ptr to output indicator var */
char sqlstate[6], /* sqlstate, allows for null-term */
char fname[28], /* fully qual func name, nul-term */
char finst[19], /* func specific name, null-term */
char msgtext[71]) /* msg text buffer, null-term */
{
/* first test for null input */
if (*input_ind == -1)
{
/* input is null, likewise output */
*output_ind = -1;
}
else
{
/* input is not null. set output to 2*input-4 */
*output = 2 * (*input) - 4;
/* and set out null indicator to zero */
*output_ind = 0;
}
/* signal successful completion by leaving sqlstate as is */
/* and exit */
return;
}
/* end of UDF: NUDFT1 */
Example 4: The following registers a Java UDF which returns the position of the first vowel in a string. The UDF is written in Java, is to be run fenced, and is the findvwl method of class javaUDFs.
CREATE FUNCTION findv ( CLOB(100K) )
RETURNS INTEGER
FENCED
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'javaUDFs.findvwl'
NO EXTERNAL ACTION
CALLED ON NULL INPUT
DETERMINISTIC
NO SQL
Example 5: This example outlines a user-defined predicate WITHIN that takes two parameters, g1 and g2, of type SHAPE as input:
CREATE FUNCTION within (g1 SHAPE, g2 SHAPE)
RETURNS INTEGER
LANGUAGE C
PARAMETER STYLE DB2SQL
NOT VARIANT
NOT FENCED
NO SQL
NO EXTERNAL ACTION
EXTERNAL NAME 'db2sefn!SDESpatilRelations'
PREDICATES
WHEN = 1
FILTER USING mbrOverlap(g1..xmin, g1..ymin, g1..xmax, g1..max,
g2..xmin, g2..ymin, g2..xmax, g2..ymax)
SEARCH BY INDEX EXTENSION gridIndex
WHEN KEY(g1) USE withinExplRule(g2)
WHEN KEY(g2) USE withinExplRule(g1)
The description of the WITHIN function is similar to that of any user-defined function, but the following additions indicate that this function can be used in a user-defined predicate.
within(g1, g2) = 1
in the WHERE clause of a DML statement, the predicate is to be treated as a user-defined predicate and the index defined by the index extension gridIndex should be used to retrieve rows that satisfy this predicate. If a constant is specified, the constant specified during the DML statement has to match exactly the constant specified in the create index statement. This condition is provided mainly to cover Boolean expression where the result type is either a 1 or a 0. For other cases, the EXPRESSION clause is a better choice.
Example 6: This example outlines a user-defined predicate DISTANCE that takes two parameters, P1 and P2, of type POINT as input:
CREATE FUNCTION distance (P1 POINT, P2 POINT)
RETURNS INTEGER
LANGUAGE C
PARAMETER STYLE DB2SQL
NOT VARIANT
NOT FENCED
NO SQL
NO EXTERNAL ACTION
EXTERNAL NAME 'db2sefn!SDEDistances'
PREDICATES
WHEN > EXPRESSION AS distExpr
SEARCH BY INDEX EXTENSION gridIndex
WHEN KEY(P1) USE distanceGrRule(P2, distExpr)
WHEN KEY(P2) USE distanceGrRule(P1, distExpr)
The description of the DISTANCE function is similar to that of any user-defined function, but the following additions indicate that when this function is used in a predicate, that predicate is a user-defined predicate.
SELECT T1.C1
FROM T1, T2
WHERE distance (T1.P1, T2.P1) > T2.C2
The predicate specification distance takes two parameters as input and compares the results with T2.C2, which is of type INTEGER. Since only the data type of the right hand side expression matters, (as opposed to using a specific constant), it is better to choose the EXPRESSION clause in the CREATE FUNCTION DDL for specifying a wildcard as the comparison value.
Alternatively, the following is also a valid user-defined predicate:
SELECT T1.C1
FROM T1, T2
WHERE distance(T1.P1, T2.P1) > distance (T1.P2, T2.P2)
There is currently a restriction that only the right hand side is treated as the expression; the term on the left hand side is the user-defined function for the user-defined predicate.