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.