The Great Debate: DDS vs. SQL?

Debates have been raging since early in the life of the i (back when it was an AS/400 <grin>) about "native vs. SQL." Most of the debates have focused on how to access data. It seems to us that the winner in the debate on accessing data is the middle ground (i.e., different options are better for different situations).

But in more recent years the debates seem to have been renewed with a slightly different focus--creating database files (or tables, if you prefer). An increasing number of our clients ask for our advice on that subject. Should they create all of their new files/tables using SQL, as IBM (and some others in the industry) are suggesting? Is that the best way? What are the pros and cons of doing it?

Many shops are also going a step further and asking if they should go back and "retrofit" their existing DDS-created physical and logical files to SQL tables and indexes. They have heard of many "clear advantages" of doing this. So have we, but to be honest, we're still struggling a bit with whether the advantages are significant enough to be worth the (often extensive) effort of doing the work.

We have also found that many of our clients are confused and unable to diferentiate between the two debates. That is, they often use the justification for creating files using SQL when saying "that's why we want to change all our DB access from CHAINs, READs, WRITEs, etc. to SQL."

We are in the process of writing an article for IBM Systems Magazine's electronic newsletter, i5 EXTRA, on the subject of using SQL vs. DDS for creating files. We would be interested in your experiences and thoughts on the subject as we do so. Please let us know. We'll be posting here when the article is available online for those of you who do not (yet) subscribe to i5 EXTRA. Or better yet, subscribe now.

Comments

[Dan Devoe]

Our shop uses DDS on the 400.

A few months ago, I attended the Northeast System i User Group conference. One of the classes that I attended was "What's new in V6R1 for database (DB2 for i5/OS) for iSeries".

I was greatly disappointed to hear that IBM did a number of improvements strictly for SQL tables, ignoring any improvements for DDS-based files.

Why would IBM abandon development of DDS, and take a "Microsoft-ish" approach to its core development?

One of the strengths of the 400 is DDS. A good number of 400 companies primarily use DDS.

"Forcing" them to convert to SQL tables is like "forcing" XP users to go to Vista. The end result is people look for alternatives, such as the Mac or Linux.

I guess that SQL tables is strategic, and DDS is non-strategic...

[Luis Rodriguez]

It should not be an "either-or" proposition, as it depends (as always) on many factors, such as business needs, existing skills, etc.

In the company I work for (a private clinic, with a lot of old programs and files) we have found that designing new files with DDL do offer a lot of advantages, such as clearer code, that is instinctively clearer for the new generation of programmers (as is coding in RPG /free).

Also, working in DDL allow us to create views detailing (more or less) complex business rules, that make for easier (and clearer) queries (most of our programmers here still use QRY/400). In fact, many times the DDL views work like a kind of "black box". We know that if the DDL has been correctly coded, the resulting view must be ok, so you can express at once what previously required 2 or more queries (creating temp files, joining result files, etc).

Of course, we must not forget that SQL tables and indexes are (most of the time) better performing than DDS tables, and have the additional advantage of checking the data integrity and consistency at write time, which allows (almost obliges) us to have better code.

That said, sometimes we need to write DDS for those instances where an old RPG program needs to do RLA to an index with an INCLUDE/OMIT spec. (we are still at V5R3 - V6R1 does away with that limitation)

Hope this helps.

[Darren Bell]

We have loads of old code that use DDS. We also have code that uses SQL tables/views. We have found it easier to use sql tables/views, even from RPG.

Increasingly though, we are using Java and Hibernate for the majoraty of our i5 programming. And this works great (big JTOpen).

We are also using Apache Maven with Hibernate to generate our schemas for us on the fly. Our throughput and efficiency has been increased drastically.

Yes, DDS has it's place, but it's home is shrinking all the time.

[B.Hauser]

Hi,

for us moving from DDS to SQL is just like moving from OPM to ILE (or RPGIII to RPGIV). Even today you can develop in RPGIII, but only RPGIV will be enhanced.

Same thing with DDL. IIRC DDS isn't enhanced any more since release V4R4. At release V5R3 there was a single enhancement to allow 63 digit numeric fieds, that's all.

All database devopment from IBM is done for DDS for examples:
SQL Tables:
- Creating and using Identity Columns (V5R1)
- Hidden Columns with automatic update (i 6.1)
- SQL Functions for encrypting and decrypting field values
- Using Large Objects or Data Links
- Data validation at write time (and not when a row gets read)
- Constraint awarness that prevents the query optimizer not to execute a nonsens query

SQL views:
- Views are much more powerfull than DDS described logical file. Everything that can be specified in a SELECT-statement (with the exception of an order by clause) can also be specified in an view.
In this way it is possible to move a lot of program logic into database logic.
(for example recursive queries)
- SQL views can be used with native I/O, but because of the lack of an order by clause, should only be used with native I/O if no predefined sequence is necessary.
- SQL views can be used like a table (or a physical file):
embedded SQL, ODBC, JDBC, interactive SQL, Query/400, Excel Download ...

SQL indexes
- The default page size for an SQL index is 64K while the default page size for a DDS described logical file is 8K
- An SQL index can be used with native I/O (specified in the F-Specs) like any DDS described keyed logical file
- There are a lot of enhancements for SQL indexes (Derived Indexes) in i 6.1 that allows you to replace almost all DDS described logical files.

- Instead Of triggers are registered for SQL views will allow you a further redesign of you grown database.

In this way:
All new tables are created solely with SQL. Each of this new tables gets an identity column that is registered as primary key. This identity column will be used to choin files together.

Native I/O is only used for Chains. But instead of accessing a DDS described logical file, now an SQL index will be used.

All other database access is realized with embedded SQL against a view.

We already replaced most existing keyed locigal files without select/omit clauses with SQL indexes with the same name.

We move as much program logic as possible into SQL views, so that the source code gets reduced to a minimum, i.e. SELECT * From View. Because a SQL view never has a key, you can have as much views as you want without any performance decrease.

For new development we also use primary and secondary keys, referential integrities and triggers. All in all we try to move as much as fuctionality as possible into the database.

The existing old programs or DDS described files are not yet converted, neither into RPGIV nor into DDL. But as soon as we have to change one of these program we convert it at least into RPGIV and replace the DDS described logical files with SQL Indexes and depending on the labour cost even replace native I/O through embedded SQL.

In this way we'll somewhen reach the point where we can add instead of triggers to our views and redesign the existing database.

Birgitta

[Larry Blake]

SQL has 2 big advantages over standard RPG file processing:

1. When the logic is “in the data”, it's much easier to have multiple interfaces (for example, RPG, Internet, RPG, handheld devices).

2. You can write applications that don’t lock the files. A web-based application uses no resources when it’s just sitting there. It only hits the database when a page is refreshed. This allows higher availability, support of a greater number of users (because the number of simultaneous users doing anything is fairly small), and some fault tolerance.

[Bill Reed]

One consideration I haven't seen mentioned is the use of cross-reference utilities and how they re-create files. We have used Hawkeye Pathfinder for many years, and find it to be both powerful and intuitive. It's easy to modify a file's DDS and then with a few keystrokes, submit a job that will rebuild the file and all its LF's, copy and map the data, and recompile all modules, programs, and service programs referencing the file(s).

The last I checked (and it was fairly recently) this product needs DDS in order to do a file rebuild (although it could still re-compile the programs if you rebuilt the files yourself, say with SQL). The utility does not look for any stored SQL statements.

This should not be taken as a reason not to try newer and better things, nor is it a criticism of the utility. But as someone who uses that utility on a daily basis, I'm hesitant to give up that function to try an approach whose advantages may not compensate for that loss.