CREATE FUNCTION (external scalar) statement
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.
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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Authorization
- IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the function does not exist.
- CREATEIN privilege on the schema, if the schema name of the function refers to an existing schema.
- SCHEMAADM authority on the schema, if the schema name of the function refers to an existing schema.
- DBADM authority.
- CREATE_EXTERNAL_ROUTINE authority on the database.
- SYSADM authority.
- DBADM authority if the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the value EXTERNAL_ROUTINE_DBADM.
- CREATE_NOT_FENCED_ROUTINE authority on the database.
- SYSADM authority.
- DBADM authority if the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the
value NOT_FENCED_ROUTINE_DBADM Note: The Db2 11.5.8 security special build 29133 includes changes to the implicit authorities of both the SYSADM and the DBADM authorities. By default, the SYSADM authority, and not the DBADM authority, implicitly has the authorities CREATE_EXTERNAL_ROUTINE and CREATE_NOT_FENCED_ROUTINE. If the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains either of the EXTERNAL_ROUTINE_DBADM or NOT_FENCED_ROUTINE_DBADM values, then the DBADM authority also implicitly has these privileges.
Syntax
- 1 OUT and INOUT are valid only if the function has LANGUAGE C.
- 2 The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
- 3 DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
- 4 For a column of type DB2SECURITYLABEL, NOT NULL WITH DEFAULT is implicit and cannot be explicitly specified (SQLSTATE 42842). The default value for a column of type DB2SECURITYLABEL is the session authorization ID's security label for write access.
- 5 LANGUAGE SQL is also supported.
Description
- OR REPLACE
- Specifies to replace the definition for the
function if one exists at the current server. The existing definition is effectively dropped before
the new definition is replaced in the catalog, with the exception that privileges that were granted
on the function are not affected. This option can be specified only by the owner of the object. This
option is ignored if a definition for the function does not exist at the current server. To replace
an existing function, the specific name and function name of the new definition must be the same as
the specific name and function name of the old definition, or the signature of the new definition
must match the signature of the old definition. Otherwise, a new function is created.
If the function is referenced in the definition of a row permission or a column mask, the function cannot be replaced (SQLSTATE 42893).
function-name
- Names
the function being defined. It is a qualified or unqualified name that designates a function. The
unqualified form of function-name is an SQL identifier. In dynamic SQL
statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object
name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the
qualifier for unqualified object names. The qualified form is a
schema-name followed by a period and an SQL identifier. The qualified name
must not be the same as the data type of the first parameter, if that first parameter is a
structured type.
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.
(parameter-declaration,...)
-
Identifies the number of
input parameters of the function, and specifies the mode, name, data type, and optional default
value of each parameter. One entry in the list must be specified for each parameter that the
function expects to receive. Up to 90 parameters can be specified (SQLSTATE 54023).You can register a function that has no parameters; 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). 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).
-
IN | OUT | INOUT
- Specifies the mode of the parameter. If an error is returned by the function, OUT parameters are
undefined and INOUT parameters are unchanged. The default is IN.
-
IN
- Identifies the parameter as an input parameter to the function. Any changes made to the parameter within the function are not available to the invoking context when control is returned. OUT
- Identifies the parameter as an output parameter for the function. INOUT
- Identifies the parameter as both an input and output parameter for the function.
parameter-name
- Specifies an optional name for the parameter. Parameter names are required to reference the parameters of a function in the index-exploitation clause of a predicate specification. The name cannot be the same as any other parameter-name in the parameter list (SQLSTATE 42734). data-type1
- Specifies the data type of the parameter. The data type can be a built-in data
type, a distinct type, a structured type, or a reference type. For a more complete description of
each built-in data type, see
CREATE TABLE
. Some data types are not supported in all languages. For details on the mapping between SQL data types and host language data types, seeData types that map to SQL data types in embedded SQL applications
.- A datetime type parameter is passed as a character data type, and the data is passed in the ISO format.
- DECIMAL (and NUMERIC) are invalid with LANGUAGE C and OLE (SQLSTATE 42815).
- DECFLOAT is invalid with LANGUAGE C, COBOL, CLR, JAVA, and OLE (SQLSTATE 42815).
- XML is invalid with LANGUAGE OLE.
- Because the XML value that is seen inside a function is a serialized version of the XML value
that is passed as a parameter in the function call, parameters of type XML must be declared using
the syntax
XML AS CLOB(n)
. - CLR does not support DECIMAL scale greater than 28 (SQLSTATE 42613).
- Array types cannot be specified (SQLSTATE 42815).
- BINARY and VARBINARY data types are invalid with LANGUAGE CLR and OLE (SQLSTATE 42815).
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.
- DEFAULT
- Specifies a default value for the parameter. The default can be a constant, a special register,
a global variable, an expression, or the keyword NULL. The special registers that can be specified
as the default are that same as those that can be specified for a column default (see
default-clause in the CREATE TABLE statement). Other special registers can
be specified as the default by using an expression.
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).
A default cannot be specified in the following situations:- For INOUT or OUT parameters (SQLSTATE 42601)
- For a parameter of type ARRAY, ROW, or CURSOR (SQLSTATE 429BB)
- For a parameter to a function definition that also specified a PREDICATES clause (SQLSTATE 42613)
- 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 based
on a LOB data type (SQLSTATE 42601). 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.
If the function is FENCED and has the NO SQL option, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
- Specifies the mode of the parameter. If an error is returned by the function, OUT parameters are
undefined and INOUT parameters are unchanged. The default is IN.
- RETURNS
- This mandatory clause identifies the output of the function.
-
data-type2
- Specifies the data type of the output.
In this case, exactly the same considerations apply as for the parameters of external functions described previously in data-type1 for function parameters.
- AS LOCATOR
- For LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be added. This indicates that a LOB locator is to be passed from the UDF instead of the actual value.
- data-type3 CAST FROM data-type4
- Specifies the data type of the output.
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.
- AS LOCATOR
- For data-type4 specifications that are LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be added. This indicates that a LOB locator is to be passed back from the UDF instead of the actual value.
- Specifies the data type of the output.
- built-in-type
- See
CREATE TABLE
for the description of built-in data types. - SPECIFIC specific-name
- Provides
a unique name for the instance of the function that is being defined. This specific name can be used
when sourcing on this function, dropping the function, or commenting on the function. It can never
be used to invoke the function. The unqualified form of specific-name is
an SQL identifier. The qualified form is a schema-name followed by a
period and an SQL identifier. The name, including the implicit or explicit qualifier, must not
identify another function instance or method specification that exists at the application server;
otherwise an error (SQLSTATE 42710) is 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.
- EXTERNAL
- This clause indicates that the CREATE FUNCTION statement is being used to register a new
function based on code written in an external programming language and adhering to the documented
linkage conventions and interface.
If NAME clause is not specified
NAME function-name
is assumed.- NAME 'string'
- This clause identifies the name of the user-written code which implements the function being
defined.
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.
- For LANGUAGE C:
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).
Extraneous blanks are not permitted within the single quotation marks.
-
library_id
- Identifies the library name containing the function. The database manager will look for the
library as follows:
Operating system Library name location
Linux®
AIX®
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/myfuncWindows The database manager will look for the function in a directory path that is specified by the LIBPATH or PATH environment variable
absolute_path_id
- Identifies
the full path name of the file containing the function. The format depends on the operating system,
as illustrated in the following table:
Operating system Full path name example
Linux
AIX
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.
! func_id
- Identifies
the entry point name of the function to be invoked. The ! serves as a delimiter between the library
ID and the function ID. The format depends on the operating system, as illustrated in the following table:
Operating system Entry point name of the function
Linux
AIX
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.
- Identifies the library name containing the function. The database manager will look for the
library as follows:
- For LANGUAGE JAVA:
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).
The string can be specified as follows:
Extraneous blanks are not permitted within the single quotation marks.
-
jar_id
- Identifies the jar identifier given to the jar collection when it was installed in the database. It can be either a simple identifier, or a schema qualified identifier. Examples are 'myJar' and 'mySchema.myJar'. class_id
- Identifies
the class identifier of the Java™ object. If the class is part
of a package, the class identifier part must include the complete package prefix, for example,
'myPacks.UserFuncs'. The directory the Java virtual machine
will look in for the classes depends on the operating system, as illustrated in the following table:
Operating system Directory the Java virtual machine will look in for the classes
Linux
AIX
'.../myPacks/UserFuncs/' Windows '...\myPacks\UserFuncs\'
method_id
- Identifies the method name of the Java object to be invoked.
- For LANGUAGE CLR:
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'.
The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.
-
assembly
- Identifies the DLL
or other assembly file in which the class resides. Any file extensions (such as .dll) must be
specified. If the full path name is not given, the file must reside in the function directory of the
database product's installation path
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.
class_id
- Specifies the name of the class within the given assembly in which the method that is to be invoked resides. If the class resides within a namespace, the full namespace must be given in addition to the class. For example, if the class EmployeeClass is in namespace MyCompany.ProcedureClasses, then MyCompany.ProcedureClasses.EmployeeClass must be specified for the class. Note that the compilers for some .NET languages will add the project name as a namespace for the class, and the behavior may differ depending on whether the command line compiler or the GUI compiler is used. This parameter is case sensitive. method_id
- Specifies the method within the given class that is to be invoked. This parameter is case sensitive.
- Identifies the DLL
or other assembly file in which the class resides. Any file extensions (such as .dll) must be
specified. If the full path name is not given, the file must reside in the function directory of the
database product's installation path
- For LANGUAGE OLE:
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).
Extraneous blanks are not permitted within the single quotation marks.
-
progid
- Identifies the programmatic identifier of the OLE object.
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).
clsid
- Identifies the class identifier of the OLE object to create. It can be used as an alternative
for specifying a progid in the case that an OLE object is not registered
with a progid. The clsid has the form:
where 'n' is an alphanumeric character. clsid is not interpreted by the database manager but only forwarded to the OLE APIs at run time.{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}
method_id
- Identifies the method name of the OLE object to be invoked.
- Identifies the programmatic identifier of the OLE object.
- For LANGUAGE CPP:
The string specified is the library identifier and class identifier within the library, which contains the evaluate method the database manager invokes to execute the user-defined function that is being created. If the string is not properly formed, an error is returned (SQLSTATE 42878).
It is not necessary that the library (or the class within the library) exist when the CREATE FUNCTION statement is executed. However, when the function is used in an SQL statement, the library and class within the library must exist and be accessible from the database server machine; otherwise, an error is returned (SQLSTATE 42724).
The body of every external function should be in a directory that is available on every database partition.
The string can be specified as follows: Extraneous blanks are not permitted within the single quotation marks.-
library_id
- The name of the library that contains the function:
- On a UNIX system, if the specified library ID is
myfunc
, and if the database manager is being run from/u/production
, the database manager looks for the function in the following library:/u/production/sqllib/function/myfunc
- On a Windows operating system, the database manager looks for the function in the directory path specified by the LIBPATH or PATH environment variable.
absolute_path_id
- On a UNIX system, if the specified library ID is
- The full path of the file that contains the function. For example:
- On a UNIX system, the following specification causes the database manager to look in
/u/jchui/mylib
for the myfunc shared library:'/u/jchui/mylib/myfunc'
- On a Windows operating system, the following specification causes 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.'d:\mylib\myfunc.dll'
class_id
- On a UNIX system, the following specification causes the database manager to look in
- The name of the class that contains the methods that are to be invoked.
For example, if you specify'mymod!myclass'
:- On a UNIX system, the database manager looks for the library
$inst_home_dir/sqllib/function/mymod
and invokes the evaluate method of the myclass class in that library. - On a Windows operating system, the database manager loads the mymod.dll file and calls the evaluate method of the myclass class in the dynamic link library (DLL).
- The name of the library that contains the function:
- For LANGUAGE C:
- NAME identifier
- This identifier specified is an SQL identifier. The SQL identifier is used as the library-id in the string. Unless it is a delimited identifier, the identifier is folded to upper case. If the identifier is qualified with a schema name, the schema name portion is ignored. This form of NAME can only be used with LANGUAGE C.
- LANGUAGE
- This mandatory clause specifies the language interface convention to which the body of the
user-defined function is written.
- C
- The database manager calls the user-defined function as if it were a C function. The user-defined function must conform to the C language calling and linkage convention as defined by the standard ANSI C prototype.
- JAVA
- The database manager calls the user-defined function as a method in a Java class.
- CLR
- The database manager calls the user-defined function as a method in a .NET class. LANGUAGE CLR is supported only for user-defined functions running on Windows operating systems. NOT FENCED cannot be specified for a CLR routine (SQLSTATE 42601).
- OLE
- The database manager calls the user-defined function as if it were a method
exposed by an OLE automation object. The user-defined function must conform with the OLE automation
data types and invocation mechanism, as described in the OLE Automation Programmer's
Reference.
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).
- CPP
- The database manager calls the user-defined function by invoking the evaluate method of a C++ class.
- PYTHON
- The database manager calls the user-defined function as a method in a Python class.
- PARAMETER STYLE
- This clause is used to specify the conventions used for passing parameters to and returning the
value from functions.
- DB2GENERAL
- Used to specify the conventions for passing parameters to and returning the value from external
functions that are defined as a method in a Java class. This
can only specified when LANGUAGE JAVA is used.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
- JAVA
- This means that the function will use a parameter passing convention that conforms to the Java language and SQLJ Routines specification. This can only be specified when LANGUAGE JAVA is used, no structured data types are specified as parameters, and no CLOB, BLOB, or DBCLOB data types are specified as return types (SQLSTATE 429B8). PARAMETER STYLE JAVA functions do not support the FINAL CALL, SCRATCHPAD, or DBINFO clause.
- SQL
- Used to specify the conventions for passing parameters to and returning the value from external functions that conform to C language calling and linkage conventions, methods exposed by OLE automation objects, or public static methods of a .NET object. This must be specified when LANGUAGE C, LANGUAGE CLR, or LANGUAGE OLE is used.
- NPSGENERIC
-
Used to specify the conventions for passing parameters to and returning the value from external functions that are defined as a method in a C++ class. This can specified only when the LANGUAGE option is set to CPP or PYTHON.
When NPSGENERIC is specified as the parameter style, the UDF is written in C++ as a derived class of the nz.udx_ver2.Udf class. The class must implement the following two methods in addition to its constructor and destructor:- static Udf* Udf::instantiate(UdxInit *pInit)
- Static member method instantiate(), which must instantiate a new instance of the UDF derived class and return a pointer to the new instance as a class Udf pointer. The engine uses this method to create an instance of a UDF object.
- virtual ReturnValue Udf::evaluate()
- Member method evaluate(), which is called by the engine to evaluate the user function and return a value to the caller.
- PARAMETER CCSID
- Specifies the encoding scheme to use for all string data passed into and out of the function. If
the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode
databases, and PARAMETER CCSID ASCII for all other databases.
- ASCII
- Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031). When the function is invoked, the application code page for the function is the database code page.
- UNICODE
- Specifies that string data is encoded in Unicode. If the database is a Unicode database,
character data is in UTF-8, and graphic data is in UCS-2. If the database is not a Unicode database,
character data is in UTF-8. In either case, when the function is invoked, the application code page
for the function is 1208.
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 CPP, LANGUAGE OLE, LANGUAGE JAVA, or LANGUAGE CLR (SQLSTATE 42613).
- DETERMINISTIC or NOT DETERMINISTIC
- This optional clause specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC function must always return the same result from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC. An example of a NOT DETERMINISTIC function would be a random-number generator. An example of a DETERMINISTIC function would be a function that determines the square root of the input.
- FENCED or NOT FENCED
- This clause specifies whether or not the function is considered
safe
to run in the database manager operating environment's process or address space.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.
CAUTION:Use of NOT FENCED for functions not adequately coded, reviewed, and tested can compromise the integrity of your database. This database product safeguards 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.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).
LANGUAGE CLR user-defined functions cannot be created when specifying the NOT FENCED clause (SQLSTATE 42601).
- THREADSAFE or NOT THREADSAFE
- Specifies whether the function is considered safe to run in the same process as other routines
(THREADSAFE), or not (NOT THREADSAFE). If the function is defined with LANGUAGE other than OLE:
- If the function is defined as THREADSAFE, the database manager can invoke the function in the same process as other routines. In general, to be threadsafe, a function should not use any global or static data areas. Most programming references include a discussion of writing threadsafe routines. Both FENCED and NOT FENCED functions can be THREADSAFE.
- If the function is defined as NOT THREADSAFE, the database manager will never simultaneously invoke the function in the same process as another routine.
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).
- RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT
- This optional clause can be used to
avoid a call to the external function if any of the arguments is null. If the user-defined function
is defined to have no parameters, then this null argument condition cannot arise, and it does not
matter how this specification is coded. If this clause is not specified, the default is RETURNS NULL
ON NULL INPUT, except when PARAMETER STYLE JAVA is specified, in which case the default is CALLED ON
NULL INPUT.
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.
- READS SQL DATA, NO SQL, or CONTAINS SQL
- Specifies the classification of SQL statements that the function can run. The database manager
verifies that the SQL statements that the function issues are consistent with this specification.
For the classification of each statement, see SQL statements that can be executed in routines and triggers.
The default is READS SQL DATA.
- READS SQL DATA
- Specifies that the function can run statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL (SQLSTATE 38002 or 42985). The function cannot run SQL statements that modify data. (SQLSTATE 38003 or 42985).
- NO SQL
- Specifies that the function can run only SQL statements with a data access classification of NO SQL (SQLSTATE 38001).
- CONTAINS SQL
- Specifies that the function can run only SQL statements with a data access classification of CONTAINS SQL or NO SQL (SQLSTATE 38004 or 42985). The function cannot run any SQL statements that read or modify data (SQLSTATE 38003 or 42985).
- STATIC DISPATCH
- This optional clause indicates that at function resolution time, a function is chosen by the database server based on the static types (declared types) of the parameters of the function.
- EXTERNAL ACTION or NO EXTERNAL ACTION
- Specifies whether the function takes an action that changes the state of an object that the
database manager does not manage. An example of an external action is sending a message or writing a
record to a file. The default is EXTERNAL ACTION.
- EXTERNAL ACTION
- Specifies that the function takes an action that changes the state of an object that the
database manager does not manage.
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.
- NO EXTERNAL ACTION
- Specifies that the function does not take any action that changes the state of an object that the database manager does not manage. The database manager uses this information during optimization of SQL statements.
- NO SCRATCHPAD or SCRATCHPAD length
- This optional clause may be used to specify whether a scratchpad is to be provided for an
external function. (It is strongly recommended that user-defined functions be re-entrant, so a
scratchpad provides a means for the function to
save state
from one call to the next.)- 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. On each invocation of the
user-defined function, an additional argument is passed to the external function which addresses the
scratchpad. This scratchpad has the following characteristics:
- length, if specified, sets the size of the scratchpad in bytes; this value must be between 1 and 32 767 (SQLSTATE 42820). The default size is 100 bytes.
- It is initialized to all X'00''s.
- Its scope is the SQL statement. There is one scratchpad per reference to the external function
in the SQL statement. So if the UDFX function in the following statement is defined with the
SCRATCHPAD keyword, three scratchpads would be assigned.
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.
- It is persistent. Its content is preserved from one external function call to the next. Any changes made to the scratchpad by the external function on one call will be there on the next call. The database manager initializes scratchpads at the beginning of execution of each SQL statement. The database manager may reset scratchpads at the beginning of execution of each subquery. The system issues a final call before resetting a scratchpad if the FINAL CALL option is specified.
- It can be used as a central point for system resources (for example, memory) which the external
function might acquire. The function could acquire the memory on the first call, keep its address in
the scratchpad, and refer to it in subsequent calls.
(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 NO SCRATCHPAD is specified then no scratchpad is allocated or passed to the external function.
SCRATCHPAD cannot be specified in combination with a PARAMETER STYLE JAVA function.
- 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. On each invocation of the
user-defined function, an additional argument is passed to the external function which addresses the
scratchpad. This scratchpad has the following characteristics:
- FINAL CALL or NO FINAL CALL
- This optional clause specifies whether a final call is to be made to an external function. The
purpose of such a final call is to enable the external function to free any system resources it has
acquired. It can be useful in conjunction with the SCRATCHPAD keyword in situations where the
external function acquires system resources such as memory and anchors them in the scratchpad.
- If FINAL CALL is specified, then at execution time an additional argument is passed to the
external function which specifies the type of call. The types of calls are:
- Normal call
- SQL arguments are passed and a result is expected to be returned.
- First call
- The first call to the external function for this reference to the user-defined function in this SQL statement. The first call is a normal call.
- Final call
- A final call to the external function to enable the function to free up resources. The final
call is not a normal call. This final call occurs at the following times:
- End-of-statement
- This case occurs when the cursor is closed for cursor-oriented statements, or when the statement is through executing otherwise.
- End-of-parallel-task
- This case occurs when the function is executed by parallel tasks.
- End-of-transaction or interrupt
- This case occurs when the normal end-of-statement does not occur. For example, the logic of an
application may for some reason bypass the close of the cursor. During this type of final call, no
SQL statements may be issued except for CLOSE cursor (SQLSTATE 38505). This type of final call is
indicated with a special value in the
call type
argument.
- If NO FINAL CALL is specified, no
call type
argument is passed to the external function, and no final call is made.
FINAL CALL cannot be specified in combination with the following parameter settings:- PARAMETER STYLE JAVA
- LANGUAGE CPP
- If FINAL CALL is specified, then at execution time an additional argument is passed to the
external function which specifies the type of call. The types of calls are:
- ALLOW PARALLEL or DISALLOW PARALLEL
- This optional clause specifies whether, for a single reference to the function, the invocation
of the function can be parallelized. In general, the invocations of most scalar functions should be
parallelizable, but there may be functions (such as those depending on a single copy of a
scratchpad) that cannot. If either ALLOW PARALLEL or DISALLOW
PARALLEL are specified for a scalar function, then this specification is accepted. The following
questions should be considered in determining which keyword is appropriate for the function.
- Are all the UDF invocations completely independent of each other? If YES, then specify ALLOW PARALLEL.
- Does each UDF invocation update the scratchpad, providing value(s) that are of interest to the next invocation? (For example, the incrementing of a counter.) If YES, then specify DISALLOW PARALLEL or accept the default.
- Is there some external action performed by the UDF which should happen only on one database partition? If YES, then specify DISALLOW PARALLEL or accept the default.
- Is the scratchpad used, but only so that some expensive initialization processing can be performed a minimal number of times? If YES, then specify ALLOW PARALLEL.
- Is the function going to be invoked in a query that accesses a column-organized table? If YES, then specifying ALLOW PARALLEL might improve performance.
In any case, the body of every external function should be in a directory that is available on every database partition.
The default value is ALLOW PARALLEL, except if one or more of the following options is specified in the statement.- NOT DETERMINISTIC
- EXTERNAL ACTION
- SCRATCHPAD
- FINAL CALL
- INHERIT SPECIAL REGISTERS
- This optional clause specifies that updatable special registers in the function will inherit
their initial values from the environment of the invoking statement. For a function invoked in the
select-statement of a cursor, the initial values are inherited from the environment when the cursor
is opened. For a routine invoked in a nested object (for example a trigger or view), the initial
values are inherited from the runtime environment (not inherited from the object definition).
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.
- NO DBINFO or DBINFO
- This optional
clause specifies whether certain specific information known by the database server will be passed to
the UDF as an additional invocation-time argument (DBINFO) or not (NO DBINFO). NO DBINFO is the
default. DBINFO is not supported for the following clauses (SQLSTATE 42613):
- LANGUAGE OLE
- PARAMETER STYLE JAVA
If DBINFO is specified, then a structure is passed to the UDF which contains the following information:- Data base name - the name of the currently connected database.
- Application ID - unique application ID which is established for each connection to the database.
- Application Authorization ID - the application runtime authorization ID, regardless of the nested UDFs in between this UDF and the application.
- Code page - identifies the database code page.
- Schema name - under the exact same conditions as for Table name, contains the name of the schema; otherwise blank.
- Table name - if and only if the UDF reference is either the right side of a SET clause in an UPDATE statement or an item in the VALUES list of an INSERT statement, contains the unqualified name of the table being updated or inserted; otherwise blank.
- Column name - under the exact same conditions as for Table name, contains the name of the column being updated or inserted; otherwise blank.
- Database version/release - identifies the version, release and modification level of the database server invoking the UDF.
- Platform - contains the server's platform type.
- Table function result column numbers - not applicable to external scalar functions.
- TRANSFORM GROUP group-name
- Indicates the transform group to be used for user-defined structured type transformations when
invoking the function. A transform is required if the function definition includes a user-defined
structured type as either a parameter or returns data type. If this clause is not specified, the
default group name DB2_FUNCTION is used. If the specified (or default)
group-name is not defined for a referenced structured type, an error is
raised (SQLSTATE 42741). If a required FROM SQL or TO SQL transform function is not defined for the
given group-name and structured type, an error is raised (SQLSTATE 42744).
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.
- PREDICATES
- Defines the filtering or index extension exploitation performed when this function is used in a
predicate. A predicate-specification allows the optional SELECTIVITY clause of a search-condition to
be specified. If the PREDICATES clause is specified, the function must be defined as DETERMINISTIC
with NO EXTERNAL ACTION (SQLSTATE 42613). If the PREDICATES clause is specified, and the
database is not a Unicode database, PARAMETER CCSID UNICODE must not be specified (SQLSTATE
42613).
- WHEN comparison-operator
- Introduces a specific use of the function in a predicate with a comparison operator (
=
,<
,>
,>=
,<=
,<>
).-
constant
- Specifies a constant value with a data type comparable to the RETURNS type of the function (SQLSTATE 42818). When a predicate uses this function with the same comparison operator and this constant, the specified filtering and index exploitation will be considered by the optimizer.
- EXPRESSION AS expression-name
- Provides a name for an expression. When a predicate uses this function with the same comparison operator and an expression, filtering and index exploitation may be used. The expression is assigned an expression name so that it can be used as a search function argument. The expression-name cannot be the same as any parameter-name of the function being created (SQLSTATE 42711). When an expression is specified, the type of the expression is identified.
- FILTER USING
- Allows specification of an external function or a case expression to be used for additional
filtering of the result table.
-
function-invocation
- Specifies a filter function that can be used to perform additional filtering of the result
table. This is a version of the defined function (used in the predicate) that reduces the number of
rows on which the user-defined predicate must be executed, to determine if rows qualify. If the
results produced by the index are close to the results expected for the user-defined predicate,
applying the filtering function may be redundant. If not specified, data filtering is not performed.
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:- Not be defined with LANGUAGE SQL (SQLSTATE 429B4)
- Not be defined with NOT DETERMINISTIC or EXTERNAL ACTION (SQLSTATE 42845)
- Not have a structured data type as the data type of any of the parameters (SQLSTATE 428E3)
- Not include a subquery (SQLSTATE 428E4)
- Not include an XMLQUERY or XMLEXISTS expression (SQLSTATE 428E4)
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).
case-expression
- Specifies a case expression for additional filtering of the result table. The
searched-when-clause and simple-when-clause can
use parameter-name, expression-name, or a
constant (SQLSTATE 42703). An external function with the rules specified in FILTER USING
function-invocation may be used as a result-expression. Any function or
method referenced in the case-expression must also conform to the four
rules listed under function-invocation.
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).
index-exploitation
- Specifies a filter function that can be used to perform additional filtering of the result
table. This is a version of the defined function (used in the predicate) that reduces the number of
rows on which the user-defined predicate must be executed, to determine if rows qualify. If the
results produced by the index are close to the results expected for the user-defined predicate,
applying the filtering function may be redundant. If not specified, data filtering is not performed.
- Defines a set of rules in terms of the search method of an index extension that can be used to
exploit the index.
- SEARCH BY INDEX EXTENSION index-extension-name
- Identifies the index extension. The index-extension-name must identify an existing index extension.
- EXACT
- Indicates that
the index lookup is exact in terms of the predicate evaluation. Use EXACT indicate that neither the
original user-defined predicate function or the filter need to be applied after the index lookup.
The EXACT predicate is useful when the index lookup returns the same results as the predicate.
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.
exploitation-rule
- Describes the search targets and search arguments and how they can be used to perform the index
search through a search method defined in the index extension.
- WHEN KEY (parameter-name1)
- This defines the search target. Only one search target can be specified for a key. The
parameter-name1 value identifies parameter names of the defined function
(SQLSTATE 42703 or 428E8).
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.
- USE search-method-name(parameter-name2)
- This defines the search argument. It identifies which search method to use from those defined in the index extension. The search-method-name must match a search method defined in the index extension (SQLSTATE 42743). The parameter-name2 values identify parameter names of the defined function or the expression-name in the EXPRESSION AS clause (SQLSTATE 42703). It must be different from any parameter name specified in the search target (SQLSTATE 428E9). The number of parameters and the data type of each parameter-name2 must match the parameters defined for the search method in the index extension (SQLSTATE 42816). The match must be exact for built-in and distinct data types and within the same structured type hierarchy for structured types.
- NOT SECURED or SECURED
- Specifies whether the function is considered secure for row and column access control. The
default is NOT SECURED.
- NOT SECURED
- Indicates that the function is not considered secure. When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled and column level access control is activated for its table (SQLSTATE 428HA). This rule applies to the non secure user-defined functions that are invoked anywhere in the statement.
- SECURED
- Indicates that the function is considered secure. The function must be secure when it is referenced in a row permission or a column mask (SQLSTATE 428H8).
- STAY RESIDENT NO
- Specifies that the library that is loaded for the function is not to remain resident in memory
after the function ends. This clause is ignored when:
- The NOT FENCED clause is specified.
- The LANGUAGE option is set to JAVA or CLR.
Notes
- Determining whether one data type is castable to another data type does not consider length or precision and scale for parameterized data types such as CHAR and DECIMAL. Therefore, errors may occur when using a function as a result of attempting to cast a value of the source data type to a value of the target data type. For example, VARCHAR is castable to DATE but if the source type is actually defined as VARCHAR(5), an error will occur when using the function.
- When choosing the data types for the parameters of a user-defined function, consider the rules
for promotion that will affect its input values (see
Promotion of data types
). For example, a constant which may be used as an input value could have a built-in data type different from the one expected and, more significantly, may not be promoted to the data type expected. Based on the rules for promotion, it is generally recommended to use the following data types for parameters:- INTEGER instead of SMALLINT
- DOUBLE instead of REAL
- VARCHAR instead of CHAR
- VARGRAPHIC instead of GRAPHIC
- For portability of UDFs across platforms the following data types should not be used:
- FLOAT- use DOUBLE or REAL instead.
- NUMERIC- use DECIMAL instead.
- LONG VARCHAR- use CLOB (or BLOB) instead.
- A function and a method may not be in an overriding relationship (SQLSTATE 42745). For more
information about overriding, see
CREATE TYPE (Structured)
. - A function may not have the same signature as a method (comparing the first parameter-type of the function with the subject-type of the method) (SQLSTATE 42723).
- Creating a function with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
- In a partitioned database environment, the use of SQL in external user-defined functions or methods is not supported (SQLSTATE 42997).
- Only routines defined as NO SQL can be used to define an index extension (SQLSTATE 428F8).
- If the function allows SQL, the external program must not attempt to access any federated objects (SQLSTATE 55047).
- A Java routine defined as NOT FENCED will be invoked as if it had been defined as FENCED THREADSAFE.
- XML parameters are only supported in LANGUAGE JAVA external functions when the PARAMETER STYLE DB2GENERAL clause is specified.
- Table access restrictions
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.
- Setting of the default value: Parameters of a function that are defined with a default value are set to their default value when the functions is invoked, but only if a value is not supplied for the corresponding argument, or is specified as DEFAULT, when the function is invoked.
- Privileges: The definer of a function always receives the EXECUTE privilege WITH
GRANT OPTION on the function, as well as the right to drop the function.
When the function is used in an SQL statement, the function definer must have the EXECUTE privilege on any packages used by the function or EXECUTEIN privilege or DATAACCESS authority on the schema containing the packages.
- EXTERNAL ACTION functions: If an EXTERNAL ACTION function is invoked in other than the outermost select list, the results are unpredictable since the number of times the function is invoked will vary depending on the access plan used.
- Syntax
alternatives: The following syntax alternatives are supported for compatibility with
previous versions of this database product and with other database products. These alternatives are
non-standard and should not be used.
- PARAMETER STYLE DB2SQL can be specified in place of PARAMETER STYLE SQL
- NOT VARIANT can be specified in place of DETERMINISTIC, and VARIANT can be specified in place of NOT DETERMINISTIC
- NULL CALL can be specified in place of CALLED ON NULL INPUT, and NOT NULL CALL can be specified in place of RETURNS NULL ON NULL INPUT
The following syntax is accepted as the default behavior:- ASUTIME NO LIMIT
- NO COLLID
- PROGRAM TYPE SUB
- STAY RESIDENT NO
- CCSID UNICODE in a Unicode database
- CCSID ASCII in a non-Unicode database if PARAMETER CCSID UNICODE is not specified
- Creating a secure function: Normally users with SECADM
authority do not have privileges to create database objects such as triggers and functions.
Typically; they will examine the data accessed by the function, ensure it is secure, then grant the
CREATE_SECURE_OBJECT authority to someone who currently has required privileges to create a secure
user-defined function. After the function is created, they will revoke the CREATE_SECURE_OBJECT
authority from the function owner.
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.
- Invoking other user-defined functions in a secure function: If a secure user-defined function invokes other user-defined functions, the database manager does not validate whether those nested user-defined functions have the SECURED attribute. If those nested functions can access sensitive data, the user with SECADM authority needs to ensure those functions are allowed to access those data and a change control audit procedure has been established for all changes to those functions.
- Replacing an existing function such that the secure attribute is changed (from SECURED to NOT SECURED and vice versa): Packages and dynamically cached SQL statements that depend on the function may be invalidated because the secure attribute affects the access path selection for statements involving tables for which row or column level access control is activated.
Examples
- Pellow is registering the CENTER function in his PELLOW schema. Let those keywords that will
default do so, and let the system provide a function specific name:
CREATE FUNCTION CENTER (INT,FLOAT) RETURNS FLOAT EXTERNAL NAME 'mod!middle' LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC NO SQL NO EXTERNAL ACTION
- Now, McBride (who has DBADM authority) is registering another CENTER 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.CENTER (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
- The following example 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 null checking), if the CREATE FUNCTION statement had used NOT NULL CALL. The CREATE FUNCTION statement: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
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 */
- The following example 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
- 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 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)
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.- PREDICATES WHEN = 1 indicates that when this function appears as
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.within(g1, g2) = 1
- FILTER USING mbrOverlap refers to a filtering function mbrOverlap, which is a cheaper version of the WITHIN predicate. In this example, the mbrOverlap function takes the minimum bounding rectangles as input and quickly determines if they overlap or not. If the minimum bounding rectangles of the two input shapes do not overlap, then g1 will not be contained with g2. Therefore the tuple can be safely discarded, avoiding the application of the expensive WITHIN predicate.
- The SEARCH BY INDEX EXTENSION clause indicates that combinations of index extension and search target can be used for this user-defined predicate.
- PREDICATES WHEN = 1 indicates that when this function appears as
- 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 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.
- PREDICATES WHEN > EXPRESSION AS distExpr is another valid predicate specification.
When an expression is specified in the WHEN clause, the result type of that expression is used for
determining if the predicate is a user-defined predicate in the DML statement. For example:
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.
Alternatively, the following statement is also a valid user-defined predicate:
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.SELECT T1.C1 FROM T1, T2 WHERE distance(T1.P1, T2.P1) > distance (T1.P2, T2.P2)
- The SEARCH BY INDEX EXTENSION clause indicates that combinations of index extension and search target can be used for this user-defined-predicate. In the case of the distance function, the expression identified as distExpr is also one of the search arguments that is passed to the range-producer function (defined as part of the index extension). The expression identifier is used to define a name for the expression so that it is passed to the range-producer function as an argument.
- PREDICATES WHEN > EXPRESSION AS distExpr is another valid predicate specification.
When an expression is specified in the WHEN clause, the result type of that expression is used for
determining if the predicate is a user-defined predicate in the DML statement. For example: