Comparison of DDS and SQL-Defined Files

Comparison of DDS and SQL-Defined Files

There are a variety of reasons to use SQL Data Definition Language (DDL) rather than Data Definition Specifications (DDS) to define our iSeries database files (or tables and views, as they're known in SQL terminology). Many SQL functions aren't available in DDS (e.g., views with summary values), and SQL is both IBM's and the industry's standard database language. But there's another important reason — performance. For many situations, access is faster for files defined with SQL DDL than with DDS.

To understand the performance implications of using SQL versus DDS, it is important to talk about some of the architectural basics, including differences between SQL and DDS. Fundamentally, SQL tables are OS/400 physical files, and SQL views and indexes are OS/400 logical files. As a result, many of the capabilities and behaviors of SQL objects are identical or similar to their OS/400 counterparts.

But there are two very important differences: data validation and access path size.


SQL and DDS Data Validation Differences
A major difference between an SQL table (i.e., defined with a Create Table statement) and a physical file created with DDS is the point at which data validation occurs. For a DDS physical file, the data is validated as data is read. For SQL, data is validated as it is written. The following Figure shows this comparison:




Access Path Differences
Another major difference between SQL-created indexes and DDS-keyed logical file is the size of the access path associated with an index object. The access path provides the key data and ordering information for the file contents. Since V4R2, an access path created via SQL, whether it be an index or constraint, has a logical page size of 64 K. A DDS keyed logical file will create, on average, an 8 K access path, up to a maximum size of 32 K.