Microsoft OLE DB is a set of OLE/COM interfaces that provide applications with uniform access to data stored in diverse information sources. The OLE DB component DBMS architecture defines OLE DB consumers and OLE DB providers. An OLE DB consumer is any system or application that consumes OLE DB interfaces; an OLE DB provider is a component that exposes OLE DB interfaces. There are two classes of OLE DB providers: OLE DB data providers, which own data and expose their data in tabular format as a rowset; and OLE DB service providers, which do not own their own data, but encapsulate some services by producing and consuming data through OLE DB interfaces.
DB2 Universal Database simplifies the creation of OLE DB applications by enabling you to define table functions that access an OLE DB data source. DB2 is an OLE DB consumer that can access any OLE DB data or service provider. You can perform operations including GROUP BY, JOIN, and UNION on data sources that expose their data through OLE DB interfaces. For example, you can define an OLE DB table function to return a table from a Microsoft Access database or a Microsoft Exchange address book, then create a report that seamlessly combines data from this OLE DB table function with data in your DB2 database.
Using OLE DB table functions reduces your application development effort by providing built-in access to any OLE DB provider. For C, Java, and OLE automation table functions, the developer needs to implement the table function, whereas in the case of OLE DB table functions, a generic built-in OLE DB consumer interfaces with any OLE DB provider to retrieve data. You only need to register a table function of language type OLEDB, and refer to the OLE DB provider and the relevant rowset as a data source. You do not have to do any UDF programming to take advantage of OLE DB table functions.
To use OLE DB table functions with DB2 Universal Database, you must install OLE DB 2.0 or later, available from Microsoft at http://www.microsoft.com. If you attempt to invoke an OLE DB table function without first installing OLE DB, DB2 issues SQLCODE 465, SQLSTATE 58032,reason code 35. For the system requirements and OLE DB providers available for your data sources, refer to your data source documentation. For a list of samples that define and use OLE DB table functions, see Appendix B, Sample Programs. For the OLE DB specification, see the Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.
To define an OLE DB table function with a single CREATE FUNCTION statement, you must:
OLE DB data sources expose their data in tabular form, called rowset. A rowset is a set of rows, each having a set of columns. The RETURNS TABLE clause includes only the columns relevant to the user. The binding of table function columns to columns of a rowset at an OLE DB data source is based on column names. If the OLE DB provider is case sensitive, place the column names in quotation marks; for example, "UPPERcase". For information on rowset names which can be fully qualified names, see Fully Qualified Rowset Names. For the mapping of OLE DB data types to DB2 data types, see Supported OLE DB Data Types. For the complete syntax of the CREATE FUNCTION statement and the rules for the EXTERNAL NAME clause, refer to the SQL Reference.
The EXTERNAL NAME clause can take either of the following forms:
'server!rowset'
or
'!rowset!connectstring'
where:
You can use a connection string in the EXTERNAL NAME clause of a CREATE FUNCTION statement, or specify the CONNECTSTRING option in a CREATE SERVER statement.
For example, you can define an OLE DB table function and return a table from a Microsoft Access database with the following CREATE FUNCTION and SELECT statements:
CREATE FUNCTION orders ()
RETURNS TABLE (orderid INTEGER, ...)
LANGUAGE OLEDB
EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;
Data Source=c:\msdasdk\bin\oledb\nwind.mdb';
SELECT orderid, DATE(orderdate) AS orderdate,
DATE(shippeddate) AS shippeddate
FROM TABLE(orders()) AS t
WHERE orderid = 10248;
Instead of putting the connection string in the EXTERNAL NAME clause, you can create and use a server name. For example, assuming you have defined the server Nwind as described in Defining a Server Name for an OLE DB Provider, you could use the following CREATE FUNCTION statement:
CREATE FUNCTION orders ()
RETURNS TABLE (orderid INTEGER, ...)
LANGUAGE OLEDB
EXTERNAL NAME 'Nwind!orders';
OLE DB table functions also allow you to specify one input parameter of any character string data type. Use the input parameter to pass command text directly to the OLE DB provider. If you define an input parameter, do not provide a rowset name in the EXTERNAL NAME clause. DB2 passes the command text to the OLE DB provider for execution and the OLE DB provider returns a rowset to DB2. Column names and data types of the resulting rowset need to be compatible with the RETURNS TABLE definition in the CREATE FUNCTION statement. Since binding to the column names of the rowset is based on matching column names, you must ensure that you name the columns properly.
The following example registers an OLE DB table function, which retrieves store information from a Microsoft SQL Server 7.0(TM) database. The connection string is provided in the EXTERNAL NAME clause. Since the table function has an input parameter to pass through command text to the OLE DB provider, the rowset name is not specified in the EXTERNAL NAME clause. The query example passes in a SQL command text which retrieves information about the top three stores from a SQL Server database.
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;
Some rowsets need to be identified in the EXTERNAL NAME clause through a fully qualified name. A fully qualified name incorporates either or both of the following:
For information on the support offered by your OLE DB provider for catalog and schema names, refer to the documentation of the literal information of your OLE DB provider.
If DBLITERAL_CATALOG_NAME is not NULL in the literal information of your provider, use a catalog name and the value of DBLITERAL_CATALOG_SEPARATOR as a separator. To determine whether the catalog name goes at the beginning or the end of the fully qualified name, refer to the value of DBPROP_CATALOGLOCATION in the property set DBPROPSET_DATASOURCEINFO of your OLE DB provider.
If DBLITERAL_SCHEMA_NAME is not NULL in the literal information of your provider, use a schema name and the value of DBLITERAL_SCHEMA_SEPARATOR as a separator.
If the names contain special characters or match keywords, enclose the names in the quote characters specified for your OLE DB provider. The quote characters are defined in the literal information of your OLE DB provider as DBLITERAL_QUOTE_PREFIX and DBLITERAL_QUOTE_SUFFIX. For example, in the following EXTERNAL NAME the specified rowset includes catalog name pubs and schema name dbo for a rowset called authors, with the quote character " used to enclose the names.
EXTERNAL NAME '!"pubs"."dbo"."authors"!Provider=SQLOLEDB.1;...';
For more information on constructing fully qualified names, refer to Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998, and the documentation for your OLE DB provider.
To provide a server name for an OLE DB data source that you can use for many CREATE FUNCTION statements, use the CREATE SERVER statement as follows:
For example, you can define the server name Nwind for the Microsoft Access OLE DB provider with the following CREATE SERVER statement:
CREATE SERVER Nwind
WRAPPER OLEDB
OPTIONS (CONNECTSTRING 'Provider=Microsoft.Jet.OLEDB.3.51;
Data Source=c:\msdasdk\bin\oledb\nwind.mdb');
You can then use the server name Nwind to identify the OLE DB provider in a CREATE FUNCTION statement, for example:
CREATE FUNCTION orders ()
RETURNS TABLE (orderid INTEGER, ...)
LANGUAGE OLEDB
EXTERNAL NAME 'Nwind!orders';
For the complete syntax of the CREATE SERVER statement, refer to the SQL Reference. For information on user mappings for OLE DB providers, see Defining a User Mapping.
You can provide user mappings for your DB2 users to provide access to OLE DB data sources with an alternate username and password. To map usernames for specific users, you can define user mappings with the CREATE USER MAPPING statement. To provide a user mapping shared by all users, add the username and password to the connection string of your CREATE FUNCTION or CREATE SERVER statement. For example, to create a specific user mapping for the DB2 user JOHN on the OLE DB server Nwind, use the following CREATE USER MAPPING statement:
CREATE USER MAPPING FOR john
SERVER Nwind
OPTIONS (REMOTE_AUTHID 'dave', REMOTE_PASSWORD 'mypwd');
To provide the equivalent access to all of the DB2 users that call the OLE DB table function orders, use the following CONNECTSTRING either in a CREATE FUNCTION or CREATE SERVER statement:
CREATE FUNCTION orders ()
RETURNS TABLE (orderid INTEGER, ...)
LANGUAGE OLEDB
EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;User ID=dave;
Password=mypwd;Data Source=c:\msdasdk\bin\oledb\nwind.mdb';
DB2 applies the following user mapping rules:
For the complete syntax of the CREATE USER MAPPING statement, refer to the SQL Reference.
The following table shows how DB2 data types map to the OLE DB data types described in Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998. Use the mapping table to define the appropriate RETURNS TABLE columns in your OLE DB table functions. For example, if you define an OLE DB table function with a column of data type INTEGER, DB2 requests the data from the OLE DB provider as DBTYPE_I4.
For mappings of OLE DB provider source data types to OLE DB data types, refer to the OLE DB provider documentation. For examples of how the ANSI SQL, Microsoft Access, and Microsoft SQL Server providers might map their respective data types to OLE DB data types, refer to the Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.
Table 12. Mapping DB2 Data Types to OLE DB
| DB2 Data Type | OLE DB Data Type |
|---|---|
| SMALLINT | DBTYPE_I2 |
| INTEGER | DBTYPE_I4 |
| BIGINT | DBTYPE_I8 |
| REAL | DBTYPE_R4 |
| FLOAT/DOUBLE | DBTYPE_R8 |
| DEC (p, s) | DBTYPE_NUMERIC (p, s) |
| DATE | DBTYPE_DBDATE |
| TIME | DBTYPE_DBTIME |
| TIMESTAMP | DBTYPE_DBTIMESTAMP |
| CHAR(N) | DBTYPE_STR |
| VARCHAR(N) | DBTYPE_STR |
| LONG VARCHAR | DBTYPE_STR |
| CLOB(N) | DBTYPE_STR |
| CHAR(N) FOR BIT DATA | DBTYPE_BYTES |
| VARCHAR(N) FOR BIT DATA | DBTYPE_BYTES |
| LONG VARCHAR FOR BIT DATA | DBTYPE_BYTES |
| BLOB(N) | DBTYPE_BYTES |
| GRAPHIC(N) | DBTYPE_WSTR |
| VARGRAPHIC(N) | DBTYPE_WSTR |
| LONG GRAPHIC | DBTYPE_WSTR |
| DBCLOB(N) | DBTYPE_WSTR |
| Note: | OLE DB data type conversion rules are defined in the Microsoft OLE DB
2.0 Programmer's Reference and Data Access SDK, Microsoft
Press, 1998. For example:
|