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--+-------------+--+--'
'-AS LOCATOR--'
>----RETURNS--+-data-type2--+-------------+------------------------+>
| '-AS LOCATOR--' |
'-data-type3--CAST FROM--data-type4--+-------------+-'
'-AS LOCATOR--'
>----*----+--------------------------+--*----------------------->
'-SPECIFIC--specific-name--'
>-----EXTERNAL--+----------------------+---*-------------------->
'-NAME--+-'string'---+-'
'-identifier-'
>----LANGUAGE--+-C----+--*---PARAMETER STYLE--+-DB2SQL-----+---->
+-JAVA-+ +-DB2GENERAL-+
'-OLE--' '-JAVA-------'
.-NOT DETERMINISTIC--. .-FENCED-----.
>----*----+--------------------+--*----+------------+----------->
| (1) | '-NOT FENCED-'
'-DETERMINISTIC------'
.-RETURNS NULL ON NULL INPUT--. (2)
>-----+-----------------------------+---------*---NO SQL--*----->
'-CALLED ON NULL INPUT--------'
.-EXTERNAL ACTION----. .-NO SCRATCHPAD--.
>-----+--------------------+--*----+----------------+--*-------->
'-NO EXTERNAL ACTION-' '-SCRATCHPAD-----'
.-NO FINAL CALL--. .-ALLOW PARALLEL----.
>-----+----------------+--*----+-------------------+--*--------->
'-FINAL CALL-----' '-DISALLOW PARALLEL-'
.-NO DBINFO--.
>-----+------------+--*----------------------------------------><
'-DBINFO-----'
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". 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 paramaterized 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).
A distinct type is 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, SQLyymmddhhmmsshhn.
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, Windows 95 and Windows NT, 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, Windows 95 and Windows NT, '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, Windows 95 and Windows NT, '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 which is mounted and 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 95 and Windows NT.
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 checked out 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. |
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, 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 compatability. 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.
(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 mounted and 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.
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