Database Modernization: Is It Right for You?

As most everyone in the IBM community knows, IBM has an ongoing major initiative across the IBM i platform called database modernization. It’s the process of moving your DDS (Data Description Specification) defined PF (Physical File) and LF (Logical File) to DDL (Data Definition Language) defined tables, views and indexes. This is the first of a series of articles that will address this initiative and some of the steps to take to move toward a data-centric architecture. This first article will focus on a general background of the topic and my recommendation of scope. Over the series, I’ll put forth an argument for why you want to convert to DDL/SQL, what benefits you’ll get by this effort, how to convert to DDL without having to touch (recompile) any programs, and how to structure your applications for maximum performance and flexibility.

Two Major Decisions

As you begin the process of database modernization and experiment with different techniques, you’ll soon come to realize you have two major decisions to make:

  1. Should you convert your DDS PF to DDL Tables?
  2. Should you convert your traditional I/O RPG programs to then use embedded SQL?

Several articles have written on these subjects and most authors have focused on one major decision criteria—speed. Using this as the only criteria, most authors on the subject come to the conclusion of “It depends” and “You don’t need to convert all your PF to SQL Tables or all your traditional I/O to embedded SQL.” I’m going to add one more criteria to your decision tree—flexibility.


Flexibility to ever-changing business needs is one of the major advantages other languages have over RPG. When you can add new columns to your tables without worrying about your programs or the effect the new column will have on your system, it gives you a huge advantage in responding to your business clients. Traditionally we, as RPG developers, have had to sacrifice flexibility for speed. Because our files are embedded in F specs and the record format of the file is embedded in the I/O buffer of the program, any change to the file causes us to have to recompile all programs that access that file.

RPG programmers have traditionally designed systems around this limitation. FILLER fields were added to the file so that these fields could be used for future expansion. A second master file (i.e., ITEMMAST and ITEMMAST2) may have been created so new fields could be added to the second master file instead of changing the original one and having to touch all programs that access it. Database normalization rules were thrown out the window to make it easier to access the data. These examples and more have caused us to limit our ability to respond to our business users in an environment where business requirements are in constant flux. As a result, IBM i and RPG have been under constant pressure to be replaced with more “modern” technology (e.g., Java, .NET, Oracle, SQL Server).

Due to performance issues when IBM released SQL on the IBM i some 20 years ago, these limitations were not solved. RPG developers still embraced DDS and traditional I/O for development and sacrificed flexibility for performance. This is totally understandable given the options we had at the time. Now, the promises of IBM and SQL are here. No longer do we have to sacrifice one option for the other. We can have both performance AND flexibility. We can compete head to head against these more “modern” technologies—and we can win. Power Systems servers running IBM i are the best machines ever created, in my humble opinion. Now we have a SQL engine as part of IBM i that’s competitive with other development platforms.

A Different Way

Over the next three articles, it will become obvious that I’m going to come down on a different side (i.e., you don’t need to convert to change your system to DDL or embedded SQL) than most other authors. I’ll be advocating for both converting your DDS to DDL and doing all (yes, you heard it—ALL) new development using SQL. Get rid of opening your files in F specs. Get rid of relying on the format level ID, worrying about level checks, and embedding the record format into the RPG programs. Embrace SQL and the performance and flexibility it provides to service your business clients. The upcoming articles will focus on the why and how of modernizing your database and embracing embedded SQL as a development technology.

Jim Ritchhart is the manager of database administration for shipping-supplies company, Uline, and is responsible for DB2 for i, SQL Server and Oracle.

comments powered by Disqus



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.


A Debate: DDS vs. DDL

Should you switch your DDS defined files to DDL?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters