Describing database files using DDS

When you describe a database file using DDS, you can describe information at the file, record format, join, field, key, and select/omit levels:

• File level DDS give the system information about the entire file. For example, you can specify whether all the key field values in the file must be unique.
• Record format level DDS give the system information about a specific record format in the file. For example, when you describe a logical file record format, you can specify the physical file that it is based on.
• Join level DDS give the system information about physical files used in a join logical file. For example, you can specify how to join two physical files.
• Field level DDS give the system information about individual fields in the record format. For example, you can specify the name and attributes of each field.
• Key field level DDS give the system information about the key fields for the file. For example, you can specify which fields in the record format are to be used as key fields.
• Select/omit field level DDS give the system information about which records are to be returned to the program when processing the file. Select/omit specifications apply to logical files only.

Example: Describing a physical file using DDS

The DDS for a physical file must be in the following order ( Figure 1):

(1)

File level entries (optional). The UNIQUE keyword is used to indicate that the value of the key field in each record in the file must be unique. Duplicate key values are not allowed in this file.

(2)

Record format level entries. The record format name is specified, along with an optional text description.

(3)

Field level entries. The field names and field lengths are specified, along with an optional text description for each field.

(4)

Key field level entries (optional). The field names used as key fields are specified.

(5)

Comment (optional).

Figure 1. DDS for a Physical File (ORDHDRP)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
      A* ORDER HEADER FILE (ORDHDRP)
      A  (5)
      A                                 (1)  UNIQUE
      A      (2) R ORDHDR                    TEXT('Order header record')
      A        (3) CUST           5   0      TEXT('Customer number')
      A            ORDER          5   0      TEXT('Order number')
      A            .
      A            .
      A            .
      A          K CUST
      A    (4)   K ORDER

The following example shows a physical file ORDHDRP (an order header file), which has an arrival sequence access path without key fields specified, and the DDS necessary to describe that file.

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A* ORDER HEADER FILE (ORDHDRP)
     A          R ORDHDR                    TEXT('Order header record')
     A            CUST           5   0      TEXT('Customer Number')
     A            ORDER          5   0      TEXT('Order Number')
     A            ORDATE         6   0      TEXT('Order Date')
     A            CUSORD        15   0      TEXT('Customer Order No.')
     A            SHPVIA        15          TEXT('Shipping Instr')
     A            ORDSTS         1          TEXT('Order Status')
     A            OPRNME        10          TEXT('Operator Name')
     A            ORDAMT         9   2      TEXT('Order Amount')
     A            CUTYPE         1          TEXT('Customer Type')
     A            INVNBR         5   0      TEXT('Invoice Number')
     A            PRTDAT         6   0      TEXT('Printed Date')
     A            SEQNBR         5   0      TEXT('Sequence Number')
     A            OPNSTS         1          TEXT('Open Status')
     A            LINES          3   0      TEXT('Order Lines')
     A            ACTMTH         2   0      TEXT('Accounting Month')
     A            ACTYR          2   0      TEXT('Accounting Year')
     A            STATE          2          TEXT('State')
     A

The R in position 17 indicates that a record format is being defined. The record format name ORDHDR is specified in positions 19 through 28.

You make no entry in position 17 when you are describing a field; a blank in position 17 along with a name in positions 19 through 28 indicates a field name.

The data type is specified in position 35. The valid data types are:

Entry

Meaning

A

Character

P

Packed decimal

S

Zoned decimal

B

Binary

F

Floating point

H

Hexadecimal

L

Date

T

Time

Z

Timestamp

Notes:

1. For double-byte character set (DBCS) data types, see Appendix B, "Double-Byte Character Set (DBCS) Considerations".

2. The AS/400 system performs arithmetic operations more efficiently for packed decimal than for zoned decimal.

3. Some high-level languages do not support floating-point data.

4. Some special considerations that apply when you are using floating-point fields are:
   • The precision associated with a floating-point field is a function of the number of bits (single or double precision) and the internal representation of the floating-point value. This translates into the number of decimal digits supported in the significant and the maximum values that can be represented in the floating-point field.
   • When a floating-point field is defined with fewer digits than supported by the precision specified, that length is only a presentation length and has no effect on the precision used for internal calculations.
   • Although floating-point numbers are accurate to 7 (single) or 15 (double) decimal digits of precision, you can specify up to 9 or 17 digits. You can use the extra digits to uniquely establish the internal bit pattern in the internal floating-point format so identical results are obtained when a floating-point number in internal format is converted to decimal and back again to internal format.

If the data type (position 35) is not specified, the decimal positions entry is used to determine the data type. If the decimal positions (positions 36 through 37) are blank, the data type is assumed to be character (A); if these positions contain a number 0 through 31, the data type is assumed to be packed decimal (P).

The length of the field is specified in positions 30 through 34, and the number of decimal positions (for numeric fields) is specified in positions 36 and 37. If a packed or zoned decimal field is to be used in a high-level language program, the field length must be limited to the length allowed by the high-level language you are using. The length is not the length of the field in storage but the number of digits or characters specified externally from storage. For example, a 5-digit packed decimal field has a length of 5 specified in DDS, but it uses only 3 bytes of storage.

Character or hexadecimal data can be defined as variable length by specifying the VARLEN field level keyword. Generally you would use variable length fields, for example, as an employee name within a database. Names usually can be stored in a 30-byte field; however, there are times when you need 100 bytes to store a very long name. If you always define the field as 100 bytes, you waste storage. If you always define the field as 30 bytes, some names are truncated.

You can use the DDS VARLEN keyword to define a character field as variable length. You can define this field as:

• Variable-length with no allocated length. This allows the field to be stored using only the number of bytes equal to the data (plus two bytes per field for the length value and a few overhead bytes per record). However, performance might be affected because all data is stored in the variable portion of the file, which requires two disk read operations to retrieve.
• Variable-length with an allocated length equal to the most likely size of the data. This allows most field data to be stored in the fixed portion of the file and minimizes unused storage allocations common with fixed-length field definitions. Only one read operation is required to retrieve field data with a length less than the allocated field length. Field data with a length greater than the allocated length is stored in the variable portion of the file and requires two read operations to retrieve the data.

Example: Describing a logical file using DDS

The DDS for a logical file must be in the following order ( Figure 2):

(1)

File level entries (optional). In this example, the UNIQUE keyword indicates that for this file the key value for each record must be unique; no duplicate key values are allowed.

For each record format:

(2)

Record format level entries. In this example, the record format name, the associated physical file, and an optional text description are specified.

(3)

Field level entries (optional). In this example, each field name used in the record format is specified.

(4)

Key field level entries (optional). In this example, the Order field is used as a key field.

(5)

Select/omit field level entries (optional). In this example, all records whose Opnsts field contains a value of N are omitted from the file's access path. That is, programs reading records from this file will never see a record whose Opnsts field contains an N value.

(6)

Comment.

Figure 2. DDS for a Simple Logical File (ORDHDRL)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
      A* ORDER HEADER FILE (ORDHDRP)
      A   (6)
      A                                 (1)  UNIQUE
      A     (2)  R ORDHDR                    PFILE(ORDHDRP)
      A       (3)  ORDER                     TEXT('Order number')
      A            CUST                      TEXT('Customer number')
      A            .
      A            .
      A            .
      A     (4)  K ORDER
      A          O OPNSTS              (5)   CMP(EQ 'N')
      A          S                           ALL

A logical file must be created after all physical files on which it is based are created. The PFILE keyword in the previous example is used to specify the physical file or files on which the logical file is based.

Record formats in a logical file can be:

• A new record format based on fields from a physical file
• The same record format as in a previously described physical or logical file (see Sharing existing record format descriptions in a database file)

Fields in the logical file record format must either appear in the record format of at least one of the physical files or be derived from the fields of the physical files on which the logical file is based.

For more information about describing logical files, see "Setting Up Logical Files".

Additional field definition functions you can describe with DDS

You can describe additional information about the fields in the physical and logical file record formats with function keywords (positions 45 through 80 on the DDS Form). Some of the things you can specify include:

• Validity checking keywords to verify that the field data meets your standards. For example, you can describe a field to have a valid range of 500 to 900. (This checking is done only when data is typed on a keyboard to the display.)
• Editing keywords to control how a field should be displayed or printed. For example, you can use the EDTCDE(Y) keyword to specify that a date field is to appear as MM/DD/YY. The EDTCDE and EDTWRD keywords can be used to control editing. (This editing is done only when used in a display or printer file.)
• Documentation, heading, and name control keywords to control the description and name of a field. For example, you can use the TEXT keyword to document a description of each field. This text description is included in your compiler list to better document the files used in your program. The TEXT and COLHDG keywords control text and column-heading definitions. The ALIAS keyword can be used to provide a more descriptive name for a field. The alias, or alternative name, is used in a program (if the high-level language supports alias names).
• Content and default value keywords to control the null content and default data for a field. The ALWNULL keyword specifies whether a null value is allowed in the field. If ALWNULL is used, the default value of the field is null. If ALWNULL is not present at the field level, the null value is not allowed, character and hexadecimal fields default to blanks, and numeric fields default to zeros, unless the DFT (default) keyword is used to specify a different value.

Using existing field descriptions and field reference files to describe a database file

If a field was already described in an existing file, and you want to use that field description in a new file you are setting up, you can request the system to copy that description into your new file description. The DDS keywords REF and REFFLD allow you to refer to a field description in an existing file. This helps reduce the effort of coding DDS statements. It also helps ensure that the field attributes are used consistently in all files that use the field.

In addition, you can create a physical file for the sole purpose of using its field descriptions. That is, the file does not contain data; it is used only as a reference for the field descriptions for other files. This type of file is known as a field reference file. A field reference file is a physical file containing no data, just field descriptions.

You can use a field reference file to simplify record format descriptions and to ensure field descriptions are used consistently. You can define all the fields you need for an application or any group of files in a field reference file. You can create a field reference file using DDS and the Create Physical File (CRTPF) command.

After the field reference file is created, you can build physical file record formats from this file without describing the characteristics of each field in each file. When you build physical files, all you need to do is refer to the field reference file (using the REF and REFFLD keywords) and specify any changes. Any changes to the field descriptions and keywords specified in your new file override the descriptions in the field reference file.

In the following example, a field reference file named DSTREFP is created for distribution applications. Figure 3 shows the DDS needed to describe DSTREFP.

Figure 3. DDS for a Field Reference File (DSTREFP)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A* FIELD REFERENCE FILE (DSTREFP)
     A          R DSTREF                    TEXT('Field reference file')
     A
     A* FIELDS DEFINED BY CUSTOMER MASTER RECORD (CUSMST)
     A            CUST           5  0       TEXT('Customer numbers')
     A                                      COLHDG('CUSTOMER' 'NUMBER')
     A            NAME          20          TEXT('Customer name')
     A            ADDR          20          TEXT('Customer address')
     A
     A            CITY          20          TEXT('Customer city')
     A
     A            STATE          2          TEXT('State abbreviation')
     A                                      CHECK(MF)
     A            CRECHK         1          TEXT('Credit check')
     A                                      VALUES('Y' 'N')
     A            SEARCH         6  0       TEXT('Customer name search')
     A                                      COLHDG('SEARCH CODE')
     A            ZIP            5  0       TEXT('Zip code')
     A                                      CHECK(MF)
     A            CUTYPE         15         COLHDG('CUSTOMER' 'TYPE')
     A                                      RANGE(1 5)
     A
     A* FIELDS DEFINED BY ITEM MASTER RECORD (ITMAST)
     A            ITEM           5          TEXT('Item number')
     A                                      COLHDG('ITEM' 'NUMBER')
     A                                      CHECK(M10)
     A            DESCRP        18          TEXT('Item description')
     A            PRICE          5  2       TEXT('Price per unit')
     A                                      EDTCDE(J)
     A                                      CMP(GT 0)
     A                                      COLHDG('PRICE')
     A            ONHAND         5  0       TEXT('On hand quantity')
     A                                      EDTCDE(Z)
     A                                      CMP(GE 0)
     A                                      COLHDG('ON HAND')
     A            WHSLOC         3          TEXT('Warehouse location')
     A                                      CHECK(MF)
     A                                      COLHDG('BIN NO')
     A            ALLOC     R               REFFLD(ONHAND *SRC)
     A                                      TEXT('Allocated quantity')
     A                                      CMP(GE 0)
     A                                      COLHDG('ALLOCATED')
     A
     A* FIELDS DEFINED BY ORDER HEADER RECORD (ORDHDR)
     A            ORDER          5  0       TEXT('Order number')
     A                                      COLHDG('ORDER' 'NUMBER')
     A            ORDATE         6  0       TEXT('Order date')
     A                                      EDTCDE(Y)
     A                                      COLHDG('DATE' 'ORDERED')
     A            CUSORD        15          TEXT('Cust purchase ord no.')
     A                                      COLHDG('P.O.' 'NUMBER')
     A            SHPVIA        15          TEXT('Shipping instructions')
     A            ORDSTS         1          TEXT('Order status code')
     A                                      COLHDG('ORDER' 'STATUS')
     A            OPRNME    R               REFFLD(NAME *SRC)
     A                                      TEXT('Operator name')
     A                                      COLHDG('OPERATOR NAME')
     A            ORDAMT         9  2       TEXT('Total order value')
     A                                      COLHDG('ORDER' 'AMOUNT')
     A

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A            INVNBR         5  0       TEXT('Invoice number')
     A                                      COLHDG('INVOICE' 'NUMBER')
     A            PRTDAT         6  0       EDTCDE(Y)
     A                                      COLHDG('PRINTED' 'DATE')
     A            SEQNBR         5  0       TEXT('Sequence number')
     A                                      COLHDG('SEQ' 'NUMBER')
     A            OPNSTS         1          TEXT('Open status')
     A                                      COLHDG('OPEN' 'STATUS')
     A            LINES          3  0       TEXT('Lines on invoice')
     A                                      COLHDG('TOTAL' 'LINES')
     A            ACTMTH         2  0       TEXT('Accounting month')
     A                                      COLHDG('ACCT' 'MONTH')
     A            ACTYR          2  0       TEXT('Accounting year')
     A                                      COLHDG('ACCT' 'YEAR')
     A
     A* FIELDS DEFINED BY ORDER DETAIL/LINE ITEM RECORD (ORDDTL)
     A            LINE           3  0       TEXT('Line no. this item')
     A                                      COLHDG('LINE' 'NO')
     A            QTYORD         3  0       TEXT('Quantity ordered')
     A                                      COLHDG('QTY' 'ORDERED'
     A                                      CMP(GE 0)
     A            EXTENS         6  2       TEXT('Ext of QTYORD x PRICE')
     A                                      EDTCDE(J)
     A                                      COLHDG('EXTENSION')
     A
     A* FIELDS DEFINED BY ACCOUNTS RECEIVABLE
     A            ARBAL          8  2       TEXT('A/R balance due')
     A                                      EDTCDE(J)
     A
     A* WORK AREAS AND OTHER FIELDS THAT OCCUR IN MULTIPLE PROGRAMS
     A            STATUS        12          TEXT('status description')
     A

Assume that the DDS in Figure 3 is entered into a source file FRSOURCE; the member name is DSTREFP. To then create a field reference file, use the Create Physical File (CRTPF) command as follows:

CRTPF FILE(DSTPRODLB/DSTREFP)
      SRCFILE(QGPL/FRSOURCE) MBR(*NONE)
      TEXT('Distribution field reference file')

The parameter MBR(*NONE) tells the system not to add a member to the file (because the field reference file never contains data and therefore does not need a member).

To describe the physical file ORDHDRP by referring to DSTREFP, use the following DDS ( Figure 4):

Figure 4. DDS for a Physical File (ORDHDRP) Built from a Field Reference File

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A* ORDER HEADER FILE (ORDHDRP) - PHYSICAL FILE RECORD DEFINITION
     A                                      REF(DSTREFP)
     A          R ORDHDR                    TEXT('Order header record')
     A            CUST      R
     A            ORDER     R
     A            ORDATE    R
     A            CUSORD    R
     A            SHPVIA    R
     A            ORDSTS    R
     A            OPRNME    R
     A            ORDAMT    R
     A            CUTYPE    R
     A            INVNBR    R
     A            PRTDAT    R
     A            SEQNBR    R
     A            OPNSTS    R
     A            LINES     R
     A            ACTMTH    R
     A            ACTYR     R
     A            STATE     R
     A

The REF keyword (positions 45 through 80) with DSTREFP (the field reference file name) specified indicates the file from which field descriptions are to be used. The R in position 29 of each field indicates that the field description is to be taken from the reference file.

When you create the ORDHDRP file, the system uses the DSTREFP file to determine the attributes of the fields included in the ORDHDR record format. To create the ORDHDRP file, use the Create Physical File (CRTPF) command. Assume that the DDS in Figure 4 was entered into a source file QDDSSRC; the member name is ORDHDRP.

   CRTPF FILE(DSTPRODLB/ORDHDRP)
         TEXT('Order Header physical file')

Using a data dictionary for field reference in a database file

You can use a data dictionary and IDDU as an alternative to using a DDS field reference file. IDDU allows you to define fields in a data dictionary. For more information, see the IDDU Use book.

Sharing existing record format descriptions in a database file

A record format can be described once in either a physical or a logical file (except a join logical file) and can be used by many files. When you describe a new file, you can specify that the record format of an existing file is to be used by the new file. This can help reduce the number of DDS statements that you would normally code to describe a record format in a new file and can save auxiliary storage space.

The file originally describing the record format can be deleted without affecting the files sharing the record format. After the last file using the record format is deleted, the system automatically deletes the record format description.

The following shows the DDS for two files. The first file describes a record format, and the second shares the record format of the first:

Figure 5. DDS for a Logical File (CUSMSTL)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A R RECORD1 PFILE(CUSMSTP) A CUST A NAME A ADDR A SEARCH A K CUST A

Figure 6. DDS for a Logical File (CUSTMSTL1) Sharing a Record Format

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A R RECORD1 PFILE(CUSMSTP) A FORMAT(CUSMSTL) A K NAME A

The example shown in Figure 5 shows file CUSMSTL, in which the fields Cust, Name, Addr, and Search make up the record format. The Cust field is specified as a key field.

The DDS in Figure 6 shows file CUSTMSTL1, in which the FORMAT keyword names CUSMSTL to supply the record format. The record format name must be RECORD1, the same as the record format name shown in Figure 5. Because the files are sharing the same format, both files have fields Cust, Name, Addr, and Search in the record format. In file CUSMSTL1, a different key field, Name is specified.

The following restrictions apply to shared record formats:

• A physical file cannot share the format of a logical file.
• A join logical file cannot share the format of another file, and another file cannot share the format of a join logical file.
• A view cannot share the format of another file, and another file cannot share the format of a view. (In SQL, a view is an alternative representation of data from one or more tables. A view can include all or some of the columns contained in the table or tables on which it is defined.)

If the original record format is changed by deleting all related files and creating the original file and all the related files again, it is changed for all files that share it. If only the file with the original format is deleted and re-created with a new record format, all files previously sharing that file's format continue to use the original format.

If a logical file is defined but no field descriptions are specified and the FORMAT keyword is not specified, the record format of the first physical file (specified first on the PFILE keyword for the logical file) is automatically shared. The record format name specified in the logical file must be the same as the record format name specified in the physical file.

To find out if a file shares a format with another file, use the RCDFMT parameter on the Display Database Relations (DSPDBR) command.

Record format relationships between physical and logical database files

When you change, add, and delete fields with the Change Physical File (CHGPF) command, the following relationships exist between the physical and logical files that share the same record format:

• When you change the length of a field in a physical file, you will also change the length of the logical file's field.
• When you add a field to the physical file, the field is also added to the logical file.
• When you delete a field in the physical file, the field will be deleted from the logical file unless there is another dependency in the DDS, such as a keyed field or a select or omit statement.

Record format sharing limitation with physical and logical database files

A record format can only be shared by 32K objects. Error messages are issued when you reach the limitation. You may encounter this limitation in a circumstance where you are duplicating the same database object multiple times.

Note:

Format sharing is performed for files that are duplicated. The format is shared up to 32,767 times. Beyond that, if a file that shares the format is duplicated, a new format will be created for the duplicated file.