DDS and SQL: two ways of describing data

On IBM i a file can be described in two ways, and the result is the same kind of object. A file created with DDS thirty years ago is queryable in SQL today with no conversion, and a table created with CREATE TABLE is readable by an RPG program like any other file.

These are not two databases: it is one database with two languages for describing it.

DDS

DDS — Data Description Specifications — is the column-based description, sibling to RPG's. A physical file:

     A          R ORDERSR
     A            ORDNUM         7P 0       TEXT('Order number')
     A            ORDCUS        10A         TEXT('Customer code')
     A            ORDDAT         8P 0       TEXT('Order date CYYMMDD')
     A            ORDAMT        11P 2       TEXT('Amount')
     A          K ORDNUM

And a logical file, which is a view with a key of its own:

     A          R ORDERSR                   PFILE(ORDERS)
     A          K ORDCUS
     A          K ORDDAT

DDS also describes display files (DSPF) and printer files (PRTF), and for those there is no SQL equivalent: anyone working on 5250 screens will go on writing DDS anyway.

SQL

The same file, in DDL:

CREATE TABLE PRODLIB.ORDERS (
  ORDNUM  DECIMAL(7,0)   NOT NULL,
  ORDCUS  CHAR(10)       NOT NULL,
  ORDDAT  DATE           NOT NULL,
  ORDAMT  DECIMAL(11,2)  NOT NULL DEFAULT 0,
  PRIMARY KEY (ORDNUM)
);

CREATE INDEX PRODLIB.ORDERS_CUS_DATE
    ON PRODLIB.ORDERS (ORDCUS, ORDDAT);

CREATE VIEW PRODLIB.OPEN_ORDERS AS
  SELECT * FROM PRODLIB.ORDERS WHERE ORDSTS = 'O';

The correspondences are direct: physical file → table, keyed logical file → index, logical file with selection → view.

What genuinely differs

This is not a matter of taste. There are three concrete differences.

Constraints. SQL lets you declare primary keys, foreign keys, uniqueness and validity conditions, and the database enforces them against anyone who writes — old program, new program, ODBC connection. In DDS those rules live in the programs, and hold as long as everybody goes through the programs.

Types. SQL has DATE, TIME, TIMESTAMP and variable-length types. DDS works with numeric and character, which is why this platform is full of dates written as seven-digit numbers: there was no other way. A DATE column cannot hold the 31st of February; a 7P 0 can.

What the optimiser knows. SQL tables carry information DDS does not have, and the optimiser works better against tables defined in SQL. On large files the difference is measurable.

How it is read, which is a separate choice again

Describing data one way or the other is independent of how a program reads it. The two routes coexist even inside the same program.

Native record-at-a-time. CHAIN, READ, SETLL, READE: one record at a time following a key. It is fast when you know exactly which record you need, and it is how all the historic code is written.

Embedded SQL. You declare what you want and the database decides how to get it. It is far better when working over sets — totals, groupings, joins, complex filters — and there record-at-a-time is not competitive, because a join done by hand in RPG is a loop inside a loop.

What to do with what exists

The short answer is: do not convert for the sake of converting.

The code does not need touching. An RPG program reading ORDERS goes on working identically if ORDERS becomes an SQL table, as long as the fields stay the same. Converting the definition is not rewriting the application.

New files are made in SQL. It is the zero-cost choice: from today on, what gets created is created in DDL. No migration, no risk, and the benefit compounds.

Indexes, yes, straight away. There is no reason to describe a new key-only logical file in DDS: CREATE INDEX does the same thing, and the system itself says which indexes it would like.

SELECT TABLE_NAME, KEY_COLUMNS_ADVISED, TIMES_ADVISED
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'PRODLIB'
 ORDER BY TIMES_ADVISED DESC