Configuring Data Links for the OLE DB Provider for DB2

The NewSnaDS tool can be used to open and modify an existing UDL file. Once a Data Link file is selected, the Data Link Properties dialog box appears with several property tabs:

Provider
Connection
Advanced
All

The properties of a Data Link file can also be edited using Windows Explorer by a right-clicking the mouse on the UDL file and selecting Properties. A Properties dialog box appears with the above OLE DB property tabs plus additional General, Security, and Summary tabs that provide access to standard file information (file location, file type, file size, file dates, file security permissions for access, etc.). The General tab has a text box with the name of the Data Link. This filename must end with the .UDL extension if the file is to be recognized as a Data Link file. Note that the Security and Summary tabs are available on NTFS files systems, not on the older FAT file systems. The Provider, Connection, Advanced, and All tabs provide access to the Data Link properties that need to be configured to connect to the DB2 system.

The Provider Tab

The Provider tab enables you to select the OLE DB provider (an alias to the provider name string) to use in the UDL file from a list of possible OLE DB providers. Select the Microsoft OLE DB Provider for DB2. The parameters and fields displayed in the remaining tabs (Connection, Advanced, and All) are determined by the OLE DB Provider that is selected.

The Connection Tab

The Connection tab enables you to configure the basic properties required to connect to a data source. In the proposed solution, we will be connecting to the IBM DB2 system with the data tables that are to be accessed. The Connection tab dialog contains several sections:

Data source and Network connectivity
Authentication
Database Properties

For the Microsoft OLE DB Provider for DB2, the Connection tab includes the following properties for Data source and Network connectivity values:

Property

Description

Data source

The data source is a property that can be used to describe the data source. Any descriptive text can be used.

Network

This drop-down list box allows selecting the type of network connection to be used. The allowable options are APPC Connection or TCP/IP Connection.

If APPC Connection is selected (using SNA LU 6.2), click the More Options ( ) button to open a dialog box for configuring APPC network settings. The properties you can configure include: the APPC local LU alias, the APPC remote LU alias, and the APPC mode name used for communication with the host. The default value for the APPC mode normally defaults to QPCSUPP. The local and remote LU alias fields do not have default values. The APPC mode name can be selected from the drop-down list box.

If TCP/IP Connection is selected, click the More Options ( ) button to open a dialog box for configuring TCP/IP network settings. The properties you can configure include the IP address of the DB2 host (or a hostname alias for this computer) and the Network Port (TCP/IP port) used for communication with the host. The default value for the Network Port is 446. The IP address of the host has no default value.

In the proposed scenario using SNA to connect to the AS/400 system, we are selecting an APPC Connection. This option requires that values be selected for the local APPC LU, remote APPC LU, and the APPC mode from drop-down lists. The local and remote APPC LUs should correspond with the values configured in Host Integration Server for connection with the remote AS/400 system. The APPC mode specifies the mode of communication used over SNA for this connection. The APPC mode for a Data Link defaults to the default value specified for SNA connections in Host Integration Server 2000. In most cases, it is unnecessary to change this value from the default of QPCSUPP.

For the Microsoft OLE DB Provider for DB2, the Connection tab includes the following properties for authentication information:

Property	Description
Single sign-on	Click this checkbox to enable using the Host Integration Security features providing a single sign-on to access this OLE DB data source. Note that single sign-on is only supported using the APPC Connection option (SNA LU 6.2). When this checkbox is selected, the User name and Password fields are unavailable. The User name and Password fields are set based on the login name used for the Windows 2000 domain login. When this checkbox is not selected, the User name and Password fields must normally contain appropriate values in order to access data sources on hosts.
User name	A valid User name and Password are normally required to access data sources on a host. These values are case-sensitive. Users must not check the Single sign-on option if a specific User name and Password are to be entered.
Password	A valid User name and Password are normally required to access data sources on hosts. These values are case-sensitive. The Blank password checkbox is only applicable for a Test Connection. In order to enter a password, the user will need to clear the Blank password check box if it is checked. If Blank password is checked, then a Test Connection with a blank password will not cause the OLE DB Provider to prompt for a password. Optionally, users can choose to save the password in the UDL file by clicking the Allow saving password check box. Users and administrators should be warned that this option saves the authentication information (password) in plain text within the UDL file.

The AS/400 requires that the User name and Password properties be in uppercase. When connecting to DB2/400, these parameters must be passed as uppercase strings. When connecting to DB2 on IBM mainframes, the User name and Password parameters can be in mixed case.

In the proposed scenario, it is possible to connect using a specific User name and Password defined in DB2 on the AS/400 system or use the single sign-on feature (often referred to as integrated Windows security). If a specific DB2 user name and Password is to be used, this information will likely need to be saved into the UDL file since this Data Link needs to be used with Data Transformation Services as part of a scheduled process. The user name and Password are saved in clear text in the UDL file. In this case, for security reasons it is imperative that the UDL file be protected with an Access Control List (ACL) that restricts access to all but system administrators. Saving the user name and Password also forces this UDL file to be updated whenever the Password associated with the user name is changed. So for a variety of reasons, specifying a User name and Password is not the preferred authentication option.

Single sign-on is the preferred authentication option, but this will require that the user account under which the DTS package will run must have the same User name and Password in the Windows domain as the remote DB2 database on the AS/400 system. Note that on most AS/400 systems, the User name and Password must be in uppercase.

For the Microsoft OLE DB Provider for DB2, the Connection tab includes the following properties for database property values:

Property	Description
Initial catalog	This OLE DB property is used as the first part of a 3-part fully qualified table name. 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. In DB2 Universal Database, this property is referred to as DATABASE. This is a required property and must not be blank.
Package collection	The name of the DRDA target collection (AS/400 library) where the Microsoft OLE DB Provider for DB2 should store and bind DB2 packages. This could be same as the Default Schema. The Microsoft OLE DB Provider for DB2 uses packages to issue dynamic and static SQL statements. Package names are not restricted and can be upper case, lower case, or mixed case. This is a required property and must not be blank.
Default schema	The name of the Collection where the OLE DB Provider for DB2 looks for catalog information. The Default Schema is the "SCHEMA" name for the target collection of tables and views. 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 DB2/400, the Default Schema is the target COLLECTION name. For DB2 Universal Database (UDB), the Default Schema is the SCHEMA name. If the user does not provide a value for Default Schema, then the OLE DB Provider uses the USER_ID provided at login. Obviously, this default is inappropriate in many cases. Therefore, it is essential that the Default Schema value in the data source be defined.

For the proposed scenario, the Initial Catalog should contain the value of the RDBNAM or DATABASE on the remote DB2 system. The Default Schema may often be the same value as the Initial Catalog.

The OLE DB Provider will create DB2 packages dynamically in the location to which the user points using the Package Collection property. By default, the OLE DB Provider will automatically create one package in the target collection, if one does not exist, at the time the user issues the first SQL statement. The package is created with GRANT EXECUTE authority to a single <AUTH_ID> only, where AUTH_ID is based on the User ID value configured in the data source. The package is created for use by SQL statements issued under the same isolation level.

A problem can arise in multi-user environments. For example, if a user specifies a Package Collection value that represents a DB2 collection used by multiple users, but this user does not have authority to GRANT execute rights to the packages to other users (e.g., PUBLIC), then the package is created for use only by this user. This means that other users may be unable to access the required package. The solution is for an administrative user with package administrative rights to create a set of packages for use by all users.

The OLE DB Provider for DB2 ships with a program for use by administrators to create packages. The crtpkg.exe tool is a Windows GUI application for use by the administrator to create packages. This tool can be run using a privileged User ID to create packages in collections accessed by multiple users. This tool will create a set of packages and grant EXECUTE privilege to PUBLIC for all users. Once created, the packages are listed in the DB2 for OS/400 QSYS2.SYSPACKAGE, and the DB2 Universal Database (UDB) SYSIBM.SYSPACKAGE catalog tables.

The Connection tab also includes a Test Connection button that can be used to test the connection properties. The connection can only be tested after all of the required parameters are entered. The values for the Initial Catalog, Package Collection, and Default Schema parameters must not be blank or the test connection will fail. When this button is pressed, an APPC or a TCP/IP session will attempt to be established with the host DB2 system using the OLE DB Provider for DB2.

If a Test Connection fails, Host Integration Server 2000 supports several troubleshooting features. If the connection to the DB2 system is using a TCP/IP connection, the Host Integration Server 2000 tracing facilities (Trace Initiator and Trace Viewer) can be used to trace the connection process in detail. Be forewarned that if tracing is enabled for the OLE DB Provider for DB2 (referred to as the DB2 Network Library in Trace Initiator), then the User name and Password from the connection string are captured and viewable as plain text in the trace file. This is not an issue if Windows integrated security is used for authentication to the DB2 system since the Password field is empty and does not contain a real password. But if a dedicated User name and Password are used for authentication, then extreme care should be taken because of security concerns if other users are allowed access to these trace files. For security, an ACL should be placed on trace files used for troubleshooting DB2 connections that allow only system administrators to access these files.

The Advanced Tab

The Advanced tab allows users to select the character code set identifier used by the host, the PC code page used on the client, and some specific options when using the OLE DB Provider for DB2.

For the Microsoft OLE DB Provider for DB2, these properties include the following values:

Property	Description
Host CCSID	The character code set identifier (CCSID) matching the DB2 data as represented on the remote host computer. The CCSID property is required when processing binary data as character data. Unless the Process Binary as Character value is set to true, character data is converted based on the DB2 column CCSID and default ANSI code page. This property defaults to U.S./Canada (37).
PC code page	The PC code page property indicates the code page to be used on the PC for character code conversion. This property is required when processing binary data as character data. Unless the Process Binary as Character checkbox is selected (value is set to true), character data is converted based on the default ANSI code page configured in Windows. This property defaults to Latin 1 (1252).
Read only	When this option is checked, the OLE DB Provider for DB2 creates a read-only data source by setting the Mode property to Read (DB_MODE_READ). A user has read access to objects such as tables, and cannot do update operations (INSERT, UPDATE, or DELETE, for example). This property defaults to a Mode property of Read/Write (DB_MODE_READ/WRITE).
Process binary as character	When this option is checked, the OLE DB Provider for DB2 treats binary data type fields (with a CCSID of 65535) 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. This property defaults to false.
Distributed transactions	When this option is checked, two-phase commit (distributed unit of work) is enabled. Distributed transactions are handled using Microsoft Transaction Server, Microsoft Distributed Transaction Coordinator, and the SNA LU 6.2 Resync Service. This option works only with DB2 for OS/390 V5R1 or later.

In the proposed scenario, the Host CCSID and PC Code Page would be selected from drop-down lists of supports character sets. The values for these parameters are only used if binary data fields with a CCSID value of 65535 on the DB2 system are to be treated as character data fields. In these cases, the Host CCSID selection should match the character set value used in the remote DB2 database for these binary data fields. Any PC Code Page can be selected that represents the preferred character set to be used on the SQL Server once the data has been moved and transformed. In most cases, these parameters are not used and data is converted based on the CCSID defined for each column in a DB2 table and default ANSI code page on the Windows system.

In the proposed scenario, the Read-only option should be checked to limit access to the DB2 tables to read access only. Since the DB2 data is to be used only for a data source, there is no need for read/write access. This read-only option can increase data access speed and protects the DB2 system from unintended changes.

The All Tab

The All tab allows users to configure essentially all of the properties for the data source except for the OLE DB Provider. The properties available in the All tab include properties that can be configured using the Connection and Advanced tabs as well as optional detailed properties used to connect to a data source.

Previous Next