INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2

ID: Q218590

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.

Back to top

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.

Back to top

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

From the Start menu, select the Microsoft SNA Server program group.

Select the Data Access program group.

Select New OLE DB Data Source. The Data Link Properties page appears.

Configure the data source information for the selected provider. Click Help for more information.

Click OK to save the Data Link.

For Windows 95/98

From the Start menu, select the Microsoft SNA Server program group.

Select the Data Access program group.

Select New OLE DB Data Source. The Data Link Properties page appears.

Configure the data source information for the selected provider. Click Help for more information.

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.

Back to top

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:

From the Start menu, select the Microsoft SNA Server program group.

Select the Data Access program group.

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 :
Version : WINDOWS:4.0 SP2
Platform : WINDOWS
Issue type : kbinfo