CREATE FUNCTION (OLE DB external table) statement

The CREATE FUNCTION (OLE DB External Table) statement is used to register a user-defined OLE DB external table function to access data from an OLE DB provider.

A table function can be used in the FROM clause of a SELECT.

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

Group privileges are not considered for any table or view specified in the CREATE FUNCTION statement.

If the SECURED option is specified, the authorization ID of the statement must include SECADM authority or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).

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.

Description

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 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. If no input parameter is specified, data is retrieved from the external source possibly subsetted through query optimization. The input parameter passes command text to an OLE DB provider.

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. 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. A duplicate signature returns an error (SQLSTATE 42723).

parameter-name

Specifies an optional name for the input parameter.

data-type1

Specifies the data type of the input parameter. The data type can be any character or graphic string data type or a distinct type based on a character or graphic string data type. Parameters of type BINARY, VARBINARY, and XML are not supported (SQLSTATE 42815).

For a more complete description of each built-in data type, see CREATE TABLE.

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.

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

RETURNS 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, resembling the style of a simple CREATE TABLE statement which has no additional specifications (constraints, for example).

column-name: Specifies the name of the column which must be the same as the corresponding rowset column name. 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. BINARY, VARBINARY, and XML are not supported (SQLSTATE 42815).

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 NAME 'string'

This clause identifies the external table and an OLE DB provider.

The 'string' option is a string constant with a maximum of 254 bytes.

The string specified is used to establish a connection and session with an OLE DB provider, and retrieve data from a rowset. The OLE DB provider and data source do not need to exist when the CREATE FUNCTION statement is executed.

The string can be specified as follows:

server

Identifies the local name of a data source as defined by CREATE SERVER.

rowset

Identifies the rowset (table) exposed by the OLE DB provider. Fully qualified table names must be provided for OLE DB providers that support catalog or schema names.

connectstring

String version of the initialization properties needed to connect to a data source. The basic format of a connection string is based on the ODBC connection string. The string contains a series of keyword/value pairs separated by semicolons. The equal sign (=) separates each keyword and its value. Keywords are the descriptions of the OLE DB initialization properties (property set DBPROPSET_DBINIT) or provider-specific keywords.

COLLATING_SEQUENCE

Specifies whether the data source uses the same collating sequence as Db2®. For details, see CREATE SERVER. Valid values are as follows:

Y = Same collating sequence
N = Different collating sequence

If COLLATING_SEQUENCE is not specified, the data source is assumed to have a different collating sequence than Db2.

If server is provided, connectstring or COLLATING_SEQUENCE are not allowed in the external name. They are defined as server options CONNECTSTRING and COLLATING_SEQUENCE. If no server is provided, a connectstring must be provided. If rowset is not provided, the table function must have an input parameter to pass through command text to the OLE DB provider.

LANGUAGE OLEDB

This means the database manager will deploy a built-in generic OLE DB consumer to retrieve data from the OLE DB provider. No table function implementation is required by the developer.

LANGUAGE OLEDB table functions can be created on any platform, but only executed on platforms supported by Microsoft OLE DB.

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.

STATIC DISPATCH

This optional clause indicates that at function resolution time, the database manager chooses a function based on the static types (declared types) of the parameters of the function.

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.

If RETURNS NULL ON NULL INPUT is specified and if at execution time any one of the function's arguments is null, the user-defined function is not called and the result is the empty table; that is, a table with no rows.

If CALLED ON NULL INPUT is specified, then at execution time regardless of whether any arguments are null, the user-defined function is called. It can return an empty table or not, depending on its logic. But responsibility for testing for null argument values lies with the UDF.

The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatibility. Similarly, NOT NULL CALL may be used as a synonym for RETURNS NULL ON NULL INPUT.

NO EXTERNAL ACTION or 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 NO EXTERNAL ACTION.

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.
EXTERNAL ACTION: Specifies that the function takes an action that changes the state of an object that the database manager does not manage.

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 2 147 483 647 inclusive.

If the CARDINALITY clause is not specified for a table function, a finite value is assumed as the default. The finite value is the same value that is 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.

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

Notes

FENCED, FINAL CALL, SCRATCHPAD, PARAMETER STYLE SQL, DISALLOW PARALLEL, NO DBINFO, NOT THREADSAFE, and NO SQL are implicit in the statement and can be specified.
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:
- 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.
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.
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 Db2 and with other database products. These alternatives are non-standard and should not be used.
- 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
Creating a secure function: Normally users with SECADM authority do not have privileges to create database objects such as triggers or 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.
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

Register an OLE DB table function, which retrieves order information from a Microsoft Access database. The connection string is defined in the external name.

   CREATE FUNCTION orders ()
     RETURNS TABLE (orderid INTEGER,
                    customerid CHAR(5),
                    employeeid INTEGER,
                    orderdate TIMESTAMP,
                    requireddate TIMESTAMP,
                    shippeddate TIMESTAMP,
                    shipvia INTEGER,
                    freight dec(19,4))
     LANGUAGE OLEDB
     EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;
                             Data Source=c:\sqllib\samples\oledb\nwind.mdb
     !COLLATING_SEQUENCE=Y';

Register an OLE DB table function, which retrieves customer information from an Oracle database. The connection string is provided through a server definition. The table name is fully qualified in the external name. The local user john is mapped to the remote user dave. Other users will use the guest user ID in the connection string.

   CREATE SERVER spirit
     WRAPPER OLEDB
     OPTIONS (CONNECTSTRING 'Provider=MSDAORA;Persist Security Info=False;
                             User ID=guest;password=pwd;Locale Identifier=1033;
                             OLE DB Services=CLIENTCURSOR;Data Source=spirit');

   CREATE USER MAPPING FOR john
     SERVER spirit
     OPTIONS (REMOTE_AUTHID 'dave', REMOTE_PASSWORD 'mypwd');

   CREATE FUNCTION customers ()
     RETURNS TABLE (customer_id INTEGER,
                    name  VARCHAR(20),
                    address VARCHAR(20),
                    city VARCHAR(20),
                    state VARCHAR(5),
                    zip_code INTEGER)
     LANGUAGE OLEDB
     EXTERNAL NAME 'spirit!demo.customer';

Register an OLE DB table function, which retrieves information about stores from a MS SQL Server 7.0 database. The connection string is provided in the external name. The table function has an input parameter to pass through command text to the OLE DB provider. The rowset name does not need to be specified in the external name. The query example passes in SQL statement text to retrieve the top three stores.

   CREATE FUNCTION favorites (varchar(600))
     RETURNS TABLE (store_id CHAR (4),
                    name  VARCHAR (41),
                    sales INTEGER)
     SPECIFIC favorites
     LANGUAGE OLEDB
     EXTERNAL NAME '!!Provider=SQLOLEDB.1;Persist Security Info=False;
                    User ID=sa;Initial Catalog=pubs;Data Source=WALTZ;
                    Locale Identifier=1033;Use Procedure for Prepare=1;
                    Auto Translate=False;Packet Size=4096;Workstation ID=WALTZ;
                    OLE DB Services=CLIENTCURSOR;';

   SELECT *
     FROM TABLE (favorites
                (' select top 3 sales.stor_id as store_id, ' CONCAT
                     ' stores.stor_name as name, ' CONCAT
                     ' sum(sales. qty) as sales ' CONCAT
                 ' from sales, stores ' CONCAT
                 ' where sales.stor_id = stores.stor_id ' CONCAT
                 ' group by sales.stor_id, stores.stor_name ' CONCAT
                 ' order by sum(sales.qty) desc ')) as f;