Another Look At .NET Apps Accessing IBM i

July 23, 2012 Dan Burger

Beauty and ease of multi-platform integration are in the eye of the beholder. Sometimes it’s the same vision. For instance, if you are a Microsoft .NET application developer and you are able to grab data residing on the DB2 for i database with the greatest of ease, it’s a beautiful thing. But it’s certainly no guarantee. Subject matter experts like Craig Pelkie and Derek Maciak find people lost in this integration wilderness on a pretty regular basis.

Not everyone is happy with the .NET and IBM i matchup. I wrote an article on this topic about six weeks ago, and now I’m going back to the well for more information. Both Maciak and Pelkie provide training and education on .NET application access to the DB2 on i database. Pelkie is a consultant and the owner of Bits & Bytes Programming and Maciak is CTO at Surround Technologies, an IBM independent software company.

From a the perspective of a .NET developer, programmers most commonly work with Visual Basic, C++, C#, and Delphi. Seldom are they familiar with the IBM i environment. However, there are a growing number of .NET developers that started out on the RPG and COBOL side. The greater the familiarity with environments, the better, but knowing just enough to get in trouble can open the proverbial can of worms. And it’s not all about the languages. Understanding frameworks and architectures is equally important.

“For developers who are primarily coming from a .NET background, IBM i is mostly just another database server,” Pelkie explains. “There are a number of database providers that can be used within .NET applications to connect to IBM i, including the drivers provided by IBM at no-charge in the IBM i Access for Windows product–ODBC, OLE DB, and the .NET Provider–as well as an OLE DB provider available from Microsoft and providers available from third parties.”

(HiT Software, Surround Technologies, Raz-Lee Security, and Progress Software come to mind. See the Related Articles listed at the end of this story.)

The problem Pelkie most often sees and hears about is that the IBM i Access Data Providers–which are part of the IBM i operating system and which are the most often used tools–don’t support Microsoft’s Entity Framework, a high-level abstraction layer for database access. Little known and seldom used is IBM DB2 Connect, a chargeable product that supports .NET and Entity Framework, and, most importantly, DB2 for i. Good luck tracking it down, however, even after you get to this landing page on the IBM website.

“Because .NET developers typically develop applications for Microsoft SQL Server in addition to the DB2 for i DB2 database,” Pelkie points out, “once they start working with Entity Framework for SQL Server, it can be a big step backwards to develop database access code without EF support.”

Maciak hasn’t encountered usage of the Entity Framework for IBM i all that often and for his own purposes utilizes his company’s Object/Relational Mapping framework for IBM i. But when it comes to choosing one of the IBM i data access tools–IBM i .NET Data Provider, ODBC .NET Data Provider, OLEDB .NET Data Provider, and DB2 for .NET Provider–he solidly recommends IBM i .NET Data Provider on the basis of it being a managed code provider, which, in his view, makes it a better choice for performance and scalability reasons.

“There are many pluses to using Entity Framework. The concept is great,” he says. “But when you go with some frameworks, you are tied to how they do things. If you design your own, as Surround Technologies has done, you have the capabilities you want.”

Certainly .NET applications can be developed without Entity Framework. Lack of support for Entity Framework is one of the worms in the can, but there are others.

“Many RPG programmers start into .NET with the assumption that if they ‘learn the language,’ be it Visual Basic or C#, they will be productive in the environment,” Pelkie says. “I believe that to be truly productive in .NET, there are actually three major pieces that must be addressed: the language, the .NET Framework itself, and the tools and deployment environment.”

Both Visual Basic and C# languages are object-oriented languages. For many RPG programmers, learning object-oriented programming is just slightly better than drinking cleaning products and this remains a big hurdle. Either choice, VB or C#, generates the same type of runtime code and results in similar performance. Pelkie’s observation is that most RPG programmers are more comfortable with Visual Basic, because the syntax is somewhat like free-format RPG. Programmers with some Java experience usually find C# to be syntactically similar.

Pelkie and Maciak agree that problems can arise when flaws occur in architecting the .NET applications.

“When a programmer develops a .NET application, most of the work of the application is done by features that are in the .NET Framework,” Pelkie says. “The programming language is used to orchestrate calls into the Framework. Many of the tasks that might have required extensive coding in an RPG program can be handled by simpler coding in a .NET program, because more of the low-level work is handled by the Framework. It is possible to get started with .NET programming and not know too much about the Framework, but to make any real progress, programmers must be constantly building their knowledge of what the Framework provides.”

“Architecture is the weak link no matter what data provider product you use for access,” Maciak says in explaining what could result in sub-par application performance. “Sound architecture on the .NET side can make a big difference. There are a lot of bad practices.”

In terms of tooling and deployment, most .NET programming is done with Microsoft Visual Studio and deployment is always on a Windows Server system. So learning to work with Visual Studio and understanding the basics of deploying Web applications within Microsoft’s Internet Information Services (IIS) server is a prerequisite to becoming a proficient .NET developer. Not surprisingly, programmers coming from the .NET camp find this easier than programmers coming from the IBM i camp. However, it’s not uncommon for RPG or COBOL programmers to be learning the .NET ropes.

When applications that use SQL to access data on the IBM i platform through a provider are criticized for slow performance, the bottleneck is sometimes found in the load being carried by the SQL statements. Pelkie’s suggestion is to test the SQL statements using the STRSQL green-screen environment on IBM i or by using the SQL performance profiling features within the IBM i Navigator.

Another issue to be aware of is that any SQL statements that are exhibiting poor performance need to be profiled against either the production database or a copy of the production database.

“Many programmers simply start coding their SQL statement right in the code for their program,” Pelkie warns. “It can be difficult to profile the statement because the program needs to be run to get the statement to execute, and there may be factors that make it difficult to run the program for profiling. One way to improve the possibility of profiling, and also simplify the program itself, is to move the SQL out of the program and put it into a stored procedure. IBM i has great support for SQL stored procedures; what you end up with is a stand-alone program that contains just the SQL. You can use the tools within the IBM i Navigator Database tooling to develop, test, profile and modify the stored procedure code. It is very easy to use stored procedures from .NET applications, so there is no downside to using them.”

Maciak prefers delivering all the data access as a Web service.

“Learn to service-enable code and deliver that to the .NET developers,” he says. “This allows performance testing on the services and shows that a service is delivered in a certain time. If it’s a fast time, the slow running app is not the fault of IBM i.”

In the end, you can chalk up a lot of the less-than-satisfactory results to the inability of IBM i and .NET developers to work with one another and to understand one another’s choice of platforms. Along with programming and architecting skills factoring into how beautiful this integration can be there’s the skill required to get two sides to sit down at the same table and work out their differences.

In addition to the suggested reading in the Related Stories listed below, you’ll find much more technical information in IT Jungle‘s email newsletter Four Hundred Guru. Go to the Search button at the top of this page and type in “Guru – .NET DB2 Access.”