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 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         7P 0       TEXT('Order date CYYMMDD')
     A            ORDAMT        11P 2       TEXT('Amount')
     A            ORDSTS         1A         TEXT('Status: O open')
     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,
  ORDSTS  CHAR(1)        NOT NULL DEFAULT 'O',
  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, and when they are checked. DDS has date, time and timestamp too (types L, T and Z), but many files keep dates in numeric fields, like the 7P 0 above, and a 7P 0 can hold the 31st of February, a DATE column cannot. Then there is a difference affecting every type: IBM explains that a physical file does not enforce data types as strongly as an SQL table, and that a program writing at record level can put invalid data into a packed field; in an SQL table such a write is rejected.

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 keeps working without recompiling if ORDERS becomes an SQL table with the same record format level identifier: IBM's modernisation Redbook says to check it after conversion. Converting the definition is not rewriting the application, and the starting SQL is generated by ACS (Schemas, "Generate SQL") or by the GENERATE_SQL service.

New files are made in SQL. It is the zero-cost choice: from today on, what gets created is created in DDL.

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, MTI_USED
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'PRODLIB'
 ORDER BY MTI_USED DESC, TIMES_ADVISED DESC

Do not create them all. The advisor reasons one query at a time: it does not know which indexes you already have, how many writes that table takes, nor that half the advice are variants of the same thing. Every index has to be maintained on every INSERT, UPDATE and DELETE, and a table loaded with useless indexes slows the overnight batches down, with the symptom appearing weeks later, far from whoever created them. An index on (A, B, C) already covers lookups by (A) and (A, B), so three pieces of advice are often a single index. How to read the advice and how to consolidate it is in the recipe finding the indexes the system suggests.

Sources

• IBM: Data type for physical and logical files
• IBM Redbook: Modernizing IBM i Applications, ch. 9, Database re-engineering