BizTalk Integration with AS400 Using DB2 Adapter

21.05.2018

The BizTalk Host Adapter for DB2 has been designed to access IBM's DB2 database on a number of platforms including mainframe, AIX, AS/400, and Windows.

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.

The adapter serves two main functions:

For Send operations (both One Way and Solicit Response), the adapter sends SQL commands and stored procedures to a DB2 instance, with the option of soliciting a response.
For Receive operations (One Way only), the adapter creates a SQL command or stored procedure that polls DB2 objects and creates per-row messages, which are then submitted to the BizTalk message system.

Implementation:

The first step is to understand how to establish data source for DB2/AS400:

Microsoft Host Integration Server technologies and tools enable enterprise organizations to integrate existing IBM host systems and data. Data Access Tool with Data Source Wizard guides enterprise developers to define and manage connections to DB2. Data Access Tool and Data Source Wizard utilize the Data Access Library as underlying technology, to connect these tools to the Microsoft network clients and data providers at runtime.

BizTalk Adapters are based on the Microsoft ADO.NET Data Providers for DB2 and Host Files, offering inbuilt wizards to configure the static solicit and response send ports solutions that efficiently integrate DB2 databases without writing code.

BizTalk Adapter for DB2 Dynamic SQL, Stored Procedures HIS data provider (SELECT, INSERT, UPDATE, DELETE, CALL)

To create a HIS data source:

Install Microsoft Host Integration Services and build a new connection string using Data Access Tool.
The Data Access Tool enables you to define and test connections to remote IBM DB2 relational database servers and IBM host file systems.
The intuitive Data Source Wizard guides you through the process of defining, test-verifying, and storing the connection.

From the BizTalk programming perspective the experience of dealing with the DB2 adapter is similar to that when dealing with other databases like using the add adapter metadata wizard to generate XSD schemas representing the request and response signatures of your stored procedure and send the appropriate messages to the DB2 adapter.

Here are a few scenarios that I have come across working with the DB2 adapter and BizTalk orchestrations.

Scenario 1: POLLING THE AS400 TABLE AND PERFROM SELECT AND UPDATE OPERATIONS: (BizTalk Receive port) - SELECT Operation on Receive with UPDATE / DELETE command

The requirement is to poll one of the AS400 tables and retrieve the latest data and update status flag as "Processed" as data returned from the table. The resultant data will be handled and processed later in BizTalk to map to other enterprise systems.

Steps to configure DB2 adapter on BizTalk receive port:

Create one way receive port and receive location and select DB2 as transport type and click on configure
Create new or select the connection string from previously created HIS data sources
Provide appropriate Root element name and target name space for the schema
The SQL command could be either a SQL SELECT or stored procedure (with static parameters). A message is created by the adapter for each row in the returned dataset. For the SQL command, write the SQL statement to retrieve the necessary data from AS 400 table (ex: SELECT PID, PDESC, QTY,ORDERDATE, STATUS from TESTDB.SALES WHERE STATUS = 'O')

As per the requirement, the update status flag needs to be updated as it is returned. The Update Command property enables you to perform an action to be taken for each selected row.

Update Command, enabling any of the columns in the SELECTed table to be updated with static values. This is useful, for example, when retrieving newly added rows of data, to prevent reselection on each subsequent poll.
The polling period is also configurable on the Receive Location, with the interval and unit of measure specified, the default being 30 seconds.
In SQL command, just add "FOR UPDATE" at the end of SQL statement. This will internally create a cursor to update the rows as they are returned.
(ex: SELECT PID, PDESC, QTY,ORDERDATE, STATUS from TESTDB.SALES WHERE STATUS = 'O' FOR UPDATE)
In Update Command, click the ellipses, and in change command window select the update command and check the Status column, and it will build the update statement with the current cursor. Just modify the statement as needed.
(ex: UPDATE TESTDB.SALES SET STATUS = 'P' WHERE CURRENT OF <CURSOR>
Note: UPDATE and DELETE CURRENT OF CURSOR are valid only for rows that are fetched within the same unit of work.
The DELETE requirement works in a similar way as the UPDATE command
- Select "delete after read" option in update command ellipses and change command window.
  (ex: DELETE FROM TESTDB.SALES WHERE CURRENT OF <CURSOR>)

Note: When working with update or delete operations from a DB2 table, DB2 adapter expects to run in transactional mode and journaling should be turned on in the DB2 table. Otherwise, we may run into an error as "N not valid for operation" when attempting to execute the update/delete operations. Another option would be adding "FOR UPDATE WITH NC" to the end of select statement (NC = no commit).

In this manner, we can pull the data from AS400 table and update the status flag at the same time as it is returned (or) we can perform delete operation as needed.

Scenario 2: SEND REQUEST TO AS400 TABLE AND RETRIEVE RESPONSE AS RESULT SET: (BizTalk SEND port) - SELECT OPERATION as DB2 request and retrieve result set as response message

The requirement is to poll one of the AS400 tables and retrieve the latest records and update the status flag as "Processed" as they are returned from table (this is the first step as explained in above section). But the resultant data will again be sent as a parameter for a new SQL statement or stored procedure, and it will retrieve the result set for each row that is returned. The resultant data will be processed in BizTalk to translate and map to other enterprise systems.

When sending messages through the adapter, either a one-way (send only) or solicit-response port can be used. The choice depends on whether the results from a database operation are required for subsequent processing or not.
The messages sent and received must conform to the configured schema although we can make changes to the auto-generated schema if required.
Create static solicit-response send port and select DB2 as a transport type and click on configure.

Steps to configure DB2 adapter on BizTalk send port:

Create new (or) select the connection string from previously created HIS data sources
Provide appropriate namespace and root node name for the XML documents sent and received from the adapter. The namespace is used to construct the outgoing messages as the required schema must be looked up by this namespace and the root node of the actual root element of the message.
The response root node is needed to correlate the outgoing message to the request correctly.

Generate schema with add adapter metadata wizard and the generated schema for SELECT.

Construct request message for SELECT to send as the request to the send adapter and retrieve the response.

xmlDoc.LoadXml(@"<ns0:TestRequest xmlns:ns0=TestNS>

<sync>

<Select><![CDATA[

SELECT VendorID,QTY,PONumber, where TESTDB.OrderID = '" + OrderId + @"'

]]>

</Select>

</sync>

</ns0:TestRequest> ");

MsgTestRequest= xmlDoc;

Now send SELECT request to As400 table and retrieve the response. Now the response schema will return the data in the form of a result set which is a single element (xs:anyType).
Now we need to transform this message into a typed message so that we can use it for further processing inside orchestration. To do this create a custom schema in the format of an expected result set and mass copy the result set message.
Example of resultant response message:

</ResultSets>

</Success>

</TestResponse>

In this way we will have a well-formed message to use it for further processes in translating to the destination schema message format.

Scenario 3: SEND UPDATE REQUEST TO AS400 TABLE AND RETRIEVE RESPONSE AS RESULT SET: (BizTalk SEND port) - UPDATE / INSERT OPERATION as DB2 request

The requirement is similar to Scenario2. Poll one of the AS400 tables and retrieve the latest records and update the status flag as "Processed" as they are returned from the table and the resultant data will be further processed. Send update request to another table based on defined criteria and retrieve the result (ex: success).

Updategrams work using a declarative model where the database operation, and hence the SQL to be generated are implied. This works by the presence or absence of the <before> and <after> elements.
The name of the immediate child node specifies the table to be affected and in the UPDATE case must be the same. The attributes on this table element specify the before and after values of the columns in the table referred to. At the very least these fields must include the primary key column(s) to identify the rows to operate.

Steps to configure DB2 adapter on BizTalk send port for update:

Create static solicit-response send port and select DB2 as transport type and click on configure
Create new or select the connection string from previously created HIS data sources
Provide appropriate namespace and root node name for the XML documents sent and received from the adapter

Generated schema with add adapter metadata wizard and the generated schema for UPDATE

Construct a request message for UPDATE to send as the request to the send adapter and retrieve the response. The name of the immediate child node specifies the table to be affected and in the UPDATE case must be the same

xmlDoc.LoadXml(@"<ns0:OrderUpdateRequest xmlns:ns0='TestNS'>

<sync>

<before> <ORDERS OrderId="1000345″ PONum="89076546″ Status="0″/> </before>"

+ "<after> <ORDERS OrderId="1000345″ PONum="89076546" Status="1″/> </after>

</sync></ns0: OrderUpdateRequest>");

Msg_UpdateRequest=xmlDoc;

The above update request message works as similar to the following SQL statement.

Ex: UPDATE ORDERS SET Status=1 where OrderId="1000345″ AND PONum="89076546″ AND Status="0″

In similar way we can perform batch (multi row) update, here is the example for multi update statement

xmlDoc.LoadXml(@"<ns0:OrderUpdateRequest xmlns:ns0='TestNS'>

<sync>

</before>"

+ "<after>

</after>

</sync></ns0: OrderUpdateRequest>");

Multiple before and after nodes can be specified in a single message to perform composite operations (all within a single unit of work) while each node can specify a different table to affect.
The response message will return with an update result like RowsAffected and success=1

INSERT also works in similar ways to update, here is example of an auto generated schema for INSERT.

It refers to the SQL statement: INSERT INTO ORDERS (OrderId,PONum,Status) VALUES ("1300345',"99078946',"0")

Note: When working with Insert, update or delete operations from a DB2 table, DB2 adapter expects to run in transactional mode and journaling should be turned on in the DB2 table. Otherwise we may run into an error as "N not valid for operation" when attempting to execute the update/delete operations.

Pay 10 Eur for receive by email Traslated in Italian or choose your language

BizTalk Integration with AS400 Using DB2 Adapter

Impostazioni avanzate