The CREATE FUNCTION (External Scalar) statement is used to register a user-defined external scalar function at the current server. A scalar function returns a single value each time it is invoked, and is in general valid wherever an SQL expression is valid.
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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Group privileges are not considered for any table or view specified in the CREATE FUNCTION statement.
To create a fenced function, no additional authorities or privileges are required.
To replace an existing function, the authorization ID of the statement must be the owner of the existing function (SQLSTATE 42501).
If the SECURED option is specified, the authorization ID of the statement must include SECADM or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).
>>-CREATE--+------------+--FUNCTION--function-name--------------> '-OR REPLACE-' >--(--+-------------------------------+--)--●-------------------> | .-,-------------------------. | | V | | '---| parameter-declaration |-+-' >--RETURNS--+-| data-type2 |--+------------+----------------------------+--> | '-AS LOCATOR-' | '-| data-type3 |--CAST FROM--| data-type4 |--+------------+-' '-AS LOCATOR-' >--| option-list |--------------------------------------------->< parameter-declaration |--+-----------------------------+------------------------------> | .-IN------. | '-+---------+--parameter-name-' | (1) | +-OUT-----+ '-INOUT---' >--| data-type1 |--+--------------------+--+------------+-------| '-| default-clause |-' '-AS LOCATOR-' data-type1, data-type2, data-type3, data-type4 |--+-| built-in-type |----+-------------------------------------| +-distinct-type-name---+ +-structured-type-name-+ '-REF--(--type-name--)-' built-in-type |--+-+-SMALLINT----+----------------------------------------------------------------------+--| | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(1)------------------------. | +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+ | | | '-CHAR------' '-(integer-+-------------+-)-' | | (2) | | | | | | +-OCTETS------+ | '-FOR BIT DATA-----' | | | | | '-CODEUNITS32-' | | | | | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | | | | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | | | | '-CHAR------' '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-+-CLOB------------------------+--+----------------------------------+------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' | | '-CHAR------' +-K-+ +-OCTETS------+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)------------------------. | +-+-GRAPHIC--+----------------------------+------+-------------------------------------+ | | '-(integer-+-------------+-)-' | | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | +-VARGRAPHIC--(integer-+-------------+-)-------+ | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-DBCLOB--+----------------------------------+-' | | '-(integer-+---+-+-------------+-)-' | | +-K-+ +-CODEUNITS16-+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)-------. | +-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+ | | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | | | | | '-CHARACTER-' | | | | | '-+-NVARCHAR-------------------------+--(integer)-' | | | | +-NCHAR VARYING--------------------+ | | | | '-NATIONAL--+-CHAR------+--VARYING-' | | | | '-CHARACTER-' | | | | .-(1M)-------------. | | | '-+-NCLOB---------------------------+--+------------------+-' | | +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' | | '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ | | +-M-+ | | '-G-' | | .-(1M)-------------. | +-+-BLOB----------------+--+------------------+----------------------------------------+ | '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------------+-----------------------------------------------------+ | +-TIME-------------------------+ | | | .-(--6--)-------. | | | '-TIMESTAMP--+---------------+-' | | '-(--integer--)-' | +-XML----------------------------------------------------------------------------------+ | .-SYSPROC.-. (3) (4) | '-+----------+--DB2SECURITYLABEL-------------------------------------------------------' default-clause |--DEFAULT--+-NULL-------------+--------------------------------| +-constant---------+ +-special-register-+ +-global-variable--+ '-(--expression--)-' option-list (5) |--●--LANGUAGE--+-C----+------●--+-------------------------+----> +-JAVA-+ '-SPECIFIC--specific-name-' +-CLR--+ '-OLE--' >--●--EXTERNAL--+----------------------+--●---------------------> '-NAME--+-'string'---+-' '-identifier-' >--PARAMETER STYLE--+-DB2GENERAL-+--●---------------------------> +-JAVA-------+ '-SQL--------' .-NOT DETERMINISTIC-. >--+------------------------------+--●--+-------------------+---> '-PARAMETER CCSID--+-ASCII---+-' '-DETERMINISTIC-----' '-UNICODE-' .-FENCED------------------------. >--●--+-------------------------------+--●----------------------> +-FENCED--●--+-THREADSAFE-----+-+ | '-NOT THREADSAFE-' | | .-THREADSAFE-. | '-NOT FENCED--●--+------------+-' .-READS SQL DATA-. >--+----------------------------+--●--+----------------+--●-----> +-RETURNS NULL ON NULL INPUT-+ +-NO SQL---------+ '-CALLED ON NULL INPUT-------' '-CONTAINS SQL---' .-STATIC DISPATCH-. .-EXTERNAL ACTION----. >--+-----------------+--●--+--------------------+--●------------> '-NO EXTERNAL ACTION-' .-NO SCRATCHPAD----------. .-NO FINAL CALL-. >--+------------------------+--●--+---------------+--●----------> | .-100----. | '-FINAL CALL----' '-SCRATCHPAD--+--------+-' '-length-' .-NO DBINFO-. >--+-------------------+--●--+-----------+--●-------------------> +-ALLOW PARALLEL----+ '-DBINFO----' '-DISALLOW PARALLEL-' >--+-----------------------------+--●---------------------------> '-TRANSFORM GROUP--group-name-' >--+-----------------------------------------------+--●---------> '-PREDICATES--(--| predicate-specification |--)-' .-INHERIT SPECIAL REGISTERS-. .-NOT SECURED-. >--+---------------------------+--●--+-------------+------------| '-SECURED-----' 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-+--)-----------|
If the function is referenced in the definition of a row permission or a column mask, the function cannot be replaced (SQLSTATE 42893).
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 cannot 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. 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 aggregate 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.
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). A weakly typed distinct type specified for a parameter is considered to be the same data type as the source type of the distinct type. For a Unicode database, CHAR(13) and GRAPHIC(8) are considered to be the same type. 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 returns an error (SQLSTATE 42723).
For a user-defined structured type, the appropriate transform functions must exist in the associated transform group.
For a reference type, the parameter can be specified as REF(type-name) if the parameter is unscoped.
The expression can be any expression of the type described in "Expressions". If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64K bytes.
The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).
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.
If the function is FENCED and has the NO SQL option, 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 previously in 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.
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, array types, and structured types are not valid as the type specified in data-type4 (SQLSTATE 42815).
The cast operation is also subject to runtime 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 bytes. The format used for the string is dependent on the LANGUAGE specified.
The string specified is the library name and function within the 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 executed. 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 is returned (SQLSTATE 42724).
>>-'--+-library_id-------+--+------------+--'------------------>< '-absolute_path_id-' '-!--func_id-'
Extraneous blanks are not permitted within the single quotation marks.
Operating system | Library name location |
---|---|
Linux AIX® Solaris HP-UX |
If myfunc was given as the library_id, and the database manager is being run from /u/production, the database manager will look for the function in library /u/production/sqllib/function/myfunc |
Windows | The database manager will look for the function in a directory path that is specified by the LIBPATH or PATH environment variable |
Operating system | Full path name example |
---|---|
Linux AIX Solaris HP-UX |
A value of '/u/jchui/mylib/myfunc' would cause the database manager to look in /u/jchui/mylib for the myfunc shared library. |
Windows | A value of 'd:\mylib\myfunc.dll' would cause the database manager to load the dynamic link library, myfunc.dll, from the d:\mylib directory. If an absolute path ID is being used to identify the routine body, be sure to append the .dll extension. |
Operating system | Entry point name of the function |
---|---|
Linux AIX Solaris HP-UX |
A value of '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. |
Windows | A value of 'mymod!func8' would direct the database manager to load the mymod.dll file and to call the func8() function in the dynamic link library (DLL). |
If the string is not properly formed, an error is returned (SQLSTATE 42878).
The body of every external function should be in a directory that is available on every database partition.
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 executed. 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 is returned (SQLSTATE 42724).
>>-'--+----------+--class_id--+-.-+--method_id--'-------------->< '-jar_id :-' '-!-'
Extraneous blanks are not permitted within the single quotation marks.
Operating system | Directory the Java virtual machine will look in for the classes |
---|---|
Linux AIX Solaris HP-UX |
'.../myPacks/UserFuncs/' |
Windows | '...\myPacks\UserFuncs\' |
The string specified represents the .NET assembly (library or executable), the class within that assembly, and the method within the class that the database manager invokes to execute the function being created. The module, class, and method do not need to exist when the CREATE FUNCTION statement is executed. However, when the function is used in an SQL statement, the module, class, and method must exist and be accessible from the database server machine; otherwise, an error is returned (SQLSTATE 42724).
C++ routines that are compiled with the '/clr' compiler option to indicate that they include managed code extensions must be cataloged as 'LANGUAGE CLR' and not 'LANGUAGE C'. The database server needs to know that the .NET infrastructure is being utilized in a user-defined function in order to make necessary runtime decisions. All user-defined functions using the .NET infrastructure must be cataloged as 'LANGUAGE CLR'.
>>-'--assembly--:--class_id--!--method_id--'-------------------><
The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.
For example, c:\sqllib\function.
If the file resides in a subdirectory of the installation function directory, the subdirectory can be given before the file name rather than specifying the full path.
For example, if your install directory is c:\sqllib and your assembly file is c:\sqllib\function\myprocs\mydotnet.dll, it is only necessary to specify 'myprocs\mydotnet.dll' for the assembly.
The case sensitivity of this parameter is the same as the case sensitivity of the file system.
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 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 is returned (SQLSTATE 42724).
>>-'--+-progid-+--!--method_id--'------------------------------>< '-clsid--'
Extraneous blanks are not permitted within the single quotation marks.
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 supported for user-defined functions for this database product only in Windows operating systems. THREADSAFE may not be specified for UDFs defined with LANGUAGE OLE (SQLSTATE 42613).
The value DB2GENRL may be used as a synonym for DB2GENERAL.
If the database is not a Unicode database, and a function with PARAMETER CCSID UNICODE is created, the function cannot have any graphic types, the XML type, or user-defined types (SQLSTATE 560C1).
If the database is not a Unicode database, and the alternate collating sequence has been specified in the database configuration, functions can be created with either PARAMETER CCSID ASCII or PARAMETER CCSID UNICODE. All string data passed into and out of the function will be converted to the appropriate code page.
This clause cannot be specified with LANGUAGE OLE, LANGUAGE JAVA, or LANGUAGE CLR (SQLSTATE 42613).
If a function is registered as FENCED, the database manager protects its internal resources (for example, 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.
Only FENCED can be specified for a function with LANGUAGE OLE or NOT THREADSAFE (SQLSTATE 42613).
If the function is FENCED and has the NO SQL option, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
Either SYSADM authority, DBADM authority, or a special authority (CREATE_NOT_FENCED_ROUTINE) is required to register a user-defined function as NOT FENCED.
LANGUAGE CLR user-defined functions cannot be created when specifying the NOT FENCED clause (SQLSTATE 42601).
For FENCED functions, THREADSAFE is the default if the LANGUAGE is JAVA or CLR. For all other languages, NOT THREADSAFE is the default. If the function is defined with LANGUAGE OLE, THREADSAFE may not be specified (SQLSTATE 42613).
For NOT FENCED functions, THREADSAFE is the default. NOT THREADSAFE cannot be specified (SQLSTATE 42613).
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.
For the classification of each statement, see SQL statements that can be executed in routines and triggers.
The default is READS SQL DATA.
A function with external actions might return incorrect results if the function is executed by parallel tasks. For example, if the function sends a note for each initial call to it, one note is sent for each parallel task instead of once for the function. Specify the DISALLOW PARALLEL clause for functions that do not work correctly with parallelism.
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 one shown previously. If the function is executed in multiple database partitions, a scratchpad would be assigned in each database partition where the function is processed, for each reference to the function in the SQL statement. Similarly, if the query is executed with intrapartition 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.
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 database partition.
No changes to the special registers are passed back to the invoker of the function.
Non-updatable special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore set to their default values.
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.
The definer of the function must have EXECUTE privilege on the specified filter function.
The function-invocation clause must not exceed 65 536 bytes in length in the database code page (SQLSTATE 22001).
Subqueries and XMLQUERY or XMLEXISTS expressions 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.
The case-invocation clause must not exceed 65 536 bytes in length in the database code page (SQLSTATE 22001).
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.
If a function is defined as READS SQL DATA, no statement in the function can access a table that is being modified by the statement which invoked the function (SQLSTATE 57053). For example, suppose the user-defined function BONUS() is defined as READS SQL DATA. If the statement UPDATE EMPLOYEE SET SALARY = SALARY + BONUS(EMPNO) is invoked, no SQL statement in the BONUS function can read from the EMPLOYEE table.
When the function is used in an SQL statement, the function definer must have the EXECUTE privilege on any packages used by the function.
The SECURED attribute is considered to be an assertion that declares the user has established a change control audit procedure for all changes to the user-defined function. The database manager assumes that such a control audit procedure is in place for all subsequent ALTER FUNCTION statements or changes to external packages.
CREATE FUNCTION CENTRE (INT,FLOAT)
RETURNS FLOAT
EXTERNAL NAME 'mod!middle'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
CREATE FUNCTION PELLOW.CENTRE (FLOAT, FLOAT, FLOAT)
RETURNS DECIMAL(8,4) CAST FROM FLOAT
SPECIFIC FOCUS92
EXTERNAL NAME 'effects!focalpt'
LANGUAGE C PARAMETER STYLE SQL
DETERMINISTIC FENCED NOT NULL CALL NO SQL NO EXTERNAL ACTION
SCRATCHPAD NO FINAL CALL
DISALLOW PARALLEL
CREATE FUNCTION ntest1 (SMALLINT)
RETURNS SMALLINT
EXTERNAL NAME 'ntest1!nudft1'
LANGUAGE C PARAMETER STYLE SQL
DETERMINISTIC NOT FENCED NULL CALL
NO SQL NO EXTERNAL ACTION
#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 */
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
CREATE FUNCTION within (g1 SHAPE, g2 SHAPE)
RETURNS INTEGER
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
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)
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. CREATE FUNCTION distance (P1 POINT, P2 POINT)
RETURNS INTEGER
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
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 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.
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 side is treated as
the expression; the term on the left side is the user-defined function
for the user-defined predicate.