DB2 For i? This Is SQL Server Calling

June 4, 2012 Dan Burger

Separate from the issue of RPG programmers doing Web development, application modernization, and DB2 for i modernization is the fact that many IBM midrange shops have Windows application development taking place, too. Often, the Microsoft platform has taken the lead in Web development. When that happens, eventually the business managers decide information residing in DB2 for i is going to be needed for .NET applications. And this can make you wonder whether the i in IBM i really stands for integration.

Two words that come to mind in any discussion about cross-platform database development are complexity and performance. Ideally, we want very little of the first and lots of the second. In reality, there seems to be just the opposite. This can be mitigated to a degree depending upon the amount of SQL work that has been done to a DB2 for i database, but there’s not been a lot of that going on. Much improvement has been made in the past five years, but it is still a slow slog when it comes to database modernization. A generous estimate might be that 10 percent of IBM i shops have undertaken such projects.

For its part, IBM seems embarrassingly inept at integrating SQL Server with DB2 for i. Product development is behind other DB2 environments. Finding documentation is not as easy as it should be. Some of what you will find is outdated (the newest Redbook was published in 2005) and customers doing research on DB2 for i can get tangled up with resources for platforms other than IBM i. Finding individuals (actual human beings with the knowledge and field experience) who you can talk with is a game of hide and seek. If you want customer support, you’ll really have to dig for it. At the end of this article, I’ll include links to several resources that can take you into the gnarly details of database interfacing. Those who are searching for answers should find these useful in planning a project, getting the DB2 and SQL Server teams on the same page, and a point of reference when seeking support from IBM.

In the trenches, seldom do you find IT staff that understands both sides of the equation. It’s like the right side and the left side of the brain with a little bit of the Hatfields versus the McCoys thrown into the fray. Get the communication lines open between your two database teams before you start looking for outsiders to blame for the difficulties. Knowing a little bit of theory and calculation can save a lot of misdirection and mistakes.

Over on the Microsoft side, .NET Entity Framework is getting a lot of attention. It’s relatively new (available since 2010), so only the progressive .NET shops have learned the ropes. But it is established that the combination of Entity Framework and SQL Server packs higher performance for that environment. And the .NET developers would like to see similar performance when knocking on the DB2 for i door. However, when opportunity knocks, it’s disappointment that answers.

Two things to consider: one is that IBM has figured out a better way to connect SQL Server and Entity Framework with DB2 for Linux, Unix, and Windows (LUW), but has not taken the same steps with DB2 for i; and don’t think that DB2 for i is the only database that doesn’t play well with SQL Server. Practically any database that’s been around for more than five years doesn’t sing in perfect harmony and the older the database, the more it sings off key. Still, the IBM i database could use the capabilities that other DB2 databases enjoy if we are to take seriously the “i is for integration” boast.

Technically, the integration is there, but it varies from maddeningly awkward and slow to moderately unrefined and lethargic. And you don’t need Entity Framework. There are various ways to reach the same goal of getting data from the database. You can do it with an ODBC driver (although that’s not used much any more). A JDBC driver is another option–there is no other option if you develop in Java), and there are ADO drivers and .NET providers that are most often used by Windows developers to access DB2 for i data. All of these have made integration possible, which is maybe a victory in itself, but satisfaction scores are typically below average when matched up against expectations.

Before you start studying and investigating what you can do to make or improve the connection between your DB2 and SQL databases, take into account a third factor–the quality of data in your database.

One of the problems with trying to approach the IBM i database with SQL Server tools is that many of the legacy IBM i databases were built before standardization practices were recognized as valuable. They evolved independently from what was going on in other IBM midrange shops with the same DB2 and different flavors of DB2. They included all kinds of workarounds. As time went on, some of these database modeling processes improved and some didn’t. Those that didn’t continued to use familiar crutches and workarounds that plague interoperability efforts today. So it could be your fault, too, basically.

Before you give IBM too much lip about what it needs to do to better support its customers, it might help to line up your own support from companies that are also interested in a better way to connect .NET apps with DB2 for i data. There is a profit motive that drives development work at IBM, just as there is at your company and for you as an individual. You can pretty much count on supply following demand as well as the squeaky wheel getting the grease.

If IBM doesn’t do a better job of making database connectivity easier and deliver better performance along with it, there will be independent software vendors that will likely step in to fill this gap. However, even if it is up to the ISVs, they still have to get the cooperation of the IBM i team to make it happen.

As promised, here are some sources of information that deserve your attention: Provider support for Microsoft Entity Framework, DB2 Connect website, and .Net Framework 4.0 features for connecting to DB2.