- 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.
- 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). 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.
- The function must be defined with LANGUAGE C (SQLSTATE 42613).
- The function can be referenced only on the right hand side of
an assignment statement that is in a compound SQL (compiled) statement,
and the function reference cannot be part of an expression (SQLSTATE
42887).
- INOUT
- Identifies the parameter as both an input and output parameter
for the function.
- The function must be defined with LANGUAGE C (SQLSTATE 42613).
- The function can be referenced only on the right hand side of
an assignment statement that is in a compound SQL (compiled) statement,
and the function reference cannot be part of an expression (SQLSTATE
42887).
- 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, see "Data 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).
For a user-defined distinct type, the length, precision, or scale
attributes for the parameter are those of the source type of the distinct
type (those specified on CREATE TYPE). A distinct type parameter is
passed as the source type of the distinct type. If the name of the
distinct type is unqualified, the database manager resolves the schema
name by searching the schemas in the SQL path.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).
- 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 above under 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 run-time 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.
- 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).
The
string can
be specified as follows:
>>-'--+-library_id-------+--+------------+--'------------------><
'-absolute_path_id-' '-!--func_id-'
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:
- On UNIX systems, 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.
- On Windows operating
systems, 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.
On UNIX systems, for example,
'/u/jchui/mylib/myfunc' would cause the database manager to look in
/u/jchui/mylib for the myfunc shared library.
On Windows operating systems, '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.
On a UNIX system, for
example, 'mymod!func8' would direct the database manager to look for
the library $inst_home_dir/sqllib/function/mymod and to use entry
point func8 within that library.
On Windows operating systems, '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.
- 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:
>>-'--+----------+--class_id--+-.-+--method_id--'--------------><
'-jar_id :-' '-!-'
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 Java virtual
machine will look in directory '.../myPacks/UserFuncs/' for the classes.
On Windows operating systems,
the Java virtual machine will
look in directory '...\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'. DB2® 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
string can
be specified as follows:
>>-'--assembly--:--class_id--!--method_id--'-------------------><
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 DB2 install path (for
example, c:\sqllib\function). If the file resides in a subdirectory
of the install 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.
- 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).
The
string can
be specified as follows:
>>-'--+-progid-+--!--method_id--'------------------------------><
'-clsid--'
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:
{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.
- method_id
- Identifies the method name of the OLE object to be invoked.
- 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 is used to specify the language interface
convention to which the user-defined function body is written.
- C
- This means the database manager will call 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
- This means the database manager will call the user-defined function
as a method in a Java class.
- CLR
- This means the database manager will call the user-defined function
as a method in a .NET class. At this time, LANGUAGE CLR is only supported
for user-defined functions running on Windows operating systems. NOT FENCED cannot
be specified for a CLR routine (SQLSTATE 42601).
- OLE
- This means the database manager will call 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 only supported for user-defined functions stored
in DB2 for Windows operating systems. THREADSAFE may
not be specified for UDFs defined with LANGUAGE OLE (SQLSTATE 42613).
- 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.
- 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 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 a DB2 database. DB2 databases
take some precautions against many of the common types of inadvertent
failures that might occur, but cannot guarantee complete integrity
when NOT FENCED user-defined functions are used.
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).
- 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.
- NO SQL, CONTAINS SQL, READS SQL DATA
- Indicates whether the function issues any SQL statements and,
if so, what type.
- NO SQL
- Indicates that the function cannot execute any SQL statements
(SQLSTATE 38001).
- CONTAINS SQL
- Indicates that SQL statements that neither read nor modify SQL
data can be executed by the function (SQLSTATE 38004 or 42985). Statements
that are not supported in any function return a different error (SQLSTATE
38003 or 42985).
- READS SQL DATA
- Indicates that some SQL statements that do not modify SQL data
can be included in the function (SQLSTATE 38002 or 42985). Statements
that are not supported in any function return a different error (SQLSTATE
38003 or 42985).
- STATIC DISPATCH
- This optional clause indicates that at function resolution time, DB2 chooses a function 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. 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 above. 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 intra-partition
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 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.
- 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:
FINAL CALL is not supported for PARAMETER STYLE JAVA functions.
- 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 DB2 will accept this specification. 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.
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
If any of these options is specified or implied, the default
value is DISALLOW PARALLEL.
- 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 DB2 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
LANGUAGE OLE (SQLSTATE 42613) or 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 run-time 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-hand
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)
If an argument invokes another function or method, these rules
are also enforced for this nested function or method. However, system-generated
observer methods are allowed as arguments to the filter function (or
any function or method used as an argument), as long as the argument
evaluates to a built-in data type.
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
- 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 to tell DB2 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.
DB2 Version 9.7 for Linux, UNIX, and Windows