Last year I have done an integration project for slaughterhouse in Holland. This slaughterhouse had a logistic program running on AS/400 with a DB2 database. I was asked to extract data out of the database concerning planning of vehicle’s to arrive at the slaughterhouse with livestock to be slaughtered. This data was displayed (consumed) by an ASP.NET application. When vehicles arrived this was registered by operators using the ASP.NET app. During the slaughter process earmarks on livestock was used to provide more data like weight, meat quality and so on about the animal. The app had an SQL Server 2000 database as backend. All this data had to be put back into DB2 database.
An integration solution was wanted in this scenario, where the slaughterhouse wanted to use BizTalk to get planning data out of the database each night and all data gathered during the day back in DB2. The planning data in DB2 had to be extracted through a sequence of queries on different tables in a certain order. The amount of data was less the a few Mb per query and this data had to be put into SQL Server through a transactions. During the day all gathered data had to be transferred back to DB2 through short time intervals of a few minutes, so data was available in the logistic program in a near time fashion.
I used HIS 2006 BizTalk adapters in this scenario to get data out of DB2 and place in the SQL Server 2000 database. By installing HIS 2006 Beta-2 I was able to use the provided adapters. I configured the adapters so I was able extract data out of the DB2. Because data had to be extracted out of DB2 through couple of queries on certain tables and be placed in SQL Server database in certain order I used an orchestration to force the order of queries and transaction of inserts in SQL Server in bulk. This was a one way process once a night before slaughtering was done during the day and had to be correct. During the day certain data was gathered by a few stored-procedure in a predefined order by intervals of couple of minutes and placed back through an other orchestration back into DB2. If the first stored procedure does not give any result, no other procedure is called and receive location will try calling the procedure next interval. This way all related data that had to be transferred back into DB2 was guaranteed.
I must say it was a very experiencing project to do with new technology integrating DB2 on AS/400 with SQL Server 2000. In a previous post on SOA & BPM conference made by me I mentioned a talk of Paul Larsen of Microsoft, where he talked about integrating legacy apps with BizTalk Adapters for Host Systems and Host Integration Server. The info was good, but there demo about host integration BizTalk adapter on DB2 failed. But already I knew through my experience that is was possible. Fun to have been witnessing a subject I was doing at that time as well.
Now HIS 2006 is available through Microsoft. And there is also a blog for HIS 2006. I am currently in trying to get an article published in the Dutch .NET magazine about HIS 2006 BizTalk Adapters.
