Installation and Configuration Supplement

Adding OLE DB data sources to a Federated System

To access OLE DB data sources using OLE DB table functions:

1. Install and configure OLE DB 2.0 or higher and OLE DB provider for data source. Follow the software requirements from your OLE DB provider.
2. Use the CREATE WRAPPER statement to define the wrapper library that will be used to access OLE DB providers.

   Wrappers are the mechanism that federated servers use to communicate with and retrieve data from data sources. The following example shows a CREATE WRAPPER statement:

   CREATE WRAPPER OLEDB
   

   where OLEDB is the default name of the wrapper module used with OLE DB providers. You can substitute the default name with a name that you choose; however, if you do so, you also must include the LIBRARY parameter and the name of the wrapper library for your federated server platform. See the SQL Reference, Volume 2 for more information about wrapper library names.
3. Use the CREATE SERVER statement to define a server name for an OLE DB data source. For example:

   CREATE SERVER Nwind 
   WRAPPER OLEDB 
   OPTIONS (
   CONNECTSTRING 'Provider=Microsoft.Jet.OLEDB.4.0; 
      Data Source=c:\msdasdk\bin\oledb\nwind.mdb',
   COLLATING_SEQUENCE 'Y');
   

   where:
   • Nwind is a name that you assign to an OLE DB data source.
   • OLEDB is the wrapper name.
   • OPTIONS lists other parameters. In this example:
     • CONNECTSTRING provides initialization properties needed to connect to a data source. The string contains a series of keyword and 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.

       For the complete syntax and semantics of the CONNECTSTRING option, see the Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.

     • COLLATING_SEQUENCE specifies whether the data source uses the same collating sequence as DB2 Universal Database. Valid values are Y (the same collating sequence is used) and N (a different collating sequence is used). If a COLLATING_SEQUENCE is not specified, the data source is assumed to have a different collating sequence than DB2 Universal Database.
4. If a user ID or password at the federated server is different from a user ID or password at an OLE DB data source, use the CREATE USER MAPPING statement to map the local user ID to the user ID and password defined at the OLE DB data source; for example:

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

   where:
   • john is the local user ID that you are mapping to a user ID defined at an OLE DB data source.
   • Nwind is the name of the OLE DB data source that you defined in the CREATE SERVER statement.
   • dave is the user ID at the OLE DB data source to which you are mapping john. This value is case sensitive.
   • mypwd is the password associated with dave. This value is case-sensitive.
5. You can use the server name Nwind to identify the OLE DB provider by using the CREATE FUNCTION statement:

   CREATE FUNCTION orders ()
   RETURNS TABLE (orderid INTEGER, ...)
   LANGUAGE OLEDB
   EXTERNAL NAME 'Nwind!orders';