Database Modernization: Why Convert DDS to DDL

In part 1 of this series on the why and how of database modernization, I introduced IBM’s major initiative ongoing across the IBM i platform called database modernization. In this second article, I want to address the question why. Why do you want to spend the time, energy and money to convert your DDS-described PF to DDL SQL Tables? The simple answer is:

  • Performance
  • Flexibility
  • IBM’s strategic direction


One of the ongoing challenges IT departments have is maintaining good performance in an ever-changing environment—especially in a high-growth company. Traditional Record Level Access (RLA) was never intended to consume mass quantities of data. That means, the more data you process the less efficiently your program runs. According to IBM, for every RLA I/O operation by an RPG program, you could have as many as three physical I/Os to the disk. This is especially true when accessing data via a logical file. As more data is processed, your program starts to slow down. Figure 1 is a graph courtesy of the IBM performance lab.

SQL-based access scales much better. As the number of rows increase, performance remains relatively flat (blue line). This is because of characteristics of embedded SQL in your programs, specific properties of DDL tables and architecture designed for performance. While a PF will process 8K page sizes, SQL Tables will process 64K page sizes. This means there is 8 times more data available in faster memory for your programs to access. Just converting DDS to DDL could give programs using buffered reads a significant and measureable performance boost. Now you may ask, what’s the downside? Your disk-bound processes now become memory bound, so you’ll need to tune your system differently as your programs start to rely more on data in memory than data on the disk.

Anecdotally, my company (ULINE) is seeing anywhere from a small increase to 20-fold increase in performance depending on the job. One example is a business user complaining about a query taking 45 minutes to run doing a column scan against a table of 110 million rows. By creating an encoded vector index over the table used in the query by the date column, we reduced the runtime to less than 2 minutes without changing any code.


Historically, RPG shops have had to sacrifice flexibility in lieu of performance. Speed took the primary spot of importance while the ability to respond quickly to our business clients suffered. Now we can have speed and flexibility at the same time. How would you like to add a new column to your table, on the fly, anytime you want without having to modify or even recompile your programs unless the program requires use of the new column? This is the advantage newer languages have. With SQL tables, you can modify the structure of the tables with minimal effort. In a well-architected system (which I’ll address in a future article in this series), this can be done without ever touching a program. With a simple “alter table” statement, you can add, modify or drop columns. This capability and flexibility gives you enormous options we’ve never had before.

IBM’s Strategic Direction

RLA using CHAIN, READE, etc., on IBM i is extremely proprietary. Skill sets are limited to only IBM i, and it’s becoming harder to find qualified individuals to fill positions. SQL is an industry standard used in many different languages and technologies, not to mention most likely being widely used in your own multiarchitecture environment today.

IBM won’t be making major enhancements for DDS PF/LF; there have been very few in recent releases. Rather, the strategy has been to use IBM’s IBM i development resources in enhancing SQL technology. Embedded SQL has been the driving force of the other languages for many years. Now, it’s the driving force for RPG and IBM i. IBM, as of IBM i 6.1 and 7.1, has included incredible enhancements with Common Table Expressions, Grouping Functions and Aggregation, as well as many other significant performance gains using the SQL Query Engine (SQE) over the old Classic Query Engine (CQE).

Get on Board

It’s time to get on board. Enhance your skill set. Embrace SQL by using both DDL and SQL embedded in RPG.

For further reading, I recommend:

Part 3 will focus on how to convert your DDS described PF/LF to DDL SQL Table/Indexes/Views. I’ll share specific examples of converting to DDL without modifying or recompiling your existing programs.

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