Definition: Externally Described Files

The AS/400 is an object-based system. Database Tables are referred to as Files. Files are defined to the system using either SQL's DDL or the native (and effectively deprecated) DDS (Data Description Specifications). Files defined in this way are referred to as Externally Described Files. The field (i.e., "columns") definitions of a file (i.e., "table") are stored in the File Object itself, along with information on the Primary Key (if any) and other attributes.

Since AS/400 is an object-based system, it treats Files as objects and can extract the File's attributes (including its field definitions) in a way that other operating systems without integrated compilers can only dream about. This field definition extraction process leads to Externally Described Files. You declare the file in a program and the Compiler retrieves the file's field definitions and inserts them into the program source code (at compile-time) as native RPG IV statements. This subtle but powerful feature has proven to be extremely addictive and natural-feeling to programmers of AS/400. Ninety percent of all databases stored on AS/400 are defined using DDS. Ten years ago, that figure was more like 99.9 percent.

Today the number of people using Embedded SQL to access files in their high-level language programs, such as RPG IV and C/C++ is probably a lot higher than you might think. Defining the actual database files, however, is typically left to native DDS, but that is slowly changing.

DDS was originally developed back in the 1970s for the IBM Datamaster but wasn't fully realized until IBM System/38 came on the market. Initially, System/38 was introduced in 1978 but first shipped in 1980/1981. It did not support SQL, however, instead it used DDS as its data description language. DDS was not only for database files, it was used for Display, Print and Telecommunications file definition. DDS is still used today, some 30 years after it was first introduced.

There are three fundamental reasons why DDS was chosen for data definition:

• SQL was not yet mature.
• In the shadow of the U.S. Government v. IBM monopoly court cases, IBM was building a proprietary system to spin off to another company should the Government succeed in its case.
• The Keyword support inherent in DDS allowed IBM to extend it as needed, and thus use it for any type of device definition, Display, Print, Telecommunications and yes, Database.

To define a typical database file using DDS, the following source statements may be used:

.....A          R CONTACT                                             
     A            CID            7P 0       COLHDG('Contact' 'Number')
     A            STATUS         1S 0       COLHDG('Status')
     A            FNAME         32A         COLHDG('First' 'Name')
     A            LNAME         32A         COLHDG('Last' 'Name')
     A            TITLE         64A         COLHDG('Title')
     A            EMAIL         80A         COLHDG('email')
     A            WEBSITE       64A         COLHDG('Website')
     A            LOCATION      64A         COLHDG('Location')
     A            COMPANY       64A         COLHDG('Company' 'Name')
     A            PHONE         11P 0       COLHDG('Phone' 'Number')
     A          K CID 

Note the final line contains the value "K CID". This indicates that the field CID (Contact number) is used as the key field for the file.

To define this same database file using SQL DDL, the following source statements may be used:

CREATE TABLE MYLIB/CONTACT (                 
  CID DECIMAL(7, 0) PRIMARY KEY NOT NULL DEFAULT 0 ,
  STATUS NUMERIC(1, 0) NOT NULL DEFAULT 0 ,
  FNAME CHAR(32) CCSID 37 NOT NULL DEFAULT '' ,
  LNAME CHAR(32) CCSID 37 NOT NULL DEFAULT '' ,
  TITLE CHAR(64) CCSID 37 NOT NULL DEFAULT '' ,
  EMAIL CHAR(80) CCSID 37 NOT NULL DEFAULT '' ,
  WEBSITE CHAR(64) CCSID 37 NOT NULL DEFAULT '' ,
  LOCATION CHAR(64) CCSID 37 NOT NULL DEFAULT '' ,
  COMPANY CHAR(64) CCSID 37 NOT NULL DEFAULT '' ,
  PHONE DECIMAL(11, 0) NOT NULL DEFAULT 0 )  ;

Note the 2nd line contains "CID DECIMAL(7,0)  PRIMARY KEY". The "PRIMARY KEY" attribute indicate that CID is the primary key for the file.

It does not matter to an RPG IV program whether a database file is defined with DDS or SQL. The resulting object is treated as an Externally Described File by RPG IV. However, by moving to SQL-based data definiton language (DDL) you have the added benefit of the enhancements to the database engine, not to mention several other improvements, such as Identity Columns (auto numbering/auto sequencing field).