While most recent database objects are created by using SQL, many files are still defined by using DDS. The procedure for creating database file definitions with DDS is similar to that of creating an RPG program.

Editor's Note: This article is excerpted from chapter 3 of Programming in ILE RPG, Fifth Edition.

The first step is to use an editor, such as the RDi LPEX editor, to create a source member of definition statements. Most installations use a file named QDDSSRC to store members representing externally described files. The source member type of a physical file member is PF, and the type of a logical file member is LF. The editor automatically provides prompts appropriate to the member type you specify.

Data Description Specifications (DDS) comprise a fixed-format language that you can use to code the source definitions for physical and logical database files as well as for display and printer files. All DDS lines include an A in position 6. An asterisk (*) in position 7 of a DDS source line signals a comment line. You can use comment lines throughout the file definition. At a minimum, you should include a few comment lines at the beginning of each file definition to identify the nature of the file.

In addition to comment lines, DDS includes record format descriptions, which name each record layout (format) within the file; field definition lines, which describe fields within records; and perhaps key specifications to designate which fields are to serve as keys to the file. The particular nature of these specifications depends upon whether you are defining a physical file or a logical file.

DDS extensively uses a variety of keywords, each with a special meaning. Keywords that apply to the file as a whole are called file-level keywords, those that apply to a specific record format within the file are known as record-level keywords, and those that associate only with a specific field are termed field-level keywords.

Although all externally defined files share those general features previously mentioned, the details of a DDS definition depend upon the type of file you are defining. Accordingly, let’s first look at using DDS to define physical files.

DDS for Physical Files

The concept of a physical file object parallels that of an SQL table. In fact, SQL tables are implemented as physical files. When you use DDS to describe the file, the physical file’s source statements define the record format of the file. Physical files can contain only one record format. That means that every record within a physical file must have an identical record layout. The record format is signaled by an R in position 17, and you enter a name for the record format in positions 19–28 (Name++++++).

Following the record format specification, you must enter lines to define each field the record contains. After the field definitions, you optionally can designate a key for the file. A K in position 17 denotes a key field. When you list a key field, its contents determine a sequence in which you can retrieve records from the file. Figure 3.6 shows the DDS code for the Customers file discussed earlier.

Figure 1: Physical file DDS

Let’s look at the details of this definition. First, UNIQUE is a file-level keyword. (All file-level keywords appear at the beginning of the DDS, before any record format specification line.)

UNIQUE stipulates that the file cannot contain records with duplicate key values. When you include this keyword, attempts to write a record to the file with a key value identical to a record already in the file causes the system to generate an error message. Use of UNIQUE is optional—without its use, the system permits records with duplicate key values.

The record format line is next. Note the R in position 17 and the format name, CUSTSREC, left-aligned in positions 19–28. DDS allows record format names (and field names, for that matter) up to 10 characters. DDS source code cannot contain lowercase alphabetic characters, except in a string enclosed in double quotation marks, so you must enter all record format and field names in DDS source as uppercase.

You define the fields of a record on successive lines below the record format line. The field name begins in position 19 (Name++++++). Next, you specify the length of the field, right-adjusted in positions 30–34 (Len++). Numeric field definitions must include a decimal entry (Dc, positions 36–37) to indicate how many decimal places the field includes. You use position 35 to specify the data type. DDS supports the following commonly used data types, discussed earlier:

• A = Character
• S = Zoned (signed) numeric
• P = Packed numeric
• L = Date
• T = Time
• Z = Timestamp

DDS recognizes other data types as well, but these character, numeric, and date data types satisfy most common requirements. When you do not specify a data type in position 35, DDS defaults to A for character fields or P for numeric fields (i.e., those with a decimal positions entry in positions 36–37).

Following the definition of all the fields to appear within the record, you can designate one or more fields as the record key by coding a K in position 17 and specifying the name of the key field in positions 19–28. In the example, CUSTNO is named as the file’s key field. Notice that you must define the key field as part of the record before naming it in the K specification. If you list more than one key line, you are specifying a composite (concatenated) key. For a composite key, list the key fields in order from major to minor. Note that fields need not be adjacent to each other within the record to be key components.

The TEXT keyword entries are optional ways to provide documentation. In the example, TEXT is used with each field to explain what the field represents. You must enclose text comments with apostrophes (') and surround them with parentheses. Although text comments are not required, it makes good sense to include them, especially if your field names are somewhat cryptic. TEXT also can appear as a record-level keyword to document the record format.

Programmers new to DDS are sometimes confused by the fact that the filename is not included within the DDS (except perhaps within a comment line). The filename is determined when you actually compile the DDS. By default, the name of the source member becomes the name of the compiled object, or database file.

DDS for Logical Files

Although you can, in theory, get by using only physical files to define your data, you are just scratching the surface of the IBM i database capabilities until you begin to use logical files. The concepts behind logical files correspond to those of SQL views and indexes.

Recall from the introduction to this chapter that logical files define access paths to data stored in physical files. You can use a logical file to change the retrieval order of records from a file (by changing the designated key field), to restrict user views to a subset of fields or records contained in a physical file, or to combine data stored in two or more separate physical files into one logical file. Although the data is stored in physical files, after you have defined logical files to the system, you can refer to these logical files in RPG programs as though the logical files themselves actually contained records. The advantage of using logical files is that they can provide alternative ways to look at data, including different orders of record access, without redundantly storing the actual data on the system.

A logical file based on a single physical file is called a simple logical file. The method of defining simple logical files is similar to that of defining physical files. You first specify a record format, then stipulate a list of fields (optional), and follow that with one or more (optional) key fields. Because logical files provide views of physical files, you must include the PFILE keyword beginning in position 45 of the Keywords area on the record format line, followed, in parentheses, by the name of the physical file upon which the logical record format is based.

The easiest way to code a simple logical file is to use the same record format name within the logical file as the record format name in the physical file on which the logical file is based. With this method, the system assumes the record layouts of the files are identical. As a result, you do not need to include fields within your logical record description. However, you can still designate one or more fields as a key, and this key does not have to match the key of the physical file. The example in Figure 3.7 shows a logical file based on customer file Customers.

Notice that this DDS contains no field-level entries. With this definition, all the fields defined within the physical file are implicitly included within the logical file. Because the logical file is keyed on last name and then first name, keyed sequential access of this logical file retrieves the customer records in alphabetic order by last name then by first name. Programmers widely use this kind of logical file definition to change the retrieval order of records in a file. Its effects are identical to that of physically sorting file records into a different order, but without the system overhead that a physical sort requires. This logical file is similar to an SQL index, and indeed, SQL can use this logical file as an index to improve the performance of some SQL statements.

Figure 2: Logical file DDS based on Customers file

To restrict the logical file so that it includes only some of the fields from the physical file, give the logical file a record format name different from that of the record format name in the physical file, and then list just those fields to include in the logical file. Only the fields listed are accessible through the logical file. Again, you can designate one or more of these fields to serve as the key to the file. Figure 3.8 illustrates the DDS for such a logical file.

Figure 3: Logical file DDS displaying accessible fields

Notice that you do not need to specify length, type, and decimal positions for the fields in a logical file. These field attributes are already given in the physical file on which the logical file is based.

You can define logical files to include only a subset of the records contained in the physical file by using Select and Omit specifications. You use this feature only if the logical file first contains a key specification. Your specifications base the record inclusion or exclusion on actual data values present in selected fields of the physical file records.

For example, assume you want to include only the male customers from the Customers file. You’d simply designate Cgender as a select field (S in position 17) and then, in position 45, provide the basis for the selection. One way to do this is with the COMP keyword. COMP specifies a comparison between a field’s value and a single given value to serve as the basis of selection or omission. You specify the nature of the comparison by using one of eight relational operators:

• EQ (equal to)
• GT (greater than)
• GE (greater than or equal to)
• LT (less than)
• LE (less than or equal to)
• NE (not equal to)
• NG (not greater than)
• NL (not less than)

Figure 3.9 shows DDS using Select/Omit to create a logical file consisting of a subset of the records in the Customers file. The S in position 17 indicates a Select specification, and the O in position 17 of the next line indicates an optional Omit specification. The DDS simply indicates that the file should select all records with Cgender equal to 'M' and omit all other records.

Figure 4: Logical file DDS using Select and Omit specifications

In addition to COMP, DDS often uses two other keywords to identify selection criteria. The VALUES keyword is similar to COMP, but it allows comparison with multiple values. The RANGE keyword lets you set a range of values to select or omit.

DDS offers many other database capabilities beyond the physical and logical file constructs that you’ve examined here. Variants on logical files, for instance, can be much more complex than the simple examples we’ve illustrated. For example, you can create logical files based on two or more physical files with multiple record formats—with each format based upon a different physical file. The logical file then gives the appearance that the physical files have been merged together.

Another feature, join logical files, joins fields from different physical files into a single record, using a matching field common to the physical files upon which to base the join. The logical file then appears as if the data exists in one file, with one format, when in reality, it has been brought together from several different physical files. You can, however, accomplish many of these same results with an SQL statement, or within an RPG program, without using complex logical files. Most programmers prefer to stick with simple logical files and avoid these more complex constructs. In addition to the keywords in this chapter, the DDS can use several dozen other keywords used with data file definitions. For additional details about these capabilities, see IBM’s online Knowledge Center.

Creating Database Files with DDS

The first step in creating a physical or logical file is to enter the DDS statements into a source member by using an editor. As previously mentioned, it is standard practice to use source file QDDSSRC to store database source members. Also recall that the source type is PF for physical file and LF for logical file.

After you’ve entered your DDS code, you must compile it to create the file as an object on the system. If you are working from a development platform, such as RDi, follow the same procedure to compile a database object that you use to create a program module object. To compile by directly entering a command at a command line, rather than by working through menus or other tools, use commands CRTPF (Create Physical File) and CRTLF (Create Logical File).

If the system encounters syntax errors while trying to create your file, it sends a message indicating that the creation was unsuccessful. Otherwise, the system informs you that the job completed normally and that the database object now exists.

Once the file object exists, you can use it to store data. You can enter data into physical files by using system utilities, by writing values to the file through a program, by copying records to the file from another file, or by using SQL statements.

You must create a physical file before you can create logical files based on that physical file, as failure to do so results in error messages. Also, you must delete all the logical files associated with a physical file before you can delete the physical file.

Be aware of one additional caveat: if you want to change a physical file’s definition after you have stored data in the file, deleting the file deletes the data in the file as well. You can avoid such data loss by using the CHGPF (Change Physical File) command. This command also can accomplish changes to a physical file without deleting the data or dependent logical files.

When you compile an RPG program that uses an externally described file, the file must exist before your program can be compiled successfully. If you change the definition of a physical or logical file after you have compiled a program that uses that file, you must recompile the program before the system will let the program run. This feature, called level checking, prevents you from running a program based on an obsolete or inaccurate definition of a database file.

Next time: Externally Described Printer Files. Can't wait? Want to learn more about Programming in ILE RPG?  Pick up the book in the MC Bookstore today!