The Windows Server 2003 and Windows 2000 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 SNA (APPC/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 2006 has the ability to trace DRDA data flows when used over TCP/IP.

This DB2 tracing capability is accessible from the DB2 Network Library 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 software development kit (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.
  
• 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.

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 Ddb2oledb.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 2006 CD 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 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 one 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 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, 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 2006 product CD 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

This particular error usually indicates a problem with the configuration parameters or the connection string passed.

Finally, your error code may be unique to the OLE DB Provider for DB2.