Problem solve Get help with specific problems with your technologies, process and projects.

SQL vs. DDS -- The good, the bad and the downright ugly

What does DDL offer over DDS and vice versa? Let's take a look.

Author Additions To This Tip

In case you haven't heard, DDS is no longer the development language of choice when it comes to defining and maintaining your database. It has been usurped by SQL's Data Definition Language (DDL).

Why the change? DDL is the standard definition language used for most (if not all) other databases. It is reasonable for IBM to adopt the same standard for OS/400 (i5/OS).

But what does DDL offer over DDS and vice versa? Let's take a look.

The Good

Here, in no particular order of importance, are some of the good points of DDL:

  • It is not a question of replacing all you current DDS with DDL. A file is a file regardless of whether it was defined using DDS or DDL. You can create logical files over DDL defined tables just as easily as you can define views and indexes over DDS defined physical files.
  • You do not have to become an expert is SQL to start using DDL. iSeries Navigator has an easy-to-use interface to get you started. The figure below shows the interface for defining a table (physical file). It is mouse driven, and you select the required options as opposed to learning the SQL syntax and keywords.
  • The iSeries Navigator interface joins many disparate functions in one place. The figure above shows tabs for Indexes, Referential Integrity, Check Constraints and Triggers; each of which would be different combinations of source and commands using a 5250 interface.
  • When you are ready to come to grips with SQL syntax, iSeries Navigator provides the option to Generate SQL for any file, regardless of whether the file was originally defined using DDS or DDL.
  • One of the most powerful features of DDL is that you can define a view of a view. That means you can define a complex join logical view and then define simpler views of that complex view as opposed to having to repeat the complex join logic in multiple views. You can even define a join between two views.
  • In a view you can construct derived columns that are way beyond the equivalent provided by DDS. Derived columns can be a simple construct such as the result of a formula applied to other columns (e.g., QUANTITY * PRICE) or they can have a more complex construct using some of SQL's scalar functions. Scalar functions are the equivalent of Built-in Functions in RPG. Just as you can define your own functions in RPG (subprocedures) so too can you define your own functions in SQL; these functions can even call subprocedures you have written in RPG.
  • Check constraints provide the ability to have the database manager validate the values that are placed in columns (fields). This can be a simple check of allowed values (e.g., PRICE > 0) or a more complicated comparison using multiple columns and formulae (e.g., PRICE > (BUYPRICE * MARKUP)). The nearest you have to this in DDS are the COMP, RANGE and VALUES keywords. Although defined on the database, these keywords are applicable only on display files. Check constraints can also be defined using the ADDPFCST command in a 5250 session.

The Bad

Here are the bad points of DDL. Some are problems because they are the opposite of the way you do things with DDS, and others are problems because they do not offer some of the functionality of DDS. Again, the list is in no particular order of importance.

  • DDL introduces a whole new terminology (at least for us) for the database. There are now collections, tables, indexes and views as opposed to libraries, physical files and logical files.
  • The DDL defaults are not the same as DDS. The default column name is the long name; this has to be defined with the ALIAS keyword in DDS (of course, that is good news for COBOL programmers). By default, all columns are null capable -- the opposite of DDS.
  • The friendly interface of iSeries Navigator is of little benefit when it comes to maintaining the database. You have to resort to SQL code unless you are simply adding columns to a table.
  • DDL does not have the equivalent of the EDTCDE and EDTWRD keywords.
  • When you define a table (physical file) in DDL the Format name is the same as the file name. This is not an issue for COBOL programmers, but it is for RPG programmers. You either rename the format in every RPG program that opens the table or create the table with the format name and rename the table. Neither solution is perfect. As an aside, my preference would be to have RPG allow the same name for the format and the file.

The Downright Ugly

Whereas each of the bad points listed above are manageable, there is one major problem with DDL.

SQL treats views and indexes as two separate entities. An index is a keyed logical, and a view is a non-keyed logical and never the twain shall meet. Or, to put it another way, you cannot define a key for a view.

That means all of those wonderful features that are available when defining views are of little or no benefit in high-level language programs. A view is useful only if you are not interested in a key sequence or in accessing records by key (and how often does that happen?) or if you are using embedded SQL.

Which to use?

Should you start using DDL or should you stick with DDS? The answer is to use both. Over the past couple of years I am inclined to use DDL to define any database, but I still resort to DDS when I need to. DDL offers functionality that removes code from programs and ensures the enforcement of rules through the database as opposed to the application.

It is almost certain (at least I hope it is almost certain) that IBM will provide some means of defining a combined view/index with DDL, especially since it can be easily defined with DDS. I am willing to make do with the bad as long as this one gets fixed.

Of course, I am also more then willing to have any of the bad features turn good.

About the author: Paul Tuohy is CEO of ComCon, an iSeries consulting company. He is the author of Re-Engineering RPG Legacy Applications and is one of the quoted industry experts in the IBM Redbook "Who Knew You Could Do That With RPG IV?"


My thanks to Dave Anderson for pointing out a couple of performance related omissions from this programming tip -- one good and one bad.

  • Indexes have a page size of 64K as opposed to an index page size of 4K or 8K for logical files. This can lead to an obvious increase in processing speed.
  • Logical files will share an access path if the keys are a subset of an existing access path (e.g., an existing access path is keyed on the fields A, B and C and you are defining a logical file with a key of the fields A and B: the access path will be shared). On the other hand, indexes will only share an access path if all the key fields match.

Dig Deeper on RPG iSeries programming