### CREATE FUNCTION (OLE DB External Table) statement

This statement is used to register a user-defined OLE DB external table function to access data from an OLE DB provider.

A table function may 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:

- SYSADM or DBADM authority
- CREATE_EXTERNAL_ROUTINE authority on the database, and at least one of:
  - 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.

If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised.

Syntax

    >>-CREATE FUNCTION--function-name------------------------------->

    >--(--+--------------------------------+--)--*------------------>
          '-+----------------+--data-type1-'
            '-parameter-name-'

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

    >--+-------------------------+--*------------------------------->
       '-SPECIFIC--specific-name-'

    >--EXTERNAL--NAME--'string'--*--LANGUAGE----OLEDB----*---------->

       .-NOT DETERMINISTIC-.     .-STATIC DISPATCH-.
    >--+-------------------+--*--+-----------------+--*------------->
       '-DETERMINISTIC-----'

       .-RETURNS NULL ON NULL INPUT-.     .-NO EXTERNAL ACTION-.
    >--+----------------------------+--*--+--------------------+---->
       '-CALLED ON NULL INPUT-------'     '-EXTERNAL ACTION----'

    >--*--+----------------------+--*------------------------------><
          '-CARDINALITY--integer-'

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 (with a maximum length of 18). 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-name
  Specifies an optional name for the parameter.
data-type1
  Identifies the input parameter of the function, and specifies the data type of the parameter. If no input parameter is specified, then data is retrieved from the external source possibly subsetted through query optimization. The input parameter can be any character or graphic string data type and it 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. Length is not considered in this type comparison. Therefore CHAR(8) and CHAR(35) are considered to be the same type. For a Unicode database, CHAR(13) and GRAPHIC(8) are considered to be the same type. A duplicate signature raises an SQL error (SQLSTATE 42723).

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.
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 (with a maximum length of 18). 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 characters.

  The string specified is used to establish a connection and session with a 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 performed.

      >>-'--+-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 Universal Database. For details, see "CREATE SERVER". Valid values are as follows:

      Y = Same collating sequence

      N = Different collating sequence

    If COLLATING_SEQUENCE is not specified, then the data source is assumed to have a different collating sequence from DB2 Universal Database.

  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, DB2 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
  This optional clause specifies whether or not the function takes some action that changes the state of an object not managed by the database manager. Optimizations that assume functions with no external impacts are prevented by specifying EXTERNAL ACTION. For example: sending a message, ringing a bell, or writing a record to a file.
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, DB2 will assume a finite value as a default- the same value assumed for tables for which the RUNSTATS utility has not gathered statistics.

  Warning: if a function does in fact have infinite cardinality, i.e. it returns a row every time it is called to do so, never returning the "end-of-table" condition, then queries which require the "end-of-table" condition to correctly function will be infinite, and will have to be interrupted. Examples of such queries are those involving GROUP BY and ORDER BY. The user is advised to not write such UDFs.

Notes

- Compatibilities
  - For compatibility with previous versions of DB2:
    - 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
- 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.

Examples

Example 1: The following registers 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';

Example 2: The following registers 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';

Example 3: The following registers 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, ' ||
                         ' stores.stor_name as name, ' ||
                         ' sum(sales. qty) as sales ' ||
                     ' from sales, stores ' ||
                     ' where sales.stor_id = stores.stor_id ' ||
                     ' group by sales.stor_id, stores.stor_name ' ||
                     ' order by sum(sales.qty) desc ')) as f;

Related reference

- Basic predicate
- CREATE SERVER statement
- CREATE USER MAPPING statement
- CREATE WRAPPER statement
- CREATE FUNCTION (External Table) statement
- Promotion of data types