CREATE FUNCTION (external table) statement

The CREATE FUNCTION (External Table) statement is used to register a user-defined external table function at the current server.

A table function can be used in the FROM clause of a SELECT, and returns a table to the SELECT by returning one row at a time.

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

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

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.

The privileges held by the authorization ID of the statement must also include one of the following authorities:

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.

To create a not-fenced function, the privileges held by the authorization ID of the statement must also include at least one of the following authorities (does not apply to OLE DB external table functions or methods):

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: Db2 11.1.4.7 security special build 41268 includes changes to the implicit authorities of both the SYSADM and the DBADM authorities. By default, the SYSADM authority, instead of 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 the value EXTERNAL_ROUTINE_DBADM or NOT_FENCED_ROUTINE_DBADM respectively, then the DBADM authority also implicitly has these privileges.

Syntax

parameter-declaration

data-type1, data-type2

built-in-type

default-clause

option-list

Notes:

¹ 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).
² DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
³ 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.
⁴ For information about creating LANGUAGE OLE DB external table functions, see CREATE FUNCTION (OLE DB External Table). For information about creating LANGUAGE SQL table functions, see CREATE FUNCTION (SQL Scalar, Table, or Row).

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 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).
BINARY and VARBINARY data types are invalid with LANGUAGE CLR and OLE (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:

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:

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:

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:

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.
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
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:
  'd:\mylib\myfunc.dll'
  If an absolute path ID is being used to identify the routine body, be sure to append the .dll extension.
class_id

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).

NAME identifier

Operating system	Directory the Java virtual machine will look in for the classes
Linux AIX Solaris HP-UX	'.../myPacks/UserFuncs/'
Windows	'...\myPacks\UserFuncs\'

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

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 32-bit operating systems.

For information about creating LANGUAGE OLE DB external table functions, see CREATE FUNCTION (OLE DB External Table).

CPP

The database manager calls the user-defined function by invoking the evaluate method of a C++ 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 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.

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.

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 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 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 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 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. On each invocation of the user-defined function, an additional argument is passed to the external function which addresses the scratchpad. The 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 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.

FINAL CALL cannot be specified in combination with LANGUAGE CPP.

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 or PARAMETER TYLE NPSGENERIC (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 range from 0 to 9,223,372,036,854,775,807.

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 has infinite cardinality (that is, if 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 never terminate and will have to be interrupted. Examples of such queries are those that contain a GROUP BY or an ORDER BY clause. For that reason, writing UDFs that have infinite cardinality 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).

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.

Rules

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).
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.

Notes

When choosing the data types for the parameters of a user-defined function, consider the rules for promotion that will affect its input values. For example, a constant which may be used as an input value could have a built-in data type that is 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, it is recommended to use the following data types:
- DOUBLE or REAL instead of FLOAT
- DECIMAL instead of NUMERIC
- CLOB (or BLOB) instead of LONG VARCHAR
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.
A Java routine defined as NOT FENCED will be invoked as if it had been defined as FENCED THREADSAFE.
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 schema DATAACCESS authority on the schema containing the packages. .
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.
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
- VARIANT can be specified in place of NOT DETERMINISTIC
- NULL CALL can be specified in place of CALLED ON NULL INPUT
- NOT NULL CALL can be specified in place of RETURNS NULL ON NULL INPUT
- DB2GENRL can be specified in place of DB2GENERAL
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.
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.

Examples

Example 1: The following example registers a table function written to return a row consisting of a single document identifier column for each known document in a text management system. The first parameter matches a given subject area and the second parameter contains a given string.
Within the context of a single session, the UDF will always return the same table, and therefore it is defined as DETERMINISTIC. Note the RETURNS clause which defines the output from DOCMATCH. FINAL CALL must be specified for each table function. In addition, the DISALLOW PARALLEL keyword is added as table functions cannot operate in parallel. Although the size of the output for DOCMATCH is highly variable, CARDINALITY 20 is a representative value, and is specified to help the database optimizer.
```
   CREATE FUNCTION DOCMATCH (VARCHAR(30), VARCHAR(255))
     RETURNS TABLE (DOC_ID CHAR(16))
     EXTERNAL NAME '/common/docfuncs/rajiv/udfmatch'
     LANGUAGE C
     PARAMETER STYLE SQL
     NO SQL
     DETERMINISTIC
     NO EXTERNAL ACTION
     NOT FENCED
     SCRATCHPAD
     FINAL CALL
     DISALLOW PARALLEL
     CARDINALITY 20
```

Example 2: The following example registers an OLE table function that is used to retrieve message header information and the partial message text of messages in Microsoft Exchange.

   CREATE FUNCTION MAIL()
     RETURNS TABLE (TIMERECEIVED DATE,
                    SUBJECT VARCHAR(15),
                    SIZE INTEGER,
                    TEXT VARCHAR(30))
     EXTERNAL NAME 'tfmail.header!list'
     LANGUAGE OLE
     PARAMETER STYLE SQL
     NOT DETERMINISTIC
     FENCED
     CALLED ON NULL INPUT
     SCRATCHPAD
     FINAL CALL
     NO SQL
     EXTERNAL ACTION
     DISALLOW PARALLEL