DB2 BizTalk Adapters: Executing a program on an AS400 using a stored procedure

Posted: November 4, 2008  |  Categories: BizTalk Uncategorized
Tags: BizTalk DB2

I have just finished my first IBM integration project using BizTalk 2006 and will share my experiences here.  

Installation of the Microsoft BizTalk Adapter for Host Systems onto my BizTalk server gave me the two adapters that I thought I would use namely;

·         The Microsoft BizTalk Adapter for DB2 accesses a IBM DB2 database.

·         The Microsoft BizTalk Adapter for Host Applications used for invoking RPG/COBOL programs

The two tasks I needed to do were to extract some data from an IBM DB2 database and call an CL program on a IBM iSeries.

Using the BizTalk DB2 adapter on a receive port was a similar experience to using a SQL adapter. See  Download details: Setting Up the BizTalk Adapter for DB2 Using Receive Ports .

Initially I thought that I would use the BizTalk Adapter for Host Applications to invoke the CL program on the AS400 as described in Download details: BizTalk Server 2006 R2 Legacy Modernization with Host Integration Server 2006 but I found another way. I found that it was possible to use DB2 stored procedures to call RPG and CL programs using a DB2 adapter on a send port. This was approach was documented for Host Integration Server 2000 and it still works for the equivalent BizTalk 2006 adapters. See;

·         Download details: Integrating RPG and CL Programs by Using the Microsoft OLE DB Provider for DB2

See Download details: Setting Up the BizTalk Adapter for DB2 Using Send Ports for how to set up the send adapters. This approach worked well in my hands but it has one limitation namely “CONNECT, SET CONNECTION, RELEASE, DISCONNECT, COMMIT, ROLLBACK and SET TRANSACTION statements are not allowed in a procedure that is running on a remote server. COMMIT and ROLLBACK statements are not allowed in an ATOMIC SQL procedure.”  http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmstcrtpef.htm . I took me a long time to realise that one of the CL programs that I was calling contained a COMMIT statement. The behaviour was quite weird. The DB2 adapter sent the request to the AS400, the CL program is called and runs successfully but the DB2 adapter never gets a response from the AS400.

NOTE: There is no transaction time out setting on the DB2 adapter and if it does not get a response it will keep waiting for ever.  This is one big difference from the SQL adapter that you need to look out for when using the DB2 adapter.  The only way I could configure a time out was by placing the request and response from the DB2 adapter within a scope in an orchestration, choosing a long running transaction type for the scope and finally setting a value for the timeout on the scope. Does any one know another way of setting a transaction time out on a DB2 adapter?

 

 

 

 

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top