SQL Reference

CREATE FUNCTION (OLE DB External Table)

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (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
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--.
>-----+--------------------+--*--------------------------------->
      |               (1)  |
      '-DETERMINISTIC------'
 
      .-RETURNS NULL ON NULL INPUT--.  (2)
>-----+-----------------------------+---------*----------------->
      '-CALLED ON NULL INPUT--------'
 
      .-NO EXTERNAL ACTION--.
>-----+---------------------+--*----+-----------------------+--->
      '-EXTERNAL ACTION-----'       '-CARDINALITY--integer--'
 
>----*---------------------------------------------------------><

Notes:

NOT VARIANT may be specified in place of DETERMINISTIC and VARIANT may be specified in place of NOT DETERMINISTIC.
NULL CALL may be specified in place of CALLED ON NULL INPUT and NOT NULL CALL may be specified in place of RETURNS NULL ON NULL INPUT.

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 may not 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 as described in Basic Predicate.

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. 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 (see language-specific sections of Application Development Guide for details on the mapping between the SQL data types and OLE DB 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 (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. See OLE DB Table Functions in Application Development Guide for more details.

>>-'--+-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. Refer to the language-specific sections of Application Development Guide for details.

COLLATING_SEQUENCE

Specifies whether the data source uses the same collating sequence as DB2 Universal Database. See CREATE SERVER for details. 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.

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, i.e. 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

FENCED, FINAL CALL, SCRATCHPAD, PARAMETER STYLE DB2SQL, DISALLOW PARALLEL, NO DBINFO, and NO SQL are implicit in the statement and can be specified. Refer to CREATE FUNCTION (External Table) for specific descriptions.
When choosing the data types for the parameters of a user-defined function, consider the rules for promotion that will affect its input values (see Promotion of Data Types). For example, a constant which may be used as an input value could have a built-in data type 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 the following data types should not be used:
- FLOAT- use DOUBLE or REAL instead.
- NUMERIC- use DECIMAL instead.
- LONG VARCHAR- use CLOB (or BLOB) instead.
For information on creating a user-defined OLE DB external table function, see the Application Development Guide.
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.

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 userid in the connection string. Refer to CREATE SERVER, CREATE WRAPPER and CREATE USER MAPPING for details on the statements.

  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 a SQL command text to retrieve the top 3 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;

[ Top of Page | Previous Page | Next Page ]