DB2 For i Modernization Gets Assist From RPG OA

June 25, 2012 Dan Burger

If you don’t know something is broke, is that any reason not to fix it? That’s not a trick question. It’s another way of saying ignorance is bliss, which doesn’t carry a lot of weight in traffic court or database management. You might think you have a get out of jail free card because the IBM i operating system, with its built-in database management system, doesn’t need a database administrator, but that little marketing half-truth is exactly what “ignorance is bliss” is all about.

It’s true the IBM midrange computer doesn’t need a database administrator in the same way that Oracle, Microsoft SQL Server, or Sybase does. Those DBA positions are full-time jobs that add $100,000 annually to the cost of the databases, which are standalone products with standalone costs. But the thing that may have cost AS/400, iSeries, and IBM i users is that most have tended to ignore the database and as long as nothing was broke, there was nothing to fix.

For many companies, this scenario can be described as “so far so good.” But the future, with its insistence on open and distributed systems, may not be so kind.

This is not a story about the sky is falling. It’s a story about what you can do to prepare the wealth of information in your database for modern times, which means that data, in a lot of cases, needs to be accessible to more than just RPG and COBOL applications. It also means the DB2 for i database is capable of much more than what is currently being asked of it.

Just as modern RPG is modular, modern DB2 for i development is modular. In either case, modular is the opposite end of the spectrum from monolithic. Modular development makes code easier to reuse and, at the database level, it means code that has for years been written into each program can be written once and accessed by multiple programs. Obviously, less coding means apps can be written quicker.

The tallest hurdle companies run into if and when they consider database modernization is the amount of coding changes that must take place (just as it is with application modernization that goes beyond screen scraping).

But there are two things to remember about coding changes. The first is that technology has resulted in fewer hand-coded changes than there used to be. The second is that data-centric development removes data processing from the application code, which shortens the app dev process and makes business processes the focal point. When data processing is handled on the database side, it is written once. When it is handled on the application side, it requires coding in each app that is written.

If an RPG (or COBOL) program could be advanced in terms of openness and interoperability with only a few lines of code being changed, would anyone be interested?

Dan Cruikshank, one of the members of the DB2 for i dream team at IBM thinks we may be on the verge of finding out. Cruikshank has been modernizing databases for IBM midrange shops for nearly 20 years using SQL and data-centric programming. He says applying RPG Open Access to this task has made it much easier. There is code available that can help, and by reusing components Cruikshank transforms RPG I/O to an I/O call using the call-level interface, which has been on the platform for many years. The process eliminates rewriting code from scratch. Like anything new, it requires learning unfamiliar skills and new tools, but he says it does not require an army of programmers.

The benefits include reduced IT expenses, derived from enhanced developer productivity, improved application performance and scalability, which comes through reducing CPU consumption and disk I/O operations. Over and above all that is the benefit of keeping data on the IBM i platform.

Refactoring and re-engineering the DB2 for i database to support SQL brings into play a set processing capabilities (replacing single file processing) and functionality that includes automatic key generation with identity columns and sequences, improved online analytical processing capabilities, high-speed text searches, and query optimization. But at what cost? And what if you crash the database?

“In most heritage applications, only about 20 percent needs to be changed,” Cruikshank says. “Most companies are not aware of the 20 percent until someone like me points it out. All they see is the 100 percent and that appears to be overwhelming. Once they realize that only 20 percent or less of the application portfolio accounts for 80 percent or more of the resource consumption, then the mountain becomes a mole hill. Changing a handful of programs becomes a no-brainer decision. There are tools that help determine what needs to be changed within the 20 percent, and the truth is that only a few applications–the ones that are truly critical–that need to be altered.”

They are easily identified, he says. The first to look at are those that use open query files. Next, look for programs that are doing updates, inserts, and deletes, especially within a loop. These are the programs that are most likely to be shared with programs written in Java, NET, PHP, and all the other possibilities in a hybrid environment. Also consider altering programs with mass reads and/or writes in batch jobs.

“RPG OA comes in because all the RPG programs that are doing updates to the same file now go to a handler that calls the same service program that calls .NET or Java or PHP. With just a few lines of code change, everyone plays by the same rules–rules that are pushed down to the database.”

Before RPG OA, which was introduced in IBM i 7.1 and made compatible back to 6.1, the process of converting the familiar DDS-based programs to the DDL-based programs that are used in SQL typically involved a long program rewrite using either SQL stored procedures or external stored procedures using embedded SQL in the RPG program so it would recognize and use the new database access methods. Now Cruikshank writes an Open Access handler that transforms the traditional I/O to call a service program or a stored procedure.

Now you don’t need to worry if your newly coded database programs will lock horns with applications and crash the database. Cruikshank explains the benefit of a single relational database with two interfaces as allowing conversion to take place gradually rather than a flip-the-switch-and-pray process. DB2 objects created with DDS can be accessed with SQL and objects created with SQL can be accessed with the native record-level access interface. This allows SQL conversion and the added capabilities it brings without impacting existing applications.

There was a time in the mid-1990s when Cruikshank showed people how to make RPG or COBOL act like SQL, but since IBM decided SQL was the roadmap for the future he says companies are wasting programming dollars by not using SQL. (The SQL runtime engine is integrated and built into the IBM i operating system.) Companies have been slow to see the light. Most have yet to use SQL and some that did have translated RPG and COBOL to SQL on a one-for-one basis, meaning it still looks at one record at a time, which defeats the power of SQL for using sets of data and multiple tables. Cruikshank claims that companies that replace an RPG program that has multiple files with a single SQL view are realizing 10X to 20X improvements in performance. When a batch program is taking too long to run, he says, you can’t expect it to run fast when it reads only one record at a time.

To take a deeper dive into the RPG OA techniques, Cruikshank provides code that he uses to write handlers in two published articles. The first is titled Decoupling RPG database IO using Rational Open Access: RPG Edition, which you can find here. The second is titled Accessing SQL Functions Using Rational Open Access: RPG Edition, which can be found here.

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search