Q218590: INF: Configuring Data Sources for the Microsoft OLE DB Provider
Article: Q218590
Product(s): Microsoft SNA Server
Version(s): WINDOWS:4.0 SP2
Operating System(s):
Keyword(s):
Last Modified: 19-JUL-2001
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft OLE DB Provider for DB2, version 4.0 SP2
-------------------------------------------------------------------------------
SUMMARY
=======
This article provides information on the following subjects:
Overview of the Microsoft OLE DB Provider for DB2
OLE DB Data Links
Adding or Configuring a Data Link
Browsing OLE DB Data Sources
Configuring OLE DB Data Source for DB2 using Data Link
MORE INFORMATION
================
Overview of the Microsoft OLE DB Provider for DB2
-------------------------------------------------
To use the Microsoft OLE DB Provider for DB2 with an OLE DB consumer application,
you must either;
- Create a Microsoft data link file (UDL), and call this from your
application.
-or-
- Call the provider using a connection string that includes the provider name.
Microsoft Data Access Components (MDAC) 2.0 and later include Data Links, a
generic method for managing and loading connections to OLE DB data sources. Data
Links also supports finding and maintaining persistent connections to OLE DB
data sources. For additional information on using UDLs, as well as a list of
supported OLE DB initialization properties and ADO connection string arguments,
see "Using the OLE DB Provider for DB2" in the SNA Server SDK documentation
online.
OLE DB Data Links
-----------------
You must configure data source information for each host data source object that
is to be accessed using OLE DB Provider for DB2. The default parameters for OLE
DB Provider for DB2 are used for the data source only, and these parameters need
to be configured for each data source separately. Data Links provides a uniform
method for creating file-persistent OLE DB data source object definitions in the
form of UDL files. Applications, such as the RowsetViewer sample included with
the SNA Server SDK, can open created UDL files and pass the stored
initialization string to OLE DB Provider for DB2 at run time.
Adding or Configuring a Data Link
---------------------------------
You must create a Data Link to configure parameters for your OLE DB data source.
You can create a new Data Link by clicking the shortcut in the SNA Server\Data
Access program folder. The properties of a Data Links file can be edited by
opening the file from Windows Explorer. To edit the data links file:
For Windows NT:
1. From the Start menu, select the Microsoft SNA Server program group.
2. Select the Data Access program group.
3. Select New OLE DB Data Source. The Data Link Properties page appears.
4. Configure the data source information for the selected provider. Click Help
for more information.
5. Click OK to save the Data Link.
For Windows 95/98:
1. From the Start menu, select the Microsoft SNA Server program group.
2. Select the Data Access program group.
3. Select New OLE DB Data Source. The Data Link Properties page appears.
4. Configure the data source information for the selected provider. Click Help
for more information.
5. Click OK to save the Data Link.
By default, Data Links are created in the Program Files\Common Files\System\OLE
DB\Data Links folder. However, you can create a Data Link in any location by
opening the target folder, selecting New Microsoft Data Link from the File menu,
and configuring the Data Link Properties page.
Browsing OLE DB Data Sources
----------------------------
By default, Data Links are created in the Program Files\Common Files\System\OLE
DB\Data Links folder. A shortcut is provided in the Microsoft SNA Server program
group. To browse the data sources:
1. From the Start menu, select the Microsoft SNA Server program group.
2. Select the Data Access program group.
3. Select Browse OLE DB Data Source. The list of data links saved in the default
location is displayed.
Configuring OLE DB Data Source for DB2 using Data Links
-------------------------------------------------------
The Provider tab allows the user to select the OLE DB provider (the provider name
string) to be used in this UDL file from a list of possible OLE DB providers.
Choose Microsoft OLE DB Provider for DB2.
The Connection tab allows the user to configure the basic properties required to
connect to a data source. For OLE DB Provider for DB2, the connection properties
include the following values:
Data Source: The data source is an optional parameter that can be used to
describe the data source. When the Data Links configuration program is loaded
from the SNA Server program folder, the Data Source field is required. This
field is used to name the UDL file, which is stored in the Program
Files\Common Files\System\OLE DB\Data directory.
User name: A valid user name is normally required to access data on DB2.
Optionally, you can persist a user name in the Data Link. The OLE DB provider
will prompt the user at runtime to enter a valid password. Additionally, the
prompt dialog box will allow the user to override the user name stored in the
Data Link.
The AS/400 computer is case-sensitive with regard to user ID and password. The
AS/400 only accepts a DB2/400 user ID and password in UPPER CASE. (If DB2/400
connection fails because of incorrect authentication, the OLE DB provider
will re-send the authentication, forcing the user ID and password into UPPER
CASE.)
The mainframe is case-insensitive. This means that on mainframe computers, you
can enter the DB2 user ID and password in any case. The OLE DB provider will
send the these values in UPPER CASE.
DB2 UDB for Windows NT is case-sensitive. The user ID is stored in UPPER CASE.
The password is stored in mixed case. The user must enter the password in the
correct case. The OLE DB provider sends exactly the password in the case
entered by the user. The user does not have to qualify the the Windows NT
user name with the Windows NT domain name.
Password: A valid password is normally required to access data on DB2.
Optionally, you can choose to save the password in the UDL file by selecting
the "Allow saving password" check box.
WARNING: This option persists the authentication information in plain text
within the UDL file.
Initial Catalog: This OLE DB property is used as the first part of a
three-part fully qualified table name.
In DB2 (MVS, OS/390), this property is referred to as LOCATION. The
SYSIBM.LOCATIONS table lists all the accessible locations. To find the
location of the DB2 to which you need to connect, ask your administrator to
look in the TSO Clist DSNTINST under the DDF definitions. These definitions
are provided in the DSNTIPR panel in the DB2 installation manual.
In DB2/400, this property is referred to as RDBNAM. The RDBNAM value can be
determined by invoking the WRKRDBDIRE command from the console to the OS/400
system. If there is no RDBNAM value, then one can be created using the Add
option.
In DB2 Universal Database, this property is referred to as DATABASE.
The Connection tab also includes a Test Connection button that can be used to
test the connection parameters. The connection can only be tested after all of
the required parameters are entered. When you click this button, a session is
established to the remote DB2 system using OLE DB Provider for DB2.
The All tab allows the user to configure additional properties used to connect to
a data source. Some of the properties in the All tab are required. These
properties may be edited by selecting a property from the displayed list and
selecting Edit Value. For Microsoft OLE DB Provider for DB2, these properties
include the following values:
Alternate TP Name: This property is only required when connecting to SQL/DS
(DB2/VM or DB2/VSE), and is referred to as the remote transaction program.
APPC Local LU Alias: The name of the local LU alias configured in SNA Server.
APPC Mode Name: The APPC mode that matches the host configuration and SNA
Server configuration. Legal values for the APPC mode include QPCSUPP (common
system default), #INTER (interactive), #INTERSC (interactive with minimal
routing security), #BATCH (batch), #BATCHSC (batch with minimal routing
security), and #IBMRDB (DB2 remote database access).
APPC Remote LU Alias: The name of the remote LU alias configured in SNA
Server.
Auto Commit Mode: This property allows for implicit COMMIT on all SQL
statements. In Auto Commit Mode, every database operation is a transaction
that is committed when performed. This mode is suitable for common
transactions that consist of a single SQL statement. It is unnecessary to
delimit or specify completion of these transactions. No ROLLBACK is allowed
when using Auto Commit Mode. The default is True.
Cache Authentication: The provider's data source object or enumerator is
allowed to cache sensitive authentication information such as a password in
an internal cache. The default is False.
Default Isolation Level: This determines the level of isolation used in cases
of simultaneous access to DB2 objects by multiple applications. The default
is NC. The following levels are supported:
CS Cursor Stability.
In DB2/400, this corresponds to COMMIT(*CS).
In ANSI, this corresponds to Read Committed (RC).
NC No Commit.
In DB2/400, this corresponds to COMMIT(*NONE).
In ANSI, this corresponds to No Commit (NC).
UR Uncommitted Read.
In DB2/400, this corresponds to COMMIT(*CHG).
In ANSI, this corresponds to Read Uncommitted.
RS Read Stability.
In DB2/400, this corresponds to COMMIT(*ALL).
In ANSI, this corresponds to Repeatable Read.
RR Repeatable Read.
In DB2/400, this corresponds to COMMIT(*RR).
In ANSI, this corresponds to Serializable (Isolated).
Default Schema: The name of the collection where the provider looks for
catalog information. The OLE DB provider uses Default Schema to restrict
results sets for popular operations, such as enumerating a list of tables in
a target collection (for example, OLE DB IDBSchemaRowset DBSCHEMA_TABLES).
Additionally, the OLE DB provider uses Default Schema to build a SQL SELECT
statement for IOpenRowset::OpenRowset requests. For additional information on
Default Schema, please read the following article in the Microsoft Knowledge
Base:
Q217757 INF: Default Schema to be used with ODBC Driver for DB2 and OLE DB
Provider for DB2
Extended Properties: A method to specify additional provider-specific
properties. Properties passed through this parameter should be delimited by
semicolons and will be interpreted by the provider's underlying network
client.
Host CCSID: The character code set identifier (CCSID) matching the DB2 data as
represented on the remote computer. This parameter defaults to U.S./Canada
(37). The CCSID property is required when processing binary data as character
data. Unless the "Process Binary as Character" value is set, character data
is converted based on the DB2 column CCSID and default ANSI code page.
Network Address: This property is used to locate the target DB2 computer,
specifically the TCP/IP address or TCP/IP host name/alias associated with the
DRDA port. The network address is required when connecting by means of
TCP/IP.
Network Port: This property is used to locate the target DB2 DRDA service
access port when connecting by means of TCP/IP. The default is the well-known
DRDA port address of 446.
Network Transport Library: The network transport dynamic link library property
designates whether the provider connects by means of SNA LU6.2 or TCP/IP. The
default value is SNA. If TCP/IP is selected, then values for Network Address
and Network Port are required. If the default SNA is selected, then values
for APPC Local LU Alias, APPC Mode Name, and APPC Remote LU Alias are
required.
Package Collection: The name of the DRDA COLLECTION where you want the driver
to store and bind DB2 packages. This could be same as the Default Schema.
This is required parameter. For additional information, please read the
following article in the Microsoft Knowledge Base:
Q216810 Creating Packages for Use with ODBC Driver for DB2 and OLE DB
Provider for DB2
PC Code Page: This property is required when processing binary data as
character data. Unless the "Process Binary as Character" value is set,
character data is converted based on the default ANSI code page configured in
Windows. The default value for this property is Latin 1 (1252).
Persist Security Info: Optionally, you can choose to save the password in the
UDL file by selecting the "Allow saving password" check box.
WARNING: This option persists the authentication information in plain text
within the UDL file.
Process Binary as Character: This option treats binary (CCSID 65535) data type
fields as character data type fields on a per-data source basis. The Host
CCSID and PC Code Page values are required input and output parameters.
Read Only: Creates a read-only data source. The user has read-only access to
objects, such as tables, and cannot do update operations, such as INSERT,
UPDATE, or DELETE.
REFERENCES
==========
OS/390 MVS OS/400
Additional query words:
======================================================================
Keywords :
Technology : kbAudDeveloper kbOLEDBSearch kbOLEDBProvDB2400SP2 kbOLEDBProvSearch
Version : WINDOWS:4.0 SP2
Issue type : kbinfo
=============================================================================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1986-2002.