When it comes to defining database objects on the IBM i system, developers have traditionally used DDS (Data Description Specifications) but increasingly have the option to leverage DDL (Data Definition Language). Each approach has distinct features and limitations, especially as IBM’s database capabilities evolved over time. Here, we’ll look at some of the key differences and why DDL may be more advantageous in modern applications.

What would your rather prefer :

DDS Definition:

A       R CUSTOMER
A          CUSTID        10S 0    TEXT('Customer ID')
A          CUSTNAME      30A      TEXT('Customer Name')
A          JOINDATE       L       TEXT('Join Date')
A          CUSTADDR      50A      TEXT('Customer Address')
A       K CUSTID

Or

DDL Definition :

CREATE TABLE CUSTOMER (
    CUSTOMER_ID FOR SYSTEM NAME CUSTID NUMERIC(10, 0) NOT NULL PRIMARY KEY,
    CUSTOMER_NAME FOR SYSTEM NAME CUSTNAME VARCHAR(30) NOT NULL,
    JOINDATE DATE,
    CUSTOMER_ADDRESS FOR SYSTEM NAME CUSTADDR VARCHAR(50),
    ROW_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

as you can see its quite a leap between the two defintions.

Core differences between DDS and DDL

Data Validation

DDS (PFs): Data validation in DDS-described physical files (PFs) happens on READ. This means you can insert invalid data, such as character data in numeric fields, which can result in frustrating decimal data errors upon reading.

DDL (Tables): Validation in DDL-described tables occurs on WRITE. This prevents invalid data from being written, enhancing data integrity and reducing runtime errors related to data types.

Modern Database Enhancements

IBM i enhancements to the database layer are no longer implemented in DDS.

DDL, however, allows for compatibility with the latest features, including XML, DBCLOB, ROW_TIMESTAMP, and ROWID, which DDS cannot support.

Flexibility Field in Naming

DDS: Limited to cryptic, 10-character names, which creates difficulty in readability and maintenance.

DDL: Supports longer, descriptive names, improving readability and aligning with SQL standards. DDL syntax is also widely compatible with database analysis and documentation tools, unlike DDS, which relies on older tools.

Keyed Logical Views

DDS: Allows creation of keyed logical files, providing flexibility in defining views with specific access paths.

DDL: Does not support keyed views, limiting the approach for applications requiring custom ordering and access paths.

Record Storage Management

DDS: The default record reuse is OFF.

DDL: Defaults to REUSEDLT (reuse deleted records), improving storage efficiency.

Page Size for Performance

DDS: Has a default page size of 8-32K. (Note: this can be manually changed, but most dont know of this)

DDL: Defaults to a 64K page size, which can provide a performance boost in read-heavy applications.

Performance Considerations

Applications with a high read-to-write ratio benefit from DDL’s write-time validation, as this eliminates validation checks during reads. This shift improves performance when applications average numerous reads per write.

It is advantagous to convert most of the DDS tables to DDL when you are able to. This is a good step into "modernizing the database" from the maintenance and documentation point of view.

For more additional details :

DDL vs DDS Presentation

DDS to DDL Conversion : the Why

DDS and SQL : The winning combination