DB2 10.5 for Linux, UNIX, and Windows

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:
  • CREATE_EXTERNAL_ROUTINE authority on the database and 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 exists
  • DBADM authority

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

Read syntax diagramSkip visual syntax diagram
>>-CREATE FUNCTION--function-name------------------------------->

>--(--| parameter-declaration |--)--●--------------------------->

                     .-,---------------------------.      
                     V                             |      
>--RETURNS TABLE--(----column-name--| data-type2 |-+--)--------->

>--| option-list |---------------------------------------------><

parameter-declaration

|--+----------------+--| data-type1 |--+--------------------+---|
   '-parameter-name-'                  '-| default-clause |-'   

data-type1, data-type2

|--+-| built-in-type |----+-------------------------------------|
   +-distinct-type-name---+   
   +-structured-type-name-+   
   '-REF--(--type-name--)-'   

built-in-type

|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
   | +-+-INTEGER-+-+                                                                      |   
   | | '-INT-----' |                                                                      |   
   | '-BIGINT------'                                                                      |   
   |                  .-(5,0)-------------------.                                         |   
   +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+   
   | | '-DEC-----' |  |          .-,0-------.   |                                         |   
   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                                         |   
   |   '-NUM-----'               '-,integer-'                                             |   
   |          .-(53)------.                                                               |   
   +-+-FLOAT--+-----------+--+------------------------------------------------------------+   
   | |        '-(integer)-'  |                                                            |   
   | +-REAL------------------+                                                            |   
   | |         .-PRECISION-. |                                                            |   
   | '-DOUBLE--+-----------+-'                                                            |   
   |           .-(34)-.                                                                   |   
   +-DECFLOAT--+------+-------------------------------------------------------------------+   
   |           '-(16)-'                                                                   |   
   |                    .-(1)------------------------.                                    |   
   +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+   
   | | | '-CHAR------'  '-(integer-+-------------+-)-'          |  |              (1) | | |   
   | | |                           +-OCTETS------+              |  '-FOR BIT DATA-----' | |   
   | | |                           '-CODEUNITS32-'              |                       | |   
   | | '-+-VARCHAR----------------+--(integer-+-------------+-)-'                       | |   
   | |   '-+-CHARACTER-+--VARYING-'           +-OCTETS------+                           | |   
   | |     '-CHAR------'                      '-CODEUNITS32-'                           | |   
   | |                                  .-(1M)-----------------------------.            | |   
   | '-+-CLOB------------------------+--+----------------------------------+------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-+-------------+-)-'              |   
   |     '-CHAR------'                             +-K-+ +-OCTETS------+                  |   
   |                                               +-M-+ '-CODEUNITS32-'                  |   
   |                                               '-G-'                                  |   
   |            .-(1)------------------------.                                            |   
   +-+-GRAPHIC--+----------------------------+------+-------------------------------------+   
   | |          '-(integer-+-------------+-)-'      |                                     |   
   | |                     +-CODEUNITS16-+          |                                     |   
   | |                     '-CODEUNITS32-'          |                                     |   
   | +-VARGRAPHIC--(integer-+-------------+-)-------+                                     |   
   | |                      +-CODEUNITS16-+         |                                     |   
   | |                      '-CODEUNITS32-'         |                                     |   
   | |         .-(1M)-----------------------------. |                                     |   
   | '-DBCLOB--+----------------------------------+-'                                     |   
   |           '-(integer-+---+-+-------------+-)-'                                       |   
   |                      +-K-+ +-CODEUNITS16-+                                           |   
   |                      +-M-+ '-CODEUNITS32-'                                           |   
   |                      '-G-'                                                           |   
   |                                  .-(1)-------.                                       |   
   +-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+   
   | | | '-NATIONAL--+-CHAR------+-'  '-(integer)-'      |       |                        |   
   | | |             '-CHARACTER-'                       |       |                        |   
   | | '-+-NVARCHAR-------------------------+--(integer)-'       |                        |   
   | |   +-NCHAR VARYING--------------------+                    |                        |   
   | |   '-NATIONAL--+-CHAR------+--VARYING-'                    |                        |   
   | |               '-CHARACTER-'                               |                        |   
   | |                                      .-(1M)-------------. |                        |   
   | '-+-NCLOB---------------------------+--+------------------+-'                        |   
   |   +-NCHAR LARGE OBJECT--------------+  '-(integer-+---+-)-'                          |   
   |   '-NATIONAL CHARACTER LARGE OBJECT-'             +-K-+                              |   
   |                                                   +-M-+                              |   
   |                                                   '-G-'                              |   
   |                          .-(1M)-------------.                                        |   
   +-+-BLOB----------------+--+------------------+----------------------------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                                        |   
   |                                     +-K-+                                            |   
   |                                     +-M-+                                            |   
   |                                     '-G-'                                            |   
   +-+-DATE-------------------------+-----------------------------------------------------+   
   | +-TIME-------------------------+                                                     |   
   | |            .-(--6--)-------. |                                                     |   
   | '-TIMESTAMP--+---------------+-'                                                     |   
   |              '-(--integer--)-'                                                       |   
   | .-SYSPROC.-.                   (2) (3)                                               |   
   '-+----------+--DB2SECURITYLABEL-------------------------------------------------------'   

default-clause

|--DEFAULT--+-NULL-------------+--------------------------------|
            +-constant---------+   
            +-special-register-+   
            +-global-variable--+   
            '-(--expression--)-'   

option-list

|--●--LANGUAGE----OLEDB----●--+-------------------------+--●---->
                              '-SPECIFIC--specific-name-'      

                                .-NOT DETERMINISTIC-.      
>--EXTERNAL--NAME--'string'--●--+-------------------+--●-------->
                                '-DETERMINISTIC-----'      

   .-STATIC DISPATCH-.     .-RETURNS NULL ON NULL INPUT-.      
>--+-----------------+--●--+----------------------------+--●---->
                           '-CALLED ON NULL INPUT-------'      

   .-NO EXTERNAL ACTION-.                                   
>--+--------------------+--●--+----------------------+--●------->
   '-EXTERNAL ACTION----'     '-CARDINALITY--integer-'      

   .-NOT SECURED-.   
>--+-------------+----------------------------------------------|
   '-SECURED-----'   

Notes:
  1. 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).
  2. DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
  3. 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 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. XML is invalid (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:
Read syntax diagramSkip visual syntax diagram
>>-'--+-server--!--+--------+-----------------------------------------------+--'-><
      |            '-rowset-'                                               |      
      '-!--+--------+--!--connectstring--+--------------------------------+-'      
           '-rowset-'                    '-!--COLLATING_SEQUENCE = -+-N-+-'        
                                                                    '-Y-'          

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® Database for Linux, UNIX, and Windows. 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 Database for Linux, UNIX, and Windows.

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

Examples

  1. 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';			    
  2. 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';
  3. 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;