A Debate: DDS Versus DDL
Should you switch your DDS-defined files to DDL and replace RPG file access with embedded SQL? There's no simple answer, but a mix might be the best option.
“IBM says that we should switch all of our DDS defined files to DDL and replace our RPG file access with embedded SQL. What do you think?”
As we noted in our August 26 blog, we hear this question frequently from our customers and i conference attendees. It seemed like a good topic to address here in EXTRA, so we committed to writing on that subject this month. We’d just do a bit of research, write some test programs, talk to a few people who’d made the switch and formulate some recommendations. How complicated could it be? Boy, were we ever wrong!
Turns out that this isn’t as cut and dried as it sometimes appears in the minds of those promoting it.
Having studied the topic, we must conclude that there’s no simple answer. Indeed, there’s not one question here but two. There’s the DDS vs. DDL debate, and then there’s the SQL vs. RPG native I/O debate. You should review the facts and figures for each and make the decisions independently.
As far as the SQL vs. RPG native debate is concerned, our views remain much as they have for years. Our perspective is that the best solution is to use a mix of the two. So in the case of queries that return a result set—for example when querying a database to fill a subfile—we’d always lean toward SQL. When the task is to retrieve a single record for update purposes, then a simple RPG CHAIN is hard to beat. Beyond that, we haven’t got the space to elaborate further in this column but will return to the topic later.
Benefits of DDL
During our research, we found an excellent article by IBM’s Dan Cruikshank titled “Modernizing Database Access—The Madness Behind the Methods.” We immediately noticed Dan was a special IBMer—he includes RPG in his examples! He provides several direct comparisons between various combinations of DDS/DDL/RPG/SQL with accompanying performance analysis. One of his primary conclusions is that all new files should be defined with DDL, and certainly his data appears to support his conclusions.
We also came upon an excellent database modernization presentation, Application Modernization: DB2 for i5/OS style, from IBM’s Mike Cain and Gene Cobb. It provides a great introduction to the whole topic of database modernization and offers some cookbook solutions to common problems.
Once you’ve absorbed this information, another good resource is the IBM Redbooks publication, “Modernizing IBM eServer iSeries Application Data Access.”
As one of the comments on our blog points out, DDL makes creating the equivalent of complex logical views, including joins, a lot simpler. Since you can define a view of a view, you can base these on combinations of simpler views, which simplifies the creation process and aids in maintainability. Additionally, views can include derived columns that go way beyond the equivalent provided by DDS. They can also take advantage of SQL functions (similar to RPG’s built-in functions), which can be user defined and even call your own RPG logic.
Much of what’s written on this topic emphasizes that with DDL-defined files, data is validated as it’s written to the database. We’re all in favor of that—clean data is always a good idea, even if the writes take slightly longer.
Many references, however, indicate this results in a performance improvement on reads from DDL-defined tables because the data doesn’t need to be validated, whereas data from DDS-defined files is validated on reads because it wasn’t validated before. While this may be true when the data is being read by SQL (we’re not sure), it’s certainly not true of native RPG reads. The fact that a decimal data error occurs in an RPG program when a record is read from a file isn’t the result of any database-specific validation process; rather, it’s a function of the fact that RPG moves individual fields from the record buffer into their corresponding storage locations. It’s this movement of the data—not the READ or CHAIN—that triggers the error. You can easily prove this by reading the file directly into a DS—you’ll find that no decimal data error occurs. See our article “Ending Those Decimal Data Error Blues” for further details.
Certainly, in the test programs we wrote, DDL files didn’t outperform DDS files in a straight sequential processing situation. In fact, some results were so strange that we’re not including them here. We’re planning to discuss them with IBM. Since both Mike Cain and Barbara Morris are speaking at our upcoming RPG and DB2 Summit, we’ll have the opportunity to discuss it with them in person and will report back in a future EXTRA or on our blog.
Another good-thing-bad-thing difference is that DDL-defined indexes use a page size of 64K as opposed to the 8K most often used by logical files. In theory, this can significantly improve processing speeds; however, in memory-constrained systems, the overhead of loading the larger pages may cause more problems than it solves.
We’ve found quite a few challenges in making the switch from DDS to DDL. First, there’s a learning curve, albeit a small one. Rather than simply sitting down and trying to learn the syntax of DDL, a good way to build up your knowledge is to use IBM Navigator for i to generate SQL from any DDS- (or DDL-) defined file and study it. Other popular database tools such as DBU and Surveyor/400 also include this facility. However, don’t let the simplicity of this mislead you into thinking that retrofitting all your existing files will be a piece of cake. Read on…
Ignoring for a moment that switching to DDL requires us to use a whole new “language” (e.g. rows, not records; columns, not fields), some other issues can trip up unwary newbies. For one thing, the default for DDL columns (fields) is null capable—the exact opposite of DDS. Another stumbling block is that in many cases you can’t always take your RPG program and simply change the name of the file. This is because DDL-created physicals can’t have keys of the same type that a keyed physical can. So you have to make other changes as well, and in some cases it’ll be necessary to switch to using an appropriate SQL index (logical file) instead of directly accessing the physical as you did before.
The perennial problem many RPGers cite is that DDL generates files with the record format name the same as the file name. This has traditionally caused problems for RPG programs, although enhancements in DDL (V5R4) and RPG (6.1) have recently addressed the issue. We’ve never fully understood the fuss. It’s always been possible to use the DDL to create the table with the same name as the record format and then change the name of the table. It’s an extra step admittedly, but not difficult.
Another challenge is that SQL treats views and indexes as completely separate things. Indexes are keyed logical files, and views are nonkeyed logical files for purposes of reformatting, subsetting or joining data. As a result, you can’t define a key for a view. Since this is something you’d commonly want when processing the file with an RPG program, many of the most valuable features of views are of little or no use in programs that use native RPG I/O operations.
Should You or Shouldn’t You?
So, what’s the answer to the question of using DDL instead of DDS? There are clearly advantages and disadvantages either way.
For creating new files, we’d tend to use DDL unless there were some compelling not to due to DDL limitations. Remember that you can still define a DDS logical file over a DDL table if the view issue was a problem.
How about retrofitting all your existing files to be DDL-defined tables? We have a tougher time falling in line with this one. Any performance differences seem, at best, minor and very situation specific. It’s probably of more benefit where much of the data access is accomplished using embedded SQL rather than native I/O.
A retrofit isn’t the no-brainer exercise it might appear to be. Just a few common challenges are with format names and the differences between views, indexes and native logical files. Yes, workarounds exist, but it’s no trivial task to ensure all the RPG programs using native as well as SQL access can still utilize the data.
Given the complexity of the task, we’re left wondering if the time and effort spent on the exercise of a wholesale retrofit of most or all of your existing physical files might better be spent in other ways of improving your applications. Obviously, every shop will need to make this decision in light of its own specific requirements.
For those who decide a retrofit is a worthwhile effort, you may want to look into buying a tool that will more fully automate the exercise, including many of the issues we’ve brought up here. The only tool that we’re aware of that’s designed to reduce your work in this task is Xcase.
Good luck with making your decisions!