BizTalk Adapter for DB2

Introduction

I recently used the BizTalk Adapter for DB2 on a project for a large Insurance company. A few interesting topics worth mentioning about this effort; connection parameters, error codes, and client side trace.

My experience involved calling DB2 stored procedures but they did not themselves use transactions. The stored procedures had been developed by the client using a COBOL CASE tool. So, this was basically a mechanism to call external COBOL routines that handled the business actual functions.

BizTalk Adapter for DB2

From MSDN (links at end):

The BizTalk Adapter for DB2 is a send and receive adapter that enables BizTalk orchestrations to interact with host systems. Specifically, the adapter enables send and receive operations over TCP/IP and APPC connections to DB2 databases running on mainframe, AS/400, and UDB platforms. Based on Host Integration Server technology, the adapter uses the Data Access Library to configure DB2 connections, and the Managed Provider for DB2 to issue SQL commands and stored procedures.

Connection Parameters

Some of the nomenclature when running the DB2 connection wizard can be a little confusing. Here is how it worked for this last project. The first two steps of the wizard are shown below. The third step is for the Username/Password and is not displayed.

Step One

• Address or alias: The AS400 server name.
• Port: Left as default 446.

Step Two

• Initial Catalog: Same as Address, as shown above
• Package collection: Client's default collection
• Default schema: Same as package collection
• Default qualifier: [blank]

 

Error Codes

The major key to understanding error codes returned from the DB2 adapter is that some errors are DB2 server generated, while some errors are provider specific; in this case Microsoft's DB2 managed provider. The SQL SATE codes are available in the exception for the stored procedure call. Depending on your implementation, an event log my also be found.

For example SQL STATE code "22001" represents a data truncation error. This can happen if the data passed to a stored procedure parameter is too wide. See the link at the end of the article for other codes.

A SQL STATE value of "HY000" represents the provider specific error. One example would be a connectivity problem.

Please see the links at the end of the article for further reading.

Tracing BizTalk DB2 Calls

A utility called SNATrace is installed with the host adapters on the BizTalk server. It provides ad-hoc logging of the connectivity with the AS/400. 

A brief blog from an actual team member of the DB2 Adapter product about using “snatrace” is provided below for further reading.

External Links from MSDN

• BizTalk Adapter for DB2
• Troubleshooting the OLE DB Provider for DB2
• DB2 Connection String Parameters
• Tracing a DB2 call
• Understanding a failure trace
• Package collections described

Other External Links

• IBM SQL STATE Error Status Code examples