Setting up the Biztalk Adapter for DB2 Using Receive Ports

Setting Up the BizTalk Adapter for DB2 Using Receive Ports

Microsoft Corporation

Published: May 2007

Author: Charles Ezzell

Summary

The purpose of this document is to show how to configure Microsoft® BizTalk® Server2006 with the BizTalk Adapter for DB2. Specifically, the document addresses how to use receive ports together with send ports, and how to use receive ports to receive and optionally update data on the DB2 database.

Setting Up the BizTalk Adapter for DB2 Using Receive Ports

Overview

Setting Up the Data Connection

What Are Updategrams?

Insert

Delete

Update

Stored Procedures

SELECT Statements

Converting Updategrams to a SQL Statement

Data Value Conversion

ResultSet Messages

BizTalk Configuration

Create a Send Port in BizTalk Server

Create a Second Send Port

Create a Third Send Port

Create a Receive Port (One-Way)

Create a Second Receive Port

Defining the BizTalk Schemas by Using Visual Studio

Test 1 – Inserting by Using a Send Port; Receive Returns select *

Test 2 – Creating an Update Command on the Receive Location

Test 3 – Creating A Delete Command on the Receive Location

Test 4 – Calling a Stored Procedure That Returns a Result Set on the Receive Location

Data Flow Charts

Test 1

Test 2

Test 3

Test 4

Conclusion

Setting Up the BizTalk Adapter for DB2 Using Receive Ports

Summary: The purpose of this document is to show how to configure Microsoft® BizTalk® Server2006 with the BizTalk Adapter for DB2. Specifically, the document addresses how to use receive ports together with send ports, and how to use receive ports to receive and optionally update data on the DB2 database.

Overview

This document contains the following sections:

Setting Up the Data Connection

What Are Updategrams?

BizTalk Configuration

Defining the BizTalk Schemas by Using Visual Studio

Data Flow Charts

The information in this document is a general introduction to the previous topics. It is not meant to be an in-depth resource for creating BizTalk applications. Rather, it is intended to help with basic configuration and setup.

In most cases, you should start with a basic project such as the one presented in this document, and then build on that as necessary. One indication that everything is going as designed is if the samples work for you.

Setting Up the Data Connection

To begin with, verify that you can connect to your DB2 database using the Microsoft OLE DB Provider for DB2. The best and quickest way to do this is to use the Data Access Tool.

The following example demonstrates connecting to an AS/400 called CONTOSO.

To connect to an AS/400

1.Click Start, point to All Programs, point to Microsoft BizTalk Adapters for Host Systems, and then click Data Access Tool.
2.Create a DB2 OLE DB UDL to point to the host. To do this, on the File menu, click New, and then click Data Source. This begins the wizard.

3.On the Data Source Wizard screen, click Next.
4.Select DB2/AS400 for Data source platform, select TCP/IP for Network type, and then click Next.

5.On the TCP/IP Network Connection screen, enter CONTOSO (or any DB2 host) in the Address or alias box.

6.The default port for AS/400 is 446. If your port is different, change this field, and then click Next. This brings up the DB2Database screen.

Settings on this page will depend on your DB2 system. For DB2/400, the initial catalog is the RDBNAME, which is the database name of the remote computer. (To find this, use the WRKRDBDIRE command from an AS/400 console.) For DB2 (MVS, OS/390), this is referred to as LOCATION. For DB2/UDB, this property is referred to as DATABASE. For more information about these properties, click Help at the bottom of the page.
7.Fill in the appropriate values. In the preceding screen, the RDBNAME is S10D823B, the package collection is the name of the AS/400 library (CNWIND, in this case), and the default schema is also set to CNWIND (target collection).
Note
If the database file is in a different library than indicated in the default schema, the default qualifier field should point to the library that hosts the file.
8.Click Next.
9.Set the appropriate values for Host CCSID and PC code page, and then click Next.

10.Enter the security method that you wish to use, and fill in the appropriate fields. In this example, the security method is Interactive sign-on, and the password will be saved to the UDL, which will be created later in this wizard.
Note
These fields are case-sensitive.
11.Once you have filled in the information for your security method, click Next.

12.On the Advanced Options screen, for most purposes when using the adapter, you can leave the options as cleared, and then click Next.

The Validation screen appears. If you click Connect, the DB2 provider verifies whether it can do a basic connection (for instance, no password required) to the DB2 system.
13.Check for a successful connection by looking for output similar to this:
Successfully connected to data source 'New Data Source'.
Server class: DB2/400
Server version: 05.04.0000
14. Click Sample Query. This should return the tables on the host from the information that you have provided in previous steps.

15.Click Packages.
This creates DB2 packages that are required for executing SQL statements. Creating packages on DB2 requires authority to CREATE, BIND, and GRANT privileges to PUBLIC. If you do not have sufficient authority (with the user ID/password given earlier in the Security screen), contact the database administrator.
When this is run on AS/400, you should end up with output similar to this:
Connected to data source 'New Data Source'.
AUTOCOMMITTED package has been created.
READ COMMITTED package has been created.
READ UNCOMMITTED package has been created.
SERIALIZABLE package has been created.
REPEATABLE READ package has been created.
The package creation process has completed successfully.
16.After performing the preceding action in the Validation screen, click Next to continue to the Saving Information screen.
17.In the Data source name text box, type the name, and then select the format in which you want to save the data source information. For purposes of this example, select Universal data link, and then click Next.

18.Click Finish.

The UDL used for this example is:
Provider=DB2OLEDB;User ID=DON;Password=don;Initial Catalog=S10D823B;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=CONTOSO;Network Port=446;Package Collection=CNWIND;Default Schema=CNWIND;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False;
For more information about the parameters in the preceding data provider string, see the Microsoft BizTalk Adapters for Host Systems documentation. To view the documentation, click Start, point to All Programs, point to Microsoft BizTalk Adapters for Host Systems, and then click Documentation.
The parameters are documented under the MsDb2ConnectionStringBuilder Members.

What Are Updategrams?

Updategrams are a format of an XML message. They contain information about how to make changes to data in a database table. Updategrams are common in Microsoft SQL Server™ because of their simple XML schema and native support.

DB2 does not contain a native parser for updategram messages, so the DB2 adapter needs to parse the content of the XML updategram. The output of the parse is a series of INSERT, DELETE, and UPDATE statements.

Updategrams have the advantage of being simple to use in BizTalk Mapper, and they do not rely on any heavyweight DataSet XML parsing routines that are used with diffgrams.

The remainder of this section discusses insert, delete, and update messages, in addition to various mechanics of updategrams.

Insert

An insert message contains elements under the after node, but not in the before node.

Delete

A delete message contains elements under the before node, but not in the after node.

Update

An update message contains elements under both the before and after nodes; the original value is stored under the before node and the new values are stored under the after node:

Stored Procedures

Updategrams, as defined in SQL Server, do not contain any syntax for executing stored procedures. The updategram syntax that the DB2 adapter uses contains an extension for executing stored procedures with parameters:

The updategram for the stored procedure does not contain before or after elements; it only lists the stored procedure to execute as the element name. Parameters are listed as attributes in the order that they should be added to the stored procedure.

SELECT Statements

Updategrams, as defined in SQL Server, do not contain any syntax for executing SELECT statements. The updategram syntax used by the DB2 adapter will contain an extension for executing SELECT statements:

<InboundRootElementName>
<sync>
<Select>SELECT * FROM ORDERS WHERE STATUS = ‘New’</Select>
</sync>
</InboundRootElementName>

The updategram for the SELECT statement does not contain before or after elements. It only lists the SELECT statement to be executed as the element name.

Converting Updategrams to a SQL Statement

When an insert message is given to the DB2 adapter to be sent, the contents are translated into a SQL statement. This SQL statement uses all of the available information that is given in the message body: the table name, the column names, and their values.

As an example, this insert updategram:

translates to this SQL statement:

INSERT INTO Orders (id, status) values (?, ?)

The adapter uses parameters to avoid SQL injection attacks. However, using parameters has a cost in that the data type of the parameters must be discovered for each SQL statement using the MsDb2CommandBuilder.DeriveParameters() method. This uses the Distributed Relational Database Architecture Application Requestor's (DRDA AR) DESCRIBE statement functionality to determine each parameter’s data type, size, precision, and scale. The adapter converts each string value from the updategram into the parameter’s type. The command is then executed.

DELETE statements work similarly to INSERT statements during conversion, except that the column values are used in a WHERE clause.

<InboundRootElementName>
<sync>
<before>
<Orders id='55' status='Active' />
</before>
<after/>
</sync>
</InboundRootElementName>
DELETE FROM Orders WHERE id = ? AND status = ?

UPDATE statements use the before values of the updategram in a WHERE clause and the after values in the SET clause:

<InboundRootElementName>
<sync>
<before>
<Orders id='55' status='Active' />
</before>
<after>
<Orders id='55' status='Filled' />
</after>
</sync>
</InboundRootElementName>
UPDATE Orders SET id = ?, status = ? WHERE id = ? AND status = ?

The stored procedure extension also uses the MsDb2CommandBuilder.DeriveParameters() method to find the data types of stored procedure parameters for conversion:

<InboundRootElementName>
<sync>
<StoredProcedure>
<AddOrder id=’55’ status=’New’/>
</StoredProcedure>
</sync>
</InboundRootElementName>
CALL AddOrder (?, ?)

Data Value Conversion

When an updategram is converted to a SQL statement with parameters, data conversion must take place from a string type to the parameter data type. The following chart illustrates these conversions.

MsDb2Type / .NET Data Type Conversion
BigInt / Int64.TryParse()
Int / Int32.TryParse()
SmallInt
Bit
TinyInt / Int16.TryParse()
Timestamp
Date / DateTime.TryParse()
Time / TimeSpan.TryParse()
Double / Double.TryParse()
Real / Single.TryParse()
Decimal
Numeric / Decimal.TryParse()
Binary
CharForBit
VarBinary VarCharForBit / Byte[] conversion (hex values)
Char
WideChar
VarChar
Graphic
VarGraphic
VarWideGraphic / String

When the adapter receives a result set from DB2, the data is converted to an XML message by using the column value’s ToString() method. This uses the current culture to translate to a string.

When converting from xsd:dateTime to CLS DateTime, the TryParse method works successfully.

ResultSet Messages

Result set data is returned to BizTalk Server by using a message that contains a row of data. The schema for this message is similar to an updategram, except that the sync, before, and after elements are removed:

The user specifies a single root element name (OutboundRootElementName). The table name is used as the nested element name. Column names are attributes, with each data value as the column name attribute value.

BizTalk Configuration

The example in this section illustrates how to create a BizTalk application for multiple scenarios. Specifically, the example shows how to:

Create a send port for sending records to the DB2 database.

Define a receive location to poll data from the DB2 database.

Connect the receive location and send port to set up an update-and-retrieve scenario against the DB2 database.

To begin, in the BizTalk Administration console, create a new application. This example uses an application named HISDB2Test2.

Create a Send Port in BizTalk Server

To create a send port

1.In the BizTalk Administration console, right-click Send Ports in your application. point to New, and then click Static Solicit-Response.
2.Name the port DB2SendPort.

3.In the Type text box, select DB2, and then click Configure.
4.On the DB2 Transport Properties dialog box, click the ellipses on the connection string property to bring up the next screen.
5.Select Existing connection string, and then click Browse. At this point, you should be able to select the UDL created earlier.

6.Click Open, and then click Finish to return to the DB2 Transport Properties screen.
7.Supply the information for Document Target Namespace and the Response Root Element Name, and then click OK.
This example uses DB2Update and DB2UpdateResponse, respectively, for these fields.
8.While still in the DB2SendPort -Send Port Properties dialog box, to create a filter to route messages from the file receive port to this send port, click Filters. This is not totally configured yet, but will be later when the receive port is created.
9.Set the BTS.ReceivePortName property value to FileReceivePort_DB2Query, and click OK.

Create a Second Send Port

In this step, you create a static one-way port to persist the response from DB2 to a file.

To create the second send port

1.Create another send port (static one-way) to receive the response, and in the Name text box, type FileSendPort_DB2Response.

2.In the Type drop-down menu, select FILE, and then click Configure.
3.In the FILE Transport Properties screen, for the destination folder, browse to a directory on your drive, and for the file name, type DB2Response_%MessageID%.xml and then click OK.
4.In the FileSendPort_DB2Response - Send Port Properties screen, select Filters.
This creates a filter to route the response messages that the DB2 adapter receives, generated by the updategrams.
5.Set the BTS.SPName property value to DB2SendPort, and then click OK.

Create a Third Send Port

In this step, you create another static one-way port to persist the response from DB2 to a file.

To create the third send port

1.In the BizTalk Server Administration Console, right-click Send Ports, point to New, and then click Static One-way Send Port.
2.In the Name text box, type FileSendPort_DB2Query.
3.In the Type drop-down menu, select FILE, and then click Configure.

4.In the FILE Transport Properties screen, for the destination folder, browse to a directory on your drive, and for the file name, type DB2Query_%MessageID%.xml.
5.Click OK.

6.In the FileSendPort_DB2Query - Send Port Properties screen, select Filters.
This creates a filter that sends the XML documents returned from the DB2 query to a directory on the hard drive.
7.Set the BTS.ReceivePortName property value to DB2ReceivePort, and then click OK.

Create a Receive Port (One-Way)

You now need to create a one-way file receive port. This port receives the XML file that contains the data that will be written to DB2.

To create a one-way receive port

1.In the BizTalk Server Administration Console, right-click Receive Ports, point to New, and then click One-way Receive Port.
2.In the Name text box, type FileReceivePort_DB2Query.

3.Click Receive Locations, click New, and name the receive location FileReceiveLocation_DB2Query.

4.In the Type drop-down menu, select FILE, and then click Configure.
5.In the FILE Transport Properties screen, for the destination folder, browse to a directory on your drive in which XML files will be placed.

6.Keep clicking OK until you exit the dialog boxes.

Create a Second Receive Port

You now need to create a receive port to execute a SELECT query against DB2.

Setting up the Biztalk Adapter for DB2 Using Receive Ports

Setting Up the BizTalk Adapter for DB2 Using Receive Ports

Summary

Contents

Setting Up the BizTalk Adapter for DB2 Using Receive Ports

Overview

Setting Up the Data Connection

What Are Updategrams?

Insert

Delete

Update

Stored Procedures

SELECT Statements

Converting Updategrams to a SQL Statement

Data Value Conversion

ResultSet Messages

BizTalk Configuration

Create a Send Port in BizTalk Server

Create a Second Send Port

Create a Third Send Port

Create a Receive Port (One-Way)