Best practices when communicating with AS400 (IBM i) from .NET

Question

I need some help on what's important and best practices when building a .NET based reporting solution on top of an existing AS400 based system.

• What's the most suited integration technology (ODBC, OLE DB, ADO.NET) and does that depend on what version of AS400 we're talking about? Is it always DB2 databases or does that vary to? What other persistency system are usually used?
• Is it possible to call programs in the mainframe that has logic in them or is preferable to replicate that logic in the .NET layer and then call the mainframe DB directly?
• I guess a reporting system should be online and call the mainframe DB directly or are there other ways (for example file export etc.) that would be preferred?
• What technical details are important to find out before starting the project (AS400 version etc.) to eliminate problems.

Basically I'm interested (and will vote up) in all information and experiences from .NET/AS400 projects. I've never done it before and need to know the pitfalls before project start.

Answer 1

Ok I used to work with and connect to AS/400 and mainframe systems from .NET several years ago. I might not be able to answer your questions directly but I can let you know what worked for me and some of the stuff I did in the past.

A common term for this type of work is Enterprise Application Integration (EAI) so you could start by reading up on that. As far as I know it is possible to have more than just DB2 databases on AS/400s. There were 2 ways in which we worked with green screen (or legacy) applications:

1. Access the data source/stores directly
2. Create a session, send keystrokes such as F10, F4 etc. that the legacy app uses to navigate through different screen, and grab data from fixed points on the legacy screen (this is sometimes called screen scraping).

To partially answer your first question, to access the data sources directly we created DSNs (data source name) using ODBC drivers which were available from 2 companies at the time, Rumba (made by Wall Data), and Attachmate (made by I think IBM). To create an ODBC DSN you typically went into Admin Tools/Data Sources and added a system DSN. You would need the (legacy system) host name, user name to log on with and password. We then used these DSNs inside .NET apps to create a connection to the legacy apps. If you have a DSN you can then use something like SQL Server DTS/SSIS to grab data from the source and save it in some location, whether that be the database, CSV files, Excel files etc. It's also more than likely possible to have a reporting tool (Crystal/SQL Server Reporting Services) access the data source directly using the DSN so you could report directly from the data source. Also you could probably create DSN-less connections as well, years ago we needed DSNs.

To partially answer your 2nd question it is possible to call and use the logic on green screen apps if you want to. A green screen is typically divided into a set number of rows and columns, and we used a standard called HLLAPI that sent keystrokes from a Windows system to positions on a legacy screen. We used Rumba for this which was available as an OCX control and I'm sure Attachmate is also. For example you could create a Winforms form with User ID and password text boxes, then create a session to the legacy app, and usually the first screen would be a logon screen. Then using the positions of the user name and password fields on the green screen send the User ID and password to those positions, and then send an Enter keystroke or whatever was needed to log on. You could then navigate to another screen e.g. a search screen, send the data and keystrokes to perform a search, then grab the resulting data from the green screen. Another approach is to create Win/Web forms that replicate the green screen app and get data from the data stores directly. The advantage of this is that you don't have to know the keystrokes / navigation of the legacy application which could get cumbersome for a large green screen system. There's no right or wrong it depends on the circumstances. Our company did a mixture of both.

For your third question it depends on the type of reports you want. If they need to be real time then you can connect directly to the data store. If they don't need to be real time you could do nightly transfers of the data from the legacy system and store the data in SQL Server for example, then run your reports against the SQL Server data.

One answer for your 4th question is that you will definitely need to get your hands on someone that knows the green screen application. You will be spending hours and hours going through the screens on the legacy app so access to user(s) that know the system is crucial. Also you will need logon id and passwords etc.

Finally there are some 3rd party companies that specialise in transferring data from source to destination, one off the top of my head is Data Mirror. Another approach would be to use a middle tier integration product like BizTalk or Tibco, both of which take data from one or more sources and stick them in one or more destinations, but this may be overkill depending on your requirements.

Hope that helps and good luck :)