How To Create Flat File In As400

A physical file is a database file that contains data records. The data records are grouped into physical file members and each member has its own access path to the data. Usually, database files have only one member which, by default, is added to the file when the file is created.

A physical file can contain only one record format. Based on the requirement, we can create two types of physical files.

Flat file
DDS file

What is meant by Flat file?

Flat files are nothing but a physical file with only one column. The length of this column is usually larger than a typical column of any other physical file. This is because each record of a flat file actually contains several columns of an ordinary physical file. We can write, read, update, delete the Flat file.

The object name, the record format name and field name of this flat physical file are the same as specified when creating the physical file.

Create Flat file

We need to use the CRTPF command as below in the command line to create a physical file.

CRTPF FILE(TEST/HISTORY) RCDLEN(100) — CRTPF Command

RCDLEN must be specified, SRCFILE and SRCMBR cannot be specified; RCDLEN is used to specify a fixed record length for the record format. A value ranging from 1 through 32766 bytes can be specified for the record length.

AS/400 actually creates a file named HISTORY in library TEST, a member named HISTORY, and a field of length 100 that is named HISTORY.

What is meant by DDS file?

File-level, record-level, field-level, and key-field-level entries are used to define a physical file with data description specifications.

Entries needs to be in the following order to define a physical file:

File-level entries
Record-level entries
Field-level entries
Key field-level entries

DDS - Data Description Specification File — DDS File

File level entries

File level entries are optional and they give the system information of the entire file. UNIQUE, LIFO, FIFO, FCFO, REF are the keywords used at the file level.

UNIQUE: File level keyword used to specify that records with duplicate key values are not allowed within a member of this physical or logical file.

You can specify whether null key values are to be considered as duplicates using the parameter. Any insertions or additions of new records, or updates to existing records, which might result in a duplicate key, are rejected. The application program issuing the write, or the update operation receives an error message. When a workstation user is using data file utility (DFU), a message is displayed at the workstation. A copy file command that copies records with duplicate keys in this file is not completed.

FIFO: File level keyword used to specify that if records with duplicate key values are retrieved from the same physical or logical file member, they are to be retrieved in a first-in first-out (FIFO) order.

At least one key field must be specified in a file containing the FIFO keyword.

LIFO: File level keyword used to specify that records with duplicate key values from the same physical file member are retrieved in a last-in first-out (LIFO) order.

At least one key field must be specified in a file containing the LIFO keyword.

FCFO: File level keyword used to specify that if records with duplicate key values are retrieved from the same physical or logical file member, the record with the key value that was changed first is the first record retrieved. This is in a first-changed first-out (FCFO) order.

At least one key field must be specified in the file containing the FCFO keyword.

REF: File level keyword to specify the name of the file from which field descriptions are retrieved. REF supplies the field attributes from a previously defined record format. Specify the file name once in the REF keyword instead of on several REFFLD keywords if each field description refers to the same file. To refer to more than one file, use the REFFLD keyword. You can specify the REF keyword only once.

Record format level entries

A "record format" is a named structure that is used for device file I/O. For a PF the record format name is specified along with an optional text description. The record level entries can be FORMAT, TEXT.

Specify the record format in either of the following two ways:

Define a new record format.

Specify field and key-field specifications as required for the new record format.

Share an existing record format.

Use the FORMAT keyword to specify that the operating system is to use a previously defined record format from another physical file. When the FORMAT keyword is used, you must specify key-field level specifications again (if a keyed access path is required) even if they were specified on the existing record format.

FORMAT:

Record-level keyword used to specify that this record format is to share the field specifications for a previously defined record format. The name of the record format you are defining must be the name of the previously defined record format.

The format of the keyword is:

FORMAT (LIB-NAME / FILE-NAME)

The database-file-name parameter is required. It is the name of the physical or logical file from which the previously defined record format is taken.

The library-name is optional. If you do not specify the library-name, the library list (*LIBL) in effect at file creation time is used.

If you specify the FORMAT keyword, you cannot specify field specifications for this record format. Specify key specifications and, if necessary, select/omit specifications if you want them to be in effect for this file. (They can be the same as or different from the previously defined record format.)

The FORMAT keyword is not valid in join logical files and you cannot specify a join logical file as the parameter value on the FORMAT keyword.

If the database file from which you are using the record format is deleted, the record format remains in existence as long as some file is using the record format. For example, RECORD in FILE2 uses the FORMAT keyword to share the specifications of RECORD in FILE1. Both files have been created. If FILE1 is deleted and then re-created with different DDS, RECORD still exists in FILE2. It can be referred to for the original record format by other files using the FORMAT keyword.

TEXT:

The record-level keyword is used to supply a text description of the record format and it is used for documentation purposes only.

The format of this keyword is:

TEXT ('description')

The text must be enclosed in single quotation marks. If the length of the text is greater than 50 positions, only the first 50 characters are used by the high-level language compiler.

Field level entries

The field names and field lengths are specified along with an optional text description for each field. (ALIAS, ALWNULL, CCSID, CHECK, CHKMSGID, CMP, COLHDG, COMP, DATFMT, DATSEP, DFT, EDTCDE, EDTWRD, REFFLD, REFSHIFT, TEXT, TIMEFMT, TIMESEP, VALUES, VARLEN)

ALIAS: Field-level keyword to specify an alternative name for a field. When the program is compiled, the alternative name is brought into the program instead of the DDS field name.

ALWNULL: Field-level keyword to define this field to allow the null value.

CCSID: Field-level keyword to specify a coded character set identifier (CCSID) for character fields. The CCSID keyword is a file- or field-level keyword on physical files, and a field-level keyword on logical files.

CHECK: Field-level keyword to specify validity checking for data.

CHKMSGID: Field-level keyword to identify an error message that is associated with validity checking keywords.

CMP/COMP: Field-level keyword to specify validity checking for the field you are defining when the field is referred to later during display file creation. The COMP keyword is equivalent to the CMP keyword.

COLHDG: Field-level keyword to specify column headings used as a label for this field by text management, the query utility, the data file utility (DFU), and the screen design aid (SDA).

DATFMT: Field-level keyword to specify the format of a date field. This keyword is valid only for date fields (data type L) or for logical-file zoned fields (data type S), packed fields (data type P), or character fields (data type A) whose corresponding physical file fields are date fields (data type L).

DATSEP: Field-level keyword to specify the separator character for a date field. This keyword is valid only for date fields (data type L).

DFT: Field-level keyword to specify a default value for a field.

EDTCDE/EDTWRD: Field-level keyword to specify editing for the field you are defining when the field is referenced later during display or printer file creation. The EDTCDE and EDTWRD keywords do not affect the physical or logical file.

REFFLD: Field-level keyword to refer to a field.

REFSHIFT: Field-level keyword to specify a keyboard shift for a field when the field is referred to in a display file or data file utility (DFU) operation.

TEXT: Field-level keyword to supply a text description (or comment) for the record format or field used for program documentation.

TIMEFMT: Field-level keyword to specify the format of a time field.

This keyword is valid for either time fields (data type T) or zoned fields (data type S) whose corresponding physical file fields are time fields (data type T).

TIMESEP: Field-level keyword to specify the separator character used for a time field. This keyword is valid only for time fields (data type T).

VALUES: You can specify this keyword at the field level, the select/omit-field level, or both.

VARLEN: You can use this field-level keyword to define this field as a variable-length field.

Variable-length fields are useful for improving storage when the data for the field typically fit within a certain length, but can occasionally be longer. Specify the maximum length of the field in positions 30 to 34. You can specify the allocated length (or typical length) in the parameter.

Key field level entries

The field names used as key fields are specified. (DESCEND, SIGNED, ABSVAL, UNSIGNED, ZONE, NOALTSEQ, DIGIT)

DESCEND: Key field-level keyword to specify that the values of this character, hexadecimal, or numeric key field are retrieved in descending sequence.

SIGNED: Key field-level keyword is in effect when sequencing the values associated with this numeric key field, the operating system considers the signs of the values (negative versus positive values).

ABSVAL: You can use this key-field level keyword to direct the operating system to ignore the sign of the field when the system sequences the values associated with this numeric field.

UNSIGNED: Key field-level keyword to specify that numeric fields are sequenced as a string of unsigned binary data. The default values of character, date, time, timestamp, and hexadecimal fields are set to unsigned values.

ZONE: You can use this key field-level keyword to specify that only the zone portion (farthest left 4 bits) of each byte of the key field is used when the operating system is constructing a value associated with this key field. The digit portion is filled with zeros.

NOALTSEQ: Key field-level keyword to specify that the ALTSEQ keyword specified at the file level does not apply to this key field.

If you specify ABSVAL or SIGNED for a key field, NOALTSEQ is automatically in effect, whether the NOALTSEQ keyword is specified for that key field.

DIGIT: Key field-level keyword is in effect, only the digit portion (farthest right 4 bits) of each byte of the key field is used when the system constructs a value associated with this key field. The zone portion is zero-filled.