- 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 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'
(SQLSTATE 42939).
A number of names used as keywords in predicates
are reserved for system use, and cannot be used as a function-name (SQLSTATE
42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL,
LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison
operators.
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 table
function should not be given the same name as a built-in function.
- (parameter-declaration,...)
- Identifies
the number of input parameters of the function, and specifies the
data type and optional default value of each parameter. One entry
in the list must be specified for each parameter that the function
will expect to receive. No more than 90 parameters are allowed (SQLSTATE
54023).
It is possible to register a function that has no parameters.
In this case, the parentheses must still be coded, with no intervening
data types. For example:
CREATE FUNCTION WOOFER() ...
No
two identically-named functions within a schema are permitted to have
exactly the same type for all corresponding parameters. Lengths, precisions,
and scales are not considered in this type comparison. Therefore,
CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2)
and DECIMAL (4,3). 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).
- parameter-name
- Specifies an optional name for the input parameter. 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 input 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).
- 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 for a parameter of type ARRAY, ROW, or
CURSOR (SQLSTATE 429BB).
- 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
- Specifies the output of the function.
- TABLE
- Specifies that the output of the function is a table. The parentheses
that follow this keyword delimit a list of the names and types of
the columns of the table. The list style resembles the style of a
simple CREATE TABLE statement which has no additional specifications
(constraints, for example). No more than 255 columns are allowed (SQLSTATE
54011).
- column-name
- Specifies the name of this column. The name cannot be qualified
and the same name cannot be used for more than one column of the table.
- data-type2
- Specifies the data type of the column, and can be any data type
supported for a parameter of a UDF written in the particular language,
except for structured types (SQLSTATE 42997).
- AS LOCATOR
- When data-type2 is a LOB type or distinct
type based on a LOB type, the use of this option indicates that the
function is returning a locator for the LOB value that is instantiated
in the result table.
The valid types for use with this clause
are discussed in the "CREATE FUNCTION (external scalar)" statement
topic.
- GENERIC TABLE
- Specifies that the output of the function is a generic table.
This clause is allowed only if you specify the LANGUAGE JAVA clause
and the PARAMETER STYLE DB2GENERAL clause (SQLSTATE 42613).
- 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
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 user-written code that 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.
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:
| Operating system |
Library name location |
|---|
Linux
AIX®
Solaris
HP-UX |
If myfunc was given as the library_id,
and the database manager is being run from /u/production,
the database manager will look for the function in library /u/production/sqllib/function/myfunc |
| Windows |
The database manager will look for the function
in a directory path that is specified by the LIBPATH or PATH environment
variable |
- 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
Solaris
HP-UX |
A value of '/u/jchui/mylib/myfunc' would cause
the database manager to look in /u/jchui/mylib for
the myfunc shared library. |
| Windows |
A value of 'd:\mylib\myfunc.dll' would cause
the database manager to load the dynamic link library, myfunc.dll,
from the d:\mylib directory. If an absolute path
ID is being used to identify the routine body, be sure to append the .dll extension. |
- ! 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
Solaris
HP-UX |
A value of 'mymod!func8' would direct the database
manager to look for the library $inst_home_dir/sqllib/function/mymod and
to use entry point func8 within that library. |
| Windows |
A value of 'mymod!func8' would direct the database
manager to load the mymod.dll file and to call
the func8() function in the dynamic link library
(DLL). |
If the string is not properly formed, an error
is returned (SQLSTATE 42878).
In any case, 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.
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 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
Solaris
HP-UX |
'.../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
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 database product's installation 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 clause identifies the name of the user-written code which
implements the function being defined. The 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 supported for
user-defined functions for this database product only in Windows 32-bit
operating systems.
For information about creating LANGUAGE OLE DB external table functions, see "CREATE FUNCTION
(OLE DB External Table)".
- 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 be specified when LANGUAGE JAVA is used.
- 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 or user-defined
types (SQLSTATE 560C1).
If the database is not a Unicode database,
table functions can be created with PARAMETER CCSID UNICODE, but the
following rules apply:
- The alternate collating sequence must be specified in the database
configuration before creating the table function (SQLSTATE 56031).
PARAMETER CCSID UNICODE table functions collate with the alternate
collating sequence specified in the database configuration.
- Tables or table functions created with CCSID ASCII, and tables
or table functions created with CCSID UNICODE, cannot both be used
in a single SQL statement (SQLSTATE 53090). This applies to tables
and table functions referenced directly in the statement, as well
as to tables and table functions referenced indirectly (such as, for
example, through referential integrity constraints, triggers, materialized
query tables, and tables in the body of views).
- Table functions created with PARAMETER CCSID UNICODE cannot be
referenced in SQL functions or SQL methods (SQLSTATE 560C0).
- An SQL statement that references a table function created with
PARAMETER CCSID UNICODE cannot invoke an SQL function or SQL method
(SQLSTATE 53090).
- Graphic types, the XML type, and user-defined types cannot be
used as parameters to PARAMETER CCSID UNICODE table functions (SQLSTATE
560C1).
- SQL statements are always interpreted in the database code page.
In particular, this means that every character in literals, hex literals,
and delimited identifiers must have a representation in the database
code page; otherwise, the character will be replaced with the substitution
character.
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 table 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 table function that is non-deterministic is one
that references special registers, global variables, non-deterministic
functions, or sequences in a way that affects the table function result
table.
- 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 (NOT FENCED), or not (FENCED).
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).
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 may 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 of course this null argument
condition cannot arise, and it does not matter how this specification
is coded.
If RETURNS NULL ON NULL INPUT is specified, and if,
at table function OPEN time, any of the function's arguments are
null, then the user-defined function is not called. The result of
the attempted table function scan is the empty table (a table with
no rows).
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, 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. If the ALLOW PARALLEL, EXECUTE ON ALL DATABASE PARTITIONS, and RESULT TABLE
DISTRIBUTED clauses are all specified, NO SQL is the only option allowed.
- 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. This scratchpad has the following characteristics:
- length, if specified, sets the size
of the scratchpad in bytes and must be between 1 and 32 767
(SQLSTATE 42820). The default value is 100.
- 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,
two scratchpads would be assigned.
SELECT A.C1, B.C2
FROM TABLE (UDFX(:hv1)) AS A,
TABLE (UDFX(:hv1)) AS B
WHERE ...
- It
is persistent. It is initialized at the beginning of the execution
of the statement, and can be used by the external table function to
preserve the state of the scratchpad from one call to the next. If
the FINAL CALL keyword is also specified for the UDF, then the scratchpad
is NEVER altered, and any resources anchored in the scratchpad should
be released when the special FINAL call is made.
If NO FINAL CALL
is specified or defaulted, then the external table function should
clean up any such resources on the CLOSE call, as the database server
will re-initialize the scratchpad on each OPEN call. This determination
of FINAL CALL or NO FINAL CALL and the associated behavior of the
scratchpad could be an important consideration, particularly if the
table function will be used in a subquery or join, since that is when
multiple OPEN calls can occur during the execution of a statement.
- 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.
(As previously
outlined, the FINAL CALL/NO FINAL CALL keyword is used to control
the re-initialization of the scratchpad, and also dictates when the
external table function should release resources anchored in the scratchpad.)
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.
- FINAL CALL or NO FINAL CALL
- This optional clause specifies whether a final call (and a separate
first call) is to be made to an external function. It also controls
when the scratchpad is re-initialized. If
NO FINAL CALL is specified, then the database server can only make
three types of calls to the table function: open, fetch and close.
However, if FINAL CALL is specified, then in addition to open, fetch
and close, a first call and a final call can be made to the table
function.
For external table functions, the call-type argument
is ALWAYS present, regardless of which option is chosen.
If
the final call is being made because of an interrupt or end-of-transaction,
the UDF may not issue any SQL statements except for CLOSE cursor (SQLSTATE
38505). A special value is passed in the "call type" argument for
these special final call situations.
- DISALLOW PARALLEL or ALLOW PARALLEL EXECUTE ON ALL DATABASE
PARTITIONS RESULT TABLE DISTRIBUTED
- Specifies whether or not, for a single reference to the function,
the invocation of the function is to be parallelized.
- DISALLOW PARALLEL
- Specifies
that on each invocation of the function, the function is invoked on
a single database partition.
- ALLOW PARALLEL EXECUTE ON ALL DATABASE PARTITIONS RESULT
TABLE DISTRIBUTED
- Specifies
that on each invocation of the function, the function is invoked on
all database partitions. The union of the result sets obtained on
each database partition is returned. The function cannot execute SQL
statements (the NO SQL clause must also be specified).
- NO DBINFO or DBINFO
- This
optional clause specifies whether certain specific information known
to the database server is to be passed to the function 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).
If DBINFO is specified, a structure containing the following
information is passed to the function:
- Database name - the name of the currently connected database
- Application ID - the unique application ID that is established
for each connection to the database
- Application authorization ID - the application runtime authorization
ID, regardless of any nested functions between this function and the
application
- Code page - the database code page
- Schema name - not applicable to external table functions
- Table name - not applicable to external table functions
- Column name - not applicable to external table functions
- Database version or release - the version, release, and modification
level of the database server that is invoking the function
- Platform - the server's platform type
- Table function result column numbers - an array of result column
numbers that is used by the statement referencing the function; this
information enables the function to return only required column values
instead of all column values
- Database partition number - the number of the database partition
on which the external table function is invoked; in a single database
partition environment, this value is 0
- CARDINALITY integer
- This optional clause provides an estimate of the expected number
of rows to be returned by the function for optimization purposes.
Valid values for integer range from 0 to
9 223 372 036 854 775 807 inclusive.
If
the CARDINALITY clause is not specified for a table function, assume
a finite value is assumed as a default; the same value assumed for
tables for which the RUNSTATS utility has not gathered statistics.
Warning:
If a function does, in fact, have infinite cardinality - that is,
it returns a row every time it is called to do so, and never returns
the "end-of-table" condition - then queries that require the end-of-table
condition to correctly function will be infinite, and will have to
be interrupted. Examples of such queries are those that contain a
GROUP BY or an ORDER BY clause. Writing such UDFs is not recommended.
- 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 a parameter 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 results (SQLSTATE
42741). If a required FROM SQL transform function is not defined
for the given group-name and structured type, an error results (SQLSTATE
42744).
- 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.
- 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, SQLCODE -20470).
DB2 10.5 for Linux, UNIX, and Windows