Export (0) Print
Expand All

Administration and Management of Data Access Using the OLE DB Provider for DB2

 

Host Integration Server 2000
Microsoft Corporation

October 2001

Summary: Microsoft Host Integration Server 2000 (HIS) includes a number of components that enable integration with host data sources. This article describes features and tools for administering and managing data access to IBM DB2 relational database systems using the OLE DB Provider for DB2 supplied with Microsoft Host Integration Server 2000. (33 printed pages)

Contents

Introduction
Configuring and Managing Data Sources
   Configuring Data Sources for the OLE DB Provider for DB2
   Creating New Data Links for the OLE DB Provider for DB2
   Browsing Data Sources for the OLE DB Provider for DB2
   Configuring Data Links for the OLE DB Provider for DB2
Creating Packages for Use With DB2
Host Security Integration
Troubleshooting Data Access

Introduction

Microsoft® Host Integration Server 2000 includes a rich set of Data Integration components, which provide desktop or server-based applications with direct access to host data. These Data Integration components provide a comprehensive set of data access services, which includes direct data access to relational and non-relational mainframe and AS/400 data through open database connectivity (ODBC), object linking and embedding database (OLE DB), and COM automation controls.

The Data Integration components included in Host Integration Server 2000 provide access to both structured and non-structured data stored on IBM mainframe or AS/400 computers. This data can be stored in a database or file system. In addition to data access, the Data Integration components also provide data transfer services between Microsoft® Windows® 2000 computers and host systems.

The Data Integration components can be organized into the following categories:

  • Relational database access
  • Record file access
  • File transfer
  • AS/400 data queue access

All of these services make use of IBM host-based products that implement the IBM Distributed Data Management Architecture (DDM). DDM is a framework or methodology for sharing and accessing data between systems. DDM defines the "how to communicate" and leaves it up to individual platform vendors to implement the DDM architecture. IBM currently supports DDM for most IBM platforms, including: OS/390 (MVS), AS/400, RS/6000 (AIX), and AS/36.

Much of the operational data stored on OS/390, AS/400, and RS/6000 computers is accessed via a relational database management system. The most popular database on these host systems is IBM DB2. Host Integration Server 2000 offers relational database access by using the Distributed Relational Data Architecture (DRDA) subset of DDM.

DRDA offers both Remote Unit of Work (RUW) and Distributed Unit of Work (DUW) access to host data. RUW is used for read-only and simple updating of database tables using SQL statements and stored procedures. DUW is used when updates span multiple DB2 instances or computer systems and supports the two-phase commit (2PC) protocol. The 2PC protocol ensures that changes to multiple databases will either succeed or fail in their entirety.

Host Integration Server 2000 implements access to DB2 via two features:

  • Microsoft OLE DB Provider for DB2
  • Microsoft ODBC Driver for DB2

The Microsoft OLE DB Provider for DB2 relies on an underlying DRDA application requester (AR) developed by Microsoft. The DRDA AR connects the OLE DB Provider for DB2 to DB2 on popular platforms, including OS/390, OS/400, RS/6000-AIX, Microsoft® Windows NT®, and Windows 2000.

The OLE DB Provider for DB2 supports a number of data access features including static and dynamic SQL, execution of DB2 stored procedures, transactions using two phase commit, and network connectivity using SNA LU 6.2 or TCP/IP. Developers can use Visual C or Microsoft Visual C++® to integrate DB2 data with Web-based and Windows-based applications. Microsoft Visual Basic® and Web developers (using scripting languages such VBScript) can use the higher-level Microsoft ActiveX® Data Objects (ADO) to develop e-commerce solutions. Additionally, DB2 is directly accessible from productivity applications, such as Microsoft Office 2000 using Visual Basic for Applications (VBA) and ADO from within Microsoft Excel.

This article describes features and tools for administering and managing data access to IBM DB2 relational database systems using the OLE DB Provider for DB2. Companion articles will discuss administration and management of other data integration components provided in Host Integration Server 2000.

Configuring and Managing Data Sources

Microsoft® Data Access Components 2.0 and later includes Data Links, a generic method for managing and loading connections to OLE DB data sources. Microsoft Data Links, a core element of the Microsoft Data Access Components (MDAC), provide a uniform method of creating persistent OLE DB data source object definitions stored in the form of universal data link (UDL) files. The OLE DB Provider for DB2 normally uses Data Links and UDL files for loading and configuring data sources.

Applications, such as the RowsetViewer sample from the Microsoft Data Access SDK, can open created UDL files and pass the stored initialization string to the OLE DB Provider for DB2 at run time. Data Links provide a flexible method for finding and saving connection information to OLE DB data sources.

In order to use Microsoft OLE DB Provider for DB2 with an OLE DB consumer application, the user must either (1) create a Microsoft data link (UDL) file and call this from the application, or (2) call the OLE DB provider from within the application using a connection string that includes the provider name and any other needed parameters.

Configuring Data Sources for the OLE DB Provider for DB2

Data source information must be configured for each DB2 system data source object that is to be accessed using the OLE DB Provider for DB2. The default parameters for the OLE DB Provider for DB2 are used as the default values for data sources and when these parameters are not configured for each data source.

Microsoft Data Links provides a uniform method for creating file-persistent OLE DB data source object definitions in the form of Universal Data Link (UDL) files. Applications, such as the RowsetViewer sample included in Microsoft Data Access and the Platform SDK, can open created UDL files and pass the stored initialization string to the OLE DB Provider for DB2 at run time.

Creating New Data Links for the OLE DB Provider for DB2

UDL files are normally stored in a special folder located at:

C:\Programs Files\Common Files\System\Ole DB\data links

Microsoft Data Access Components 2.5 introduced a set of new OLE DB interfaces and functions to enumerate, create, and modify data link UDL files for configuring data sources. The NewSnaDS.exe utility provided as part of the OLE DB Provider for DB2 enables users to create and modify data links. This tool makes calls to the OLE DB Service Component Manager that provides these functions.

To create a new UDL file, run the NewSnaDS tool. This tool is installed in the System folder below the subdirectory where Microsoft Host Integration Server 2000 is installed. The default location where this tool is installed is the following:

C:\Program Files\Host Integration Server\System\NewSnaDS.exe

A shortcut for this tool is added to the Programs menu under the Host Integration Server\Data Integration folder with a name of OLE DB Data Sources. This shortcut is created when Microsoft Host Integration Server 2000 software for the server or client (End-User Client or Administrator Client) is first installed and support for data access is selected.

A shortcut entitled the OLE DB Data Sources Browser is also added to the Programs menu in the Host Integration Server\Data Integration folder. This shortcut opens Windows Explorer to the default directory where UDL files are stored:

C:\Programs Files\Common Files\System\Ole DB\data links

Using SNA Server 4.0 and older versions of the Microsoft Data Access Components (MDAC 2.1), it was possible to create a new UDL file by navigating to this folder using Windows Explorer. In the right pane of Windows Explorer, right-click to open a shortcut menu and create a New Microsoft Data Link.

In the past, a data link file could also be created using SNA Server 4.0 with a shortcut in the SNA Server 4.0 program folder. Also, the properties of a data link file could be edited by opening the file from Windows Explorer. The procedures used with SNA Server 4.0 to create a new UDL file have been deprecated and will not work with Microsoft Host Integration Server 2000, Windows 2000, and MDAC 2.5 or later.

Once a UDL file has been created using the NewSnaDS tool, the file can be changed to a more appropriate name and copied to other client computers for use with the OLE DB Provider for DB2.

A new data link file can be created with the NewSnaDS utility using the following procedure:

  1. Click the Start button, point to Programs, and then point to Host Integration Server.
  2. Point to Data Integration, and then click OLE DB Data Sources to run the NewSnaDS tool.
    A UDL file is created, and the Data Link Properties dialog box is displayed.
  3. Select Microsoft OLE DB Provider for DB2 from the list of providers, and then configure the data source information as needed.
  4. Click OK to save the data link.

By default, data links are created in the following folder:

C:\Program Files\Common Files\System\Ole DB\data links

However, a data link can be created in this location and moved to other client computers or folders, as needed.

Browsing Data Sources for the OLE DB Provider for DB2

By default, data links are created in the following folder:

C:\Program Files\Common Files\System\Ole DB\data links

A shortcut is provided in the Host Integration Server program group to this folder.

  1. Click the Start button, point to Programs, and then point to Host Integration Server.
  2. Point to Data Integration, and then click OLE DB Data Source Browser.
    Windows Explorer opens in the default location where UDL files are stored. The list of data links saved in the default location appears.

Configuring Data Links for the OLE DB Provider for DB2

To edit the properties of a Data Link file, right-click the file using Windows Explorer and click Properties. The Properties dialog box appears with several property tabs:

  • General
  • Security
  • Summary
  • Provider
  • Connection
  • Advanced
  • All

The General, Security, and Summary tabs provide access to general file information for the UDL file that is available for other files and is not related to the Data Link properties. This information includes file location, file type, file size, file dates, file security permissions for access, and descriptive summary information (description and origin properties and values such title, subject, author, and so forth) for the UDL file. The Provider, Connection, Advanced, and All tabs provide access to the Data Link properties.

The NewSnaDS tool can also be used to open and modify an existing UDL file. The Data Link Properties dialog box appears with several property tabs:

  • Provider
  • Connection
  • Advanced
  • All

Provider

The Provider tab enables you to select the OLE DB provider (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.

Figure 1. The Provider tab

Connection

The Connection tab enables you to configure the basic properties required to connect to a data source.

Figure 2. The Connection tab

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 an optional parameter that can be used to describe the data source.

When the NewSnaDS configuration program is loaded from the Host Integration Server program folder, the Data source field is required. This field is used to name the UDL file, which is stored in C:\Program Files\Common Files\System\Ole DB\data links.

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

If TCP/IP Connection is selected, click the More Options … button to open a dialog box for configuring TCP/IP network settings. The parameters 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.

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 parameters 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 local and remote LU alias fields do not have default values. The default value for the APPC mode name normally defaults to QPCSUPP. The APPC mode can be selected from the drop-down list.

Figure 3. SNA Network Settings

Figure 4. TCP/IP Network Settings

The Data Source in OLE DB is similar to a Data Source Name (DSN) in ODBC. The data source information is stored in a Microsoft Data Links file and contains the connection information required for the OLE DB Provider for DB2 to access IBM Data Base 2.

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 grayed out and become inaccessible. The user name and password fields are set based on the Windows 2000 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 button 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 parameters 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.

For the Microsoft OLE DB Provider for DB2, the Connection tab includes the following 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 (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 the 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.

In SQL/DS (DB2/VM or DB2/VSE), this property is referred to as DBNAME.

If the provider supports changing the catalog for an initialized data source, the consumer can specify a different catalog name through the DBPROP_CURRENTCATALOG property in the DBPROPSET_DATASOURCE property set after initialization.

This is a required property.

This property is equivalent to the DBPROP_INIT_CATALOG OLE DB property ID.

Package collection This is 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, which is implemented as an IBM DRDA Application Requester, uses packages to issue dynamic and static SQL statements. Package names are not restricted and can be uppercase, lowercase, or mixed case.

The OLE DB Provider will create 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 specified when calling the OLE DB ITransactionLocal::StartTransaction or ITransactionJoin::JoinTransaction methods, as well as when setting the ADO IsolationLevel property on the Connection object.

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 (for example, 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 (see the later section on Creating Packages for Use with DB2).

The OLE DB Provider for DB2 ships with a tool 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 (see descriptions under the isoLevel parameter of the OLE DB ITransactionLocal::StartTransaction or ITransactionJoin::JoinTransaction methods, as well as the ADO IsolationLevel property in the Host Integration Server 2000 online Developer's Guide). The packages created are as follows:

AUTOCOMMITTED package (MSNC001 is only applicable on DB2/400)
READ UNCOMMITTED package (MSUR001)
READ COMMITTED package (MSCS001)
REPEATABLE READ package (MSRS001)
SERIALIZABLE package (MSRR001)

Note that the AUTOCOMMITTED package (MSNC001) is only created on DB2 for OS/400.

Once created, the packages are listed in the DB2 (mainframe) SYSIBM.SYSPACKAGE, the DB2 for OS/400 QSYS2.SYSPACKAGE, and the DB2 Universal Database (UDB) SYSIBM.SYSPACKAGE catalog tables.

Note that when upgrading from SNA Server 4.0, any existing SNA 4.0 packages must be recreated using the Host Integration Server CrtPkg utility to make them compatible with Host Integration Server 2000. The package names changed from SNA Server 4.0.

This property is equivalent to the DBPROP_DB2OLEDB_PACKAGECOL OLE DB property ID.

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, the Default schema is the target AUTHENTICATION (User ID or "owner").

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, the OLE DB Provider uses the USER_ID provided at login. For DB2/400, the driver will use QSYS2 if there is no collection found matching the USER_ID value. Obviously, this default value is inappropriate in many cases. Therefore it is essential that the Default schema value in the data source be defined.

This property is equivalent to the DBPROP_DB2OLEDB_CATALOGCOL OLE DB property ID.

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 this button is pressed, an APPC session or a TCP/IP session will attempt to be established with the host using the OLE DB Provider for DB2.

Advanced

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 select some specific options when using the OLE DB Provider for DB2.

Figure 5. The Advanced tab

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

Property Description
Host CCSID This is 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.

Note that Host CCSID 37 is not supported by the OLE DB Provider for DB2 when connecting to DB2 UDB for Windows NT or DB2 UDB for AIX.

This property defaults to U.S./Canada (37).

This property is equivalent to the DBPROP_DB2OLEDB_HOSTCCSID OLE DB property ID.

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).

This property is equivalent to the DBPROP_DB2OLEDB_PCCODEPAGE OLE DB property ID.

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).

This property is equivalent to the DBPROP_INIT_MODE OLE DB property ID.

Process binary as character When this option is checked (value is set to true), 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.

This property is equivalent to the DBPROP_DB2OLEDB_BINASCHAR OLE DB property ID.

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. This option also requires that an APPC Connection (the SNA LU 6.2 service) is selected as the Network transport in the Connection tab and Microsoft Transaction Server (MTS) is installed.

This property is equivalent to the DBPROP_DB2OLEDB_UNITSOFWORK OLE DB property ID.

All

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.

Figure 6. The All tab

The properties on the All tab may be edited by selecting a property from the list displayed and selecting Edit Value. This button will invoke a dialog box for the specific property containing a Property Description describing the property and a Property Value box for making changes.

Figure 7. Edit Property Value

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

Property Description
Alternate TP Name This is the remote transaction program name when used with SQL/DS. This property is only required when connecting to SQL/DS (DB2/VM or DB2/VSE).

This property is equivalent to the DBPROP_DB2OLEDB_TPNAME OLE DB property ID.

APPC Local LU Alias When an APPC Connection (SNA) using SNA LU 6.2 is selected for the Network Transport Library, this field is the name of the local LU alias configured in the SNA server.

This property is equivalent to the DBPROP_DB2OLEDB_LOCALLU OLE DB property ID.

APPC Mode Name When an APPC Connection (SNA) using SNA LU 6.2 is selected for the Network Transport Library, this field is the APPC mode and must be set to a value that matches the host configuration and SNA server configuration.

Legal values for the APPC mode name include QPCSUPP (common system default often used by 5250), #INTER (interactive), #INTERSC (interactive with minimal routing security), #BATCH (batch), #BATCHSC (batch with minimal routing security), #IBMRDB (DB2 remote database access), and custom modes. The following modes that support bi-directional LZ89 compression are also legal: #INTERC (interactive with compression), INTERCS (interactive with compression and minimal routing security), BATCHC (batch with compression), and BATCHCS (batch with compression and minimal routing security).

This property normally defaults to QPCSUPP.

This property is equivalent to the DBPROP_DB2OLEDB_APPCMODE OLE DB property ID.

APPC Remote LU Alias When an APPC Connection (SNA) using SNA LU 6.2 is selected for the Network Transport Library, this field is the name of the remote LU alias configured in the SNA server.

This property is equivalent to the DBPROP_DB2OLEDB_REMOTELU OLE DB property ID.

Cache Authentication This property determines whether the OLE DB Provider caches authentication information. This property defaults to false.

The value of this property (true or false) is selected from the drop-down list.

This property is equivalent to the DBPROP_CACHE_AUTHINFO OLE DB property ID.

Data Source The data source is an optional parameter that can be used to describe the data source.

This property does not have a default value.

Default Schema This is 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, the Default Schema is the target AUTHENTICATION (User ID or "owner").

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, the OLE DB Provider uses the USER_ID provided at login. For DB2/400, the driver will use QSYS2 if there is no collection found matching the USER_ID value. Obviously, this default is inappropriate in many cases; therefore, it is essential that the Default Schema value in the data source be defined.

This property is equivalent to the DBPROP_DB2OLEDB_CATALOGCOL OLE DB property ID.

Extended Properties This parameter is a string containing provider-specific, extended connection information. The use of this property implies that the OLE DB consumer knows how this string will be interpreted and used by the OLE DB provider. This property should be used only for provider-specific connection information that cannot be explicitly described through the other property parameters.

This property is equivalent to the DBPROP_INIT_PROVIDERSTRING OLE DB property ID.

Host CCSID This is 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.

Note that Host CCSID 37 is not supported by the OLE DB Provider for DB2 when connecting to DB2 UDB for Windows NT or DB2 UDB for AIX.

This property defaults to U.S./Canada (37).

This property is equivalent to the DBPROP_DB2OLEDB_HOSTCCSID OLE DB property ID.

Initial Catalog This OLE DB property is used as the first part of a 3-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 the 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.

In SQL/DS (DB2/VM or DB2/VSE), this property is referred to as DBNAME.

If the provider supports changing the catalog for an initialized data source, the consumer can specify a different catalog name through the DBPROP_CURRENTCATALOG property in the DBPROPSET_DATASOURCE property set after initialization.

This is a required property.

This property is equivalent to the DBPROP_INIT_CATALOG OLE DB property ID.

Integrated Security This property determines whether the OLE DB Provider uses Host Security Integration (single sign-on).

When this property is set to SSPI, single sign-on is enabled and separate user id and password parameters are not required. The user id and password parameters are set based on the Windows 2000 login.

When this property is null, this single sign-on feature is disabled.

This property defaults to null (host security integration is disabled) and a user id and password are required.

This property is equivalent to the DBPROP_AUTH_INTEGRATED OLE DB property ID.

Mode A Mode parameter is a bit mask specifying access permissions. This bit mask can be a combination of zero or more of the following:

DB_MODE_READ—Read-only.

DB_MODE_READWRITE—Read/write (DB_MODE_READ | DB_MODE_WRITE).

DB_MODE_SHARE_DENY_NONE—Neither read nor write access can be denied to others.

DB_MODE_SHARE_DENY_READ—Prevents others from opening in read mode.

DB_MODE_SHARE_DENY_WRITE—Prevents others from opening in write mode.

DB_MODE_SHARE_EXCLUSIVE—Prevents others from opening in read/write mode (DB_MODE_SHARE_DENY_READ | DB_MODE_SHARE_DENY_WRITE).

DB_MODE_WRITE—Write-only.

The following values for mode are supported by the OLE DB Provider for DB2: Read (DB_MODE_READ) and Read/Write (DB_MODE_READ/WRITE). This property defaults to Read/Write.

When the Read Only parameter is checked in the Advanced tab, the OLE DB Provider for DB2 creates a read-only data source by setting the Mode parameter 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 is equivalent to the DBPROP_INIT_MODE OLE DB property ID.

Network Address When TCP/IP has been selected for the Network Transport Library, this property indicates the IP address of the DB2 host or a hostname alias for this computer.

This property is equivalent to the DBPROP_DB2OLEDB_NETADDRESS OLE DB property ID.

Network Port When TCP/IP has been selected for the Network Transport Library, this property is the TCP/IP port used for communication with the DB2 host. The default value is TCP/IP port 446.

This property is equivalent to the DBPROP_DB2OLEDB_NETPORT OLE DB property ID.

Network Transport Library The network transport dynamic link library property designates whether the OLE DB Provider for DB2 connects via an APPC Connection using SNA LU6.2 or TCP/IP Connection. The possible values for this property are TCP/IP or SNA.

The default value for this property is SNA.

If the default SNA is selected, values for APPC Local LU Alias, APPC Mode Name, and APPC Remote LU Alias are required.

If TCP/IP is selected, values for Network Address and Network Port are required.

This property is equivalent to the DBPROP_DB2OLEDB_NETTYPE OLE DB property ID.

Package Collection This is 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, which is implemented as an IBM DRDA Application Requester, uses packages to issue dynamic and static SQL statements. Package names are not restricted and can be uppercase, lowercase, or mixed case.

The OLE DB Provider will create 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 specified when calling the OLE DB ITransactionLocal::StartTransaction or ITransactionJoin::JoinTransaction methods, as well as when setting the ADO IsolationLevel property on the Connection object.

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 (for example, 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 (see the later section on Creating Packages for Use with DB2).

The OLE DB Provider for DB2 ships with a tool 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 (see descriptions under the isoLevel parameter of the OLE DB ITransactionLocal::StartTransaction or ITransactionJoin::JoinTransaction methods, as well as the ADO IsolationLevel property in the Host Integration Server 2000 online Developer's Guide). The packages created are as follows:

AUTOCOMMITTED package (MSNC001 is only applicable on DB2/400)
READ UNCOMMITTED package (MSUR001)
READ COMMITTED package (MSCS001)
REPEATABLE READ package (MSRS001)
SERIALIZABLE package (MSRR001)

Note that the AUTOCOMMITTED package (MSNC001) is only created on DB2 for OS/400.

Once created, the packages are listed in the DB2 (mainframe) SYSIBM.SYSPACKAGE, the DB2 for OS/400 QSYS2.SYSPACKAGE, and the DB2 Universal Database (UDB) SYSIBM.SYSPACKAGE catalog tables.

Note that when upgrading from SNA Server 4.0, any existing SNA 4.0 packages must be recreated using the Host Integration Server CrtPkg utility to make them compatible with Host Integration Server 2000. The package names changed from SNA Server 4.0.

This property is equivalent to the DBPROP_DB2OLEDB_PACKAGECOL OLE DB property ID.

Password A valid user name and password are normally required to access data sources on hosts. The password is case sensitive and is displayed as asterisks in this dialog box for security purposes.

This property is equivalent to the DBPROP_AUTH_PASSWORD OLE DB property ID.

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 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).

This property is equivalent to the DBPROP_DB2OLEDB_PCCODEPAGE OLE DB property ID.

Persist Security Info This parameter indicates whether the data source object is allowed to persist sensitive authentication information, such as a password along with other authentication information. This property defaults to false.

The value of this property (true or false) is selected from the drop-down list.

This property is equivalent to the DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO OLE DB property ID.

Process Binary as Character When this property is set to true, 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.

The value of this property (true or false) is selected from the drop-down list.

This property is equivalent to the DBPROP_DB2OLEDB_BINASCHAR OLE DB property ID.

Units of Work This property indicates whether two-phase commit (distributed unit of work) used for transactions is supported for this data source. Distributed transactions are handled using Microsoft Transaction Server, Microsoft Distributed Transaction Coordinator, and the SNA LU 6.2 Resync Service.

The following values for this property are supported by the OLE DB Provider for DB2:

RUW (remote unit of work)

DUW (distributed unit of work)

Distributed unit of work (two-phase commit) works only with DB2 for OS/390 v5R1 or later. This option also requires that the SNA LU 6.2 service is selected as the network transport and Microsoft Transaction Server (MTS) is installed.

This property defaults to RUW.

The value of this property (RUW or DUW) is selected from the drop-down list.

This property is equivalent to the DBPROP_DB2OLEDB_UNITSOFWORK OLE DB property ID.

User ID A valid User name is normally required to access data sources on hosts. This value is case sensitive.

This property is equivalent to the DBPROP_AUTH_USERID OLE DB property ID.

Creating Packages for Use With DB2

The OLE DB Provider for DB2, which is implemented as an IBM Distributed Relational Database Architecture (DRDA) Application Requester, uses packages to issue SQL statements and call DB2 stored procedures. There is a provider-specific property that the OLE DB Provider for DB2 uses to identify a location in which to create and store DB2 packages. The OLE DB Provider for DB2 will create packages dynamically in the location to which the user points using the Package Collection property corresponding to the DBPROP_DB2OLEDB_PACKAGECOL property ID of OLE DB. This location may be configured using the Connection and All tabs using Microsoft Data Links or can be passed as part of the connection string as an attribute keyword and argument. This attribute keyword can be either pkgcol or the long form of this attribute, Package Collection.

There are two package creation options:

  1. The OLE DB Provider for DB2 will auto-create one package for the currently-used isolation level at run time if no package already exists. This auto-create process may fail if the user account does not have authority to create packages.
  2. An administrator or user can manually creates all four packages (five packages on DB2/400) for use with all isolation levels and for use by all users (PUBLIC). The OLE DB Provider for DB2 includes a utility program for use by users with appropriate administrative privilege for this purpose.

However, some users may not have the security level when manually creating packages to GRANT authority to the packages to other users (PUBLIC, for example). This can be a problem if two or more users with different user IDs try to access a single collection of packages. The first user that created the packages will have access to the packages, but the second user likely will not. The Host Integration Server 2000 CD-ROM includes a program for use by an administrator to create packages. This tool can be run using a privileged User ID to create packages in collections accessed by multiple users. The Create Packages for DB2 utility, CrtPkg.exe, is a GUI-based tool included with Host Integration Server 2000 for creating packages for use with DB2. This tool is installed in the System folder below the subdirectory where the Microsoft Host Integration Server 2000 has been installed. The default location where this tool is installed is the following:

C:\Program Files\Host Integration Server\system\CrtPkg.exe

A shortcut for this tool is added to the Programs menu off the Start button on the Windows Taskbar under the Host Integration Server\Data Integration folder with a name of Packages for DB2. This shortcut is created when Microsoft Host Integration Server 2000 software for the server or client (End-User Client or Administrator Client) is first installed and support for Data Access is selected.

This tool will create a set of packages and grant EXECUTE privilege to PUBLIC for all:

  • AUTOCOMMITTED package (MSNC001) is only applicable on DB2/400
  • READ UNCOMMITTED package (MSUR001)
  • READ COMMITTED package (MSCS001)
  • REPEATABLE READ package (MSRS001)
  • SERIALIZABLE package (MSRR001)

Note that the AUTOCOMMITTED package (MSNNC001) is only created on DB2 for OS/400.

The descriptive process name used by the CrtPkg utility of each package corresponds with the isolation levels defined in the ANSI SQL standard. The table below indicates how these packages correspond with the terms used by IBM for isolation levels in DB2 documentation.

Package Description Package Name IBM Documentation
AUTOCOMMITTED (Note that this applies only to DB2/400 and does not correspond with an ANSI SQL isolation level.) MSNC001 COMMIT(*NONE) (NC).

This isolation level is used in DB2/400 auto-commit mode only and has no corresponding isolation level on other DB2 platforms or in ANSI SQL.

READ UNCOMMITTED MSUR001 UNCOMMITTED READ (UR).

This isolation level corresponds with ANSI SQL READ UNCOMMITTED.

READ COMMITTED MSCS001 CURSOR STABILITY (CS).

This isolation level corresponds with ANSI SQL READ COMMITTED.

REPEATABLE READ MSRS001 READ STABILITY (RS).

This isolation level corresponds with ANSI SQL REPEATABLE READ.

SERIALIZABLE MSRR001 REPEATABLE READ (RR).

This isolation level corresponds with ANSI SQL SERIALIZABLE.

These Isolation Levels are described in detail under "Support for Isolation Levels Using the OLE DB Provider for DB2" in the Host Integration Server 2000 online Developer's Guide. These Isolation Levels are also described under the OLE DB isoLevel parameter and ADO IsolationLevel property in the Host Integration Server 2000 online Developer's Guide. Note that the AUTOCOMMITTED package (MSNC001) is only created on DB2 for OS/400.

Note that when upgrading from SNA Server 4.0, any existing SNA 4.0 packages must be recreated using the Host Integration Server CrtPkg utility to make them compatible with Host Integration Server 2000. The package names used by the OLE DB Driver for DB2 on SNA Server 4.0 are not compatible with the OLE DB Driver for DB2 included with Host Integration Server. On SNA Server 4.0, these packages used different names as follows:

AUTOCOMMITTED package (SNANC001) only applicable on DB2/400
READ UNCOMMITTED package (SNACH001)
READ COMMITTED package (SNACS001)
REPEATABLE READ package (SNARR001)
SERIALIZABLE package (SNAAL001)

The CrtPkg utility will create all of these packages inside the Collection that is specified in the Package Collection property in the data link file, or in the connection string. If the user does not have the appropriate authority to create packages in the specified Collection, or if the specified Collection does not exist, the OLE DB Provider for DB2 will return an error.

In the case of DB2 on MVS or OS/390, the normal error text returned if the user does not have the appropriate authority would be as follows:

A SQL error has occurred. Please consult the documentation for your 
   specific DB2 version for a description of the associated Native 
      Error and SQL State. SQLSTATE: 51002, SQLCODE: -567. 

In the case of DB2/400, the normal error text returned if the user does not have the appropriate authority would be as follows:

A SQL error has occurred. Please consult the documentation for your 
   specific DB2 version for a description of the associated Native 
      Error and SQL State. SQLSTATE: 51002, SQLCODE: -805. 

In the case of DB2/400, the normal error returned if the collection does not exist would be as follows:

Failed to create AUTOCOMMITTED (NC) package.  RETCODE=-99.
SQL Error: Code=-204, State=42704, Error Text= A SQL error has occurred.
   Please consult the documentation for your specific DB2 version for a 
   description of the associated Native Error and SQL State. 
      SQLSTATE: 42704, SQLCODE: -204

There are two authorities required to execute the create package process on MVS using the CrtPkg utility:

GRANT BINDADD TO <authorization ID>
GRANT CREATE IN COLLECTION <collection ID> TO <authorization ID>

The "authorization ID" is the user who needs the permission to create the packages. The "collection ID" is the name of the Collection, which the user specifies in the data link file for the Package Collection property. This Collection should be a valid Collection within the DB2.

If an administrator executes the above statements on behalf a non-privileged user, this non-privileged user can then run the CrtPkg utility. Once run, the CrtPkg process will create four sets of packages (one for each of the four isolation levels supported on DB2 for MVS or OS/390) for use by "all" (PUBLIC) users of the Microsoft data access features.

The example below illustrates this process on DB2 for MVS or DB2 for OS/390.

Grant rights to run the CrtPkg utility to authorization ID WNW999.

GRANT BINDADD TO WNW999
GRANT CREATE IN COLLECTION MSPKG TO WNW999

Run the CrtPkg utility using authorization ID WNW999 (see output from CrtPkg below).

Beginning creation process
Initializing environment...
Connecting to the host...
Connection established.
Start package creation process...
Creating READ UNCOMMITTED package...
READ UNCOMMITTED package created.
Package creation succeeded.
EXECUTE privilege on MSUR001            granted to PUBLIC
Creating READ COMMITTED package...
READ COMMITTED package created.
Package creation succeeded.
EXECUTE privilege on MSCS001            granted to PUBLIC
Creating REPEATABLE READ package...
REPEATABLE READ package created.
Package creation succeeded.
EXECUTE privilege on MSRS001            granted to PUBLIC
Creating SERIALIZABLE package...
SERIALIZABLE package created.
Package creation succeeded.
EXECUTE privilege on MSRR001            granted to PUBLIC
Free statement handles...
Disconnecting...
Disconnected
End of package creation.
Creation process has completed

In order to execute the CrtPkg utility on DB2/400, a user ID must have one of the following authorities:

  • *CHANGE authority on the DB2 collection
  • *ALL authority on the DB2 collection

If the user merely has *USE authority or if the user has *EXCLUDE authority, the Create Package process will fail.

There are several steps required to change user authority on a DB2/400 collection (AS/400 library): From interactive SQL (STRSQL command) while logged in as user with administrative privileges, create a new collection. This command can also be issued using ADO, OLE DB, and ODBC. However, most administrators typically create collections from the AS/400 console since the administrator must be logged in at the console to issue the Command Language (CL) command with which to change the user authority on the collection.

CREATE COLLECTION <collection ID>

From the AS/400 command console, issue the CL WRKOBJ command with the <collection ID> as a parameter.

WRKOBJ <collection ID>

The "collection ID" is the name of the Collection, which the user specifies in the data link file for the Package Collection property. This Collection should be a valid Collection within DB2. The Work with objects screen appears. Place the cursor on the *PUBLIC Object Authority line and change the authority from *USE to *ALL.

If an administrator executes the above statements on behalf a non-privileged user, this non-privileged user can then run the CrtPkg utility. Once run, the CrtPkg process will create five sets of packages (one for each of the five isolation levels supported on DB2/400) for use by "all" (PUBLIC) users of the Microsoft data access features. On DB2/400, five packages are created including the AUTOCOMMITTED packages.

The example below illustrates this process on DB2/400.

Grant rights to run the CrtPkg utility to authorization ID WNW999.

CREATE COLLECTION MSPKG
WRKOBJ MSPKG

Run the CrtPkg utility (see the output from CrtPkg for DB2/400 below).

Beginning creation process
Initializing environment...
Connecting to the host...
Connection established.
Start package creation process...
Creating AUTOCOMMITTED (NC) package...
AUTOCOMMITTED (NC) package created.
Package creation succeeded.
EXECUTE privilege on MSNC001            granted to PUBLIC
Creating READ UNCOMMITTED package...
READ UNCOMMITTED package created.
Package creation succeeded.
EXECUTE privilege on MSUR001            granted to PUBLIC
Creating READ COMMITTED package...
READ COMMITTED package created.
Package creation succeeded.
EXECUTE privilege on MSCS001            granted to PUBLIC
Creating REPEATABLE READ package...
REPEATABLE READ package created.
Package creation succeeded.
EXECUTE privilege on MSRS001            granted to PUBLIC
Creating SERIALIZABLE package...
SERIALIZABLE package created.
Package creation succeeded.
EXECUTE privilege on MSRR001            granted to PUBLIC
Free statement handles...
Disconnecting...
Disconnected
End of package creation.
Creation process has completed

CrtPkg allows a user to create a new UDL file or load a data source and modify an existing UDL file for connection configuration information. The File menu of CrtPkg has a New option used for creating a new OLE DB UDL File and a Load Data Source option to load an existing UDL file. The File menu Edit Data Source option allows a user to access and modify the properties for a data source similar to using the NewSnaDS.exe tool. The Run menu option is used to create packages.

When using the create package tool, if the package collection specified does not exist, then DB2 returns SQLCODE -805.

When using auto-create packages, if a package collection is not specified or the package collection does not exist, the consumer application will receive SQLSTATE HY000 and SQLCODE -385 during the "auto-create" package process. The SQLSTATE HY000 is defined as a provider-specific error. The -385 Error Return Code is not a SQLCODE but rather a DDM DRDA AR (DB2 client) return code. This error code is defined as DDM_VALNSPRM with the following associated text string:

"The parameter value is not supported by the target system." 

The OLE DB Provider for DB2 client error codes are defined in the db2oledb.h file located on the Host Integration Server 2000 CD-ROM.

Note that when upgrading from SNA Server 4.0, any existing SNA 4.0 packages must be recreated using the Host Integration Server CrtPkg utility to make them compatible with Host Integration Server 2000.

SNA Server 4.0 with Service Pack 3 came with two similar utilities for creating packages: CRTPKG.EXE (a command-line tool) and CRTPKGW.EXE (a GUI-based tool).

Host Security Integration

An Integrated Security (single sign-on) feature is supported by Host Integration Server 2000 to automate the overall logon process. When configured for this feature, Host Integration Server 2000 automatically replaces special keywords in the data stream with the actual host user name and password at appropriate points in the session. This feature must be enabled by the administrator within a Host Integration Server 2000 subdomain and special strings must be entered for the user name (MS$SAME) and password (MS$SAME) that will be replaced.

When using the OLE DB Provider for DB2, this single sign-on feature works only when an APPC Connection using SNA LU 6.2 is used for the network transport. This feature is enabled under the Connection or All tabs when configuring a data source for use with the OLE DB Provider for DB2.

Troubleshooting Data Access

The Windows 2000 and Windows NT Event Viewer can be a useful tool for troubleshooting data access in some cases. The OLE DB Provider for DB2 does not issue events. However, when an APPC Connection using SNA LU 6.2 is used for the network transport for the OLE DB Provider for DB2, the low-level SNA APPC transport issues events on the SNA connection.

The Microsoft® OLE DB Provider for DB2 supplied with Host Integration Server 2000 has the ability to trace DRDA data flows when used over TCP/IP.

This DB2 tracing capability is accessible from the SNADB2 Service tracing inside the Trace utility. This facility will show the same data as an APPC trace but without the control indicators (for example, What_Received). Socket errors are traced and the error codes can be looked up in Winsock2.h supplied with the Platform SDK.

The OLE DB Provider for DB2 can return the following types of errors:

  • DB2 SQL errors from the remote database
  • Microsoft OLE DB Provider-specific errors
  • Errors from the underlying DRDA Application Requester network client

When the OLE DB Provider for DB2 passes an error code, the best source in which to look up the meaning of the return code is often the SQL Reference or SQL Messages and Codes Reference for the target SQL database. In this case, the target database would be one of the DB2 platforms supported by the Microsoft OLE DB Provider for DB2.

The OLE DB Provider for DB2 maintains an internal integer variable named SQLCODE and an internal 5-byte character string variable named SQLSTATE used to check the execution of SQL statements on DB2. SQLCODE is set by DB2 after each SQL statement is executed. DB2 returns the following values for SQLCODE:

  • If SQLCODE = 0, execution was successful.
  • If SQLCODE > 0, execution was successful with a warning.
  • If SQLCODE < 0, execution was not successful.
  • If SQLCODE = 100, "no data" was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

SQLSTATE is also set by DB2 after the execution of each SQL statement. Application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE. SQLSTATE provides application programs with common codes for common error conditions (the values of SQLSTATE are product-specific only if the error or warning is product-specific). Furthermore, SQLSTATE is designed so that application programs can test for specific errors or classes of errors.

SQLSTATE values consist of a two-character class code value, followed by a three-character subclass code value. The first character of an SQLSTATE value indicates whether the SQL statement was executed successfully or unsuccessfully (equal to or not equal to zero, respectively). Class code values represent classes of successful and unsuccessful execution conditions. The following SQLSTATE class codes are used by DB2:

Class Code Description of Error Class
00 Successful completion. Execution of the SQL statement was successful and did not result in any type of warning or exception condition.
01 Warning
02 No data
07 Dynamic SQL error
08 Connection exception
0A Feature not supported
0F Invalid token
21 Cardinality violation
22 Data exception
23 Constraint violation
24 Invalid cursor state
25 Invalid Transaction State
26 Invalid SQL statement identifier
2D Invalid transaction termination
34 Invalid cursor name
39 External function call exception
40 Transaction rollback
42 Syntax error or access rule violation
44 WITH CHECK OPTION violation
51 Invalid application state
53 Invalid operand or inconsistent specification
54 SQL or product limit exceeded
55 Object not in prerequisite state
56 Miscellaneous SQL or product error
57 Resource not available or operator intervention
58 System error

The SQLSTATE value of HY000 is defined as a provider-specific error. An SQLSTATE of 08S01 (connection exception with a subclass code of S01) also indicates a provider-specific error. This means the SQLCODE should be looked up in the driver-specific documentation included with the OLE DB Provider for DB2.

If the SQLSTATE does not indicate a driver-specific error when the OLE DB Provider for DB2 passes back an SQLSTATE of 08S01, it indicates a network error. For example, an SQLCODE of -603 is a provider-specific error that is mapped to DB2OLEDB_COMM_HOST_CONNECT_FAILED in the db2oledb.h include file supplied with the OLE DB Provider for DB2. Errors with an SQLSTATE of 08S01 are documented in the db2oledb.h include file (the SQLCODE value) which is located on the Host Integration Server 2000 CD-ROM in the SDK\Include subdirectory.

The following steps are useful in researching an error. Start by reading the provided error text returned by the OLE DB Provider for DB2. In some cases, the error text provides very limited useful information. For example, error text from an SQLCODE of -603 states the following:

Test connection failed because of an error in initializing provider.
Could not connect to specified host.

The next step is to lookup the SQLSTATE to determine the source of the error. Is the error a DB2 error, a network client error, or an OLE DB Provider error? An SQLSTATE of 08S01 is defined as follows:

Communication link failure.

This definition is intended to inform the user, administrator, or developer that the error is related to the OLE DB Provider's underlying network client.

Unfortunately, many of the SQLSTATE codes returned by the OLE DB Provider for DB2 are DB2 errors and are not documented in the OLE DB Provider for DB2 on-line help.

The SQLSTATE of HY000 is defined as a provider-specific error. An SQLSTATE of 08S01 also indicates a provider-specific error. This means the SQLCODE should be looked up in the provider-specific documentation included with the OLE DB Provider for DB2.

If the SQLSTATE does not indicate a driver-specific error, then the SQLCODE should be looked up in the appropriate DB2 manual for the target platform. For example, an SQLCODE of -603 is documented in Appendix B, SQLCODEs and SQLSTATEs, in the AS/400 Advanced Series DB2 for AS/400 SQL Programming, Version 4, Document Number SC41-5611-00 published by IBM. An SQLCODE of -603 corresponds to SQLSTATE 23515 in the DB2 for OS/400 error code list. For example, the explanation for this SQLCODE is as follows:

Unique index cannot be created because of duplicate keys.

When the SQLSTATE and the SQLCODE definitions documented in these appendices create a mismatch with the actual errors returned, this usually indicates a provider-specific error condition.

A final step to understand an error is to check the db2oledb.h file. This file is not installed by the Host Integration Server or Host Integration Client setup program, but can be found on the Host Integration Server 2000 product CD ROM in the SDK\Include subdirectory. An SQLCODE (for example, -603) can be looked up by searching the right-most column of the db2oledb.h file for a value near to 603. In this case, one will see a comment "/* -600 */" and can then count down three additional lines to line number 603. The internal error code -603 is defined as follows:

DB2OLEDB_COMM_HOST_CONNECT_FAILED

Unfortunately, this error text is not further defined anywhere in the software or documentation provided to the customer. This particular error usually indicates a problem with the configuration parameters or the connection string passed.

Show:
© 2015 Microsoft