Use these positions to specify names of the following:
Refer to the DDS Concepts information for rules to use when specifying record or field names in DDS.
Names must begin in position 19.
You must specify the name type in position 17, unless you are specifying a field name or select/omit AND condition.
Figure 3 shows how to code names for a physical file. Figure 1 and Figure 2 show how to code names for logical files.
When you specify R in position 17, the name specified in positions 19 through 28 is a record format name.
Record format for physical files:
Only one record format name is allowed for a physical file. Specify the record format name in one of two ways:
Record format for simple and multiple format logical files:
You can specify more than one record format name. However, each name must be unique within the file. See the appropriate high-level language manual for exceptions.
Specify the record format name in one of three ways:
The record format name can be the same as the file name specified in the create file command. However, a warning message is sent if the names are not unique. Some high-level language processors, such as RPG, do not allow record format and file names to be the same.
Use the PFILE keyword in conjunction with the record format name to specify the physical files with which the record format is to be associated. A record format can have more than one physical file specified on the PFILE keyword. If no fields are defined and the FORMAT keyword is not specified, the format of the first file specified in the PFILE keyword is used as the format for all the physical files. (This format is used for field attribute references and attribute and name checking.)
Only one record format name can be specified. Specify the record format name as the name of a new record format with field names specified in this logical file. Every field in the record format for a join logical file must be identified by the name in positions 19 through 28. Physical file fields that are parameters of the RENAME, CONCAT, and SST keywords are part of the logical file record format only if you specify the field names elsewhere in the record format.
The JFILE keyword is required at the record level. It specifies the physical files that the record format joins.
When position 17 is left blank, the name specified in positions 19 through 28 is a field name. You cannot specify field names if you specify the FORMAT keyword.
Physical files require that each field be named. These names must be unique within the record format. The field names appear in the physical buffer in the same order that they are specified in the DDS.
If you are describing a simple or multiple format logical file, you can use the record format as it exists in the physical file on which this logical file is based, and you do not have to specify field names.
If you do not use the record format as it exists in the physical file, you must name each field specified in a logical file. In a simple or multiple format logical file, each field name must be unique within the record format and must correspond to a field in the physical file record format. The field name order is the order in which the fields appear to programs using the logical file.
The name you give to a field in a logical file record format is usually the same as the corresponding field name in the physical file record format. If different, the two names must be equated by using the RENAME keyword. A field in a logical file record format can also represent the concatenation of two or more fields from the physical file (see CONCAT (Concatenate) keyword--logical files only). The SST keyword can also be used to describe a substring of a field from the physical file in the logical file format.
When you specify K in position 17, the name specified in positions 19 through 28 is a key field name. It must be one of the field names within the physical file record format. The contents of this field are used to sequence the records for retrieval from the database. Specifying a key is optional. If no key field is specified, the default sequence is arrival sequence (the order that the records were put into the file).
Use key fields (and optionally, select/omit fields) to define a keyed sequence access path for record formats in the logical file member. The logical file member includes the physical file members specified on the DTAMBRS parameter on the Create Logical File (CRTLF) or Add Logical File Member (ADDLFM) commands.
You can change the sequence of records as they are read from the file by specifying a sequencing keyword. The sequencing keywords are ALTSEQ, NOALTSEQ, SIGNED, UNSIGNED, ABSVAL, ZONE, DIGIT, DESCEND, FCFO, FIFO, and LIFO. Refer to the discussion of each of these keywords for more information.
When you do not specify any sequencing keywords for a key field, the default sequence for that key field is ascending order. The default for character, hexadecimal, date, time, and timestamp fields is the UNSIGNED attribute. The default for numeric fields is the SIGNED attribute, except for zoned decimal fields (S specified in position 35) in the following cases:
If you specify more than one record format for a logical file or more than one physical file for the PFILE keyword, you must specify at least one key field for all record formats of that logical file.
A key can have more than one key field. This is called a composite key. In a composite key, specify the key field names in the order of importance (major to minor), and specify each key field name on a separate line.
Figure 4 shows a multiple format logical file with two record formats, one of which uses a composite key. In this example, RECORD1 has a single key field, FIELD1. RECORD2 has a composite key that includes FIELD4 and FIELD5.
Figure 4. Specifying a Multiple Format Logical File with Two Record Formats
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PF1) 00020A FIELD1 00030A FIELD2 00040A FIELD3 00050A K FIELD1 00060A* 00070A R RECORD2 PFILE(PF2) 00080A FIELD4 00090A FIELD5 00100A K FIELD4 00110A K FIELD5 A
If you do not specify a key field for a logical file, the file you are defining has an arrival sequence access path.
The number of fields that make up a key is restricted to 120. The total key length cannot exceed 2000 bytes. (If the FCFO keyword is specified, the total key length cannot exceed 1995 bytes.) The total key length includes the length of each key field. If any of the key fields allow the null value, add 1 byte for each key field that allows the null value. The OS/400 program uses the extra byte to determine whether the key contains the null value. If any of the key fields is variable length, add 2 bytes for each variable-length key field. The OS/400 program uses the extra 2 bytes to store the allocated length of the field.
When you specify more than one record format in a logical file, an additional byte for the first *NONE key field position is required. An additional byte also may be required for each additional key field position. The OS/400 program uses the extra bytes when records from different physical files have duplicate key values.
For example, suppose a key consists of fields named FIELDA, FIELDB, and FIELDC (in that order). The DDS appears as shown in Figure 5.
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A* SAMPLE COMPOSITE KEY (PHYSICAL FILE) 00020A R RECORD 00030A FIELDA 3 0 00040A FIELDB 3 0 00050A FIELDC 3 0 00060A FIELDD 3 0 00070A K FIELDA 00080A K FIELDB 00090A K FIELDC A
The records are sequenced in the following order:
Consider the following file:
Record | FIELDA | FIELDB | FIELDC |
---|---|---|---|
1 | 333 | 99 | 67 |
2 | 444 | 10 | 45 |
3 | 222 | 34 | 23 |
4 | 222 | 12 | 01 |
5 | 222 | 23 | 45 |
6 | 111 | 06 | 89 |
7 | 222 | 23 | 67 |
Assuming ascending sequencing for all fields, the records are retrieved in
this order:
Record | FIELDA | FIELDB | FIELDC |
---|---|---|---|
6 | 111 | 06 | 89 |
4 | 222 | 12 | 01 |
5 | 222 | 23 | 45 |
7 | 222 | 23 | 67 |
3 | 222 | 34 | 23 |
1 | 333 | 99 | 67 |
2 | 444 | 10 | 45 |
The following information applies:
See SIGNED (Signed) keyword for physical and logical files for an example that includes a key field with negative (-) contents.
Special restrictions apply to key field specifications when either FILETYPE(*SRC) is used on the Create Physical File (CRTPF) command or for the Create Source Physical File (CRTSRCPF) command. .
For logical files, the following rules apply to fields that you specify as key fields:
If the field name is specified more than once, the first occurrence is used.
The field name on a CONCAT or RENAME keyword and the associated field name in positions 19 through 28 cannot both be specified as key fields.
The parameter name on the SST keyword is not valid as a key field unless it is defined elsewhere in the logical file format.
If you are concatenating numeric with either character or hexadecimal, you cannot specify the numeric fields as key fields. If you are concatenating zoned decimal and fields of any other numeric data type, you cannot specify the fields of the other data types as key fields.
Figure 6 illustrates which concatenated fields can and cannot be used as key fields.
Figure 6. Correct and Incorrect Concatenated Fields
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PF1) 00020A FLD1 00030A FLD2 00040A Z CONCAT(ZFLD PFLD) 00050A A CONCAT(AFLD NFLD) 00060A K ZFLD 00070A K AFLD A
In physical file PF1, ZFLD is zoned decimal and PFLD is packed decimal. Therefore Z is zoned decimal, and PFLD cannot be used as a key field. ZFLD and Z can be used as key fields but not in the same record format.
In physical file PF1, AFLD is a character field and NFLD is a numeric field. Therefore A is character, and NFLD cannot be used as a key field. AFLD and A can be used as key fields but not in the same record format.
You can specify one or more access path keywords to affect the way the
OS/400 program builds and uses key values. The access path keywords
are:
File Level | Key Field Level |
---|---|
ALTSEQ FCFO FIFO LIFO REFACCPTH UNIQUE |
DESCEND DIGIT SIGNED UNSIGNED ZONE |
Different key fields within a composite key can have different access path keywords.
When you specify more than one record format in a logical file, you must specify at least one key field for every record format in the logical file. It is not necessary to specify the same number of key fields in each key. Also, key fields specified in one record format must have the same field attributes and access path keywords as the corresponding key fields in other record formats in the same logical file. For variable-length key fields, a variable-length key field will not be allowed to align with a fixed-length key field, even if the field types and lengths are the same.
A key is required for every record format so that the logical file members can have a single access path sequencing records of each record format. When records are returned from the various members of the physical file on which the logical file is based, they are merged according to the values of the key fields in the access path for the logical file member.
When records of a logical file member are sequenced, the OS/400 program builds a key value for each record by concatenating the values in its key fields. The key value is then used to build the access path for use by your program.
Each key field in a composite key has a key position. The first key field specified is in position 1, the second key field specified is in position 2, and so on. During I/O operations to a logical file, the OS/400 program compares the key values of the records written to or read from the database. When you create a logical file that has more than one record format (with or without different key fields specified), the OS/400 program performs key position attribute checking. For key position attribute checking to succeed, key fields of different record formats that are in the same key positions must have the same data type, length, decimal positions, and access path keywords specified at the key field level. This ensures a meaningful record sequence from the comparisons made during an I/O operation.
Floating-point fields used as key fields must have the same data type and precision but need not have the same length and decimal positions.
In Figure 7, FIELD1, FLD1, and F1 must have the same attributes, and FIELD2, FLD2, and F2 must have the same attributes. FIELD1, FLD1, and F1 are in key position 1; FIELD2, FLD2, and F2 are in key position 2. One record format can have more key fields than another, and the additional fields do not need key position attribute checking. FLD3 is such a field.
Figure 7. Key Field Attribute Checking
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PF1) 00020A FIELD1 5 0 00030A FIELD2 10 00040A FIELD3 10 00050A K FIELD1 00060A K FIELD2 DESCEND 00070A* 00080A R RECORD2 PFILE(PF2) 00090A FLD1 5 0 00100A FLD2 10 00110A FLD3 20 00120A K FLD1 00130A K FLD2 DESCEND 00140A K FLD3 A* A R RECORD3 PFILE(PF3) A F1 5 0 A F2 10 A F3 30 A K F1 A K F2 DESCEND A A
For examples of key fields in a logical file with more than one record format, refer to Figure 1. In Figure 1, fields named ITEM are specified in each key. For record formats INVFMT and ACTFMT, ITEM is the only key field specified. For record format ORDFMT, a composite key is specified. This composite key includes ITEM, SHPYR, SHPMO, and SHPDA. Each of the fields used in a key must also exist at the field level. Therefore, ITEM must exist in the record format for the physical file INVENTORY so that it can be copied into this logical file for INVFMT. Also, ITEM must exist in the record format for the logical file ACCOUNTL so that it can be copied into this logical file for ACTFMT. ITEM must also exist in physical file ACCOUNTS.
Two conditions occur in which key fields having the same key position should not be compared. The two conditions are:
To avoid unwanted comparisons between key fields, specify *NONE in place of one of them and move the displaced key field to the next key position. The OS/400 program compares the values of key positions before and after *NONE, but retrieves the affected records in the order in which the record formats are specified in the DDS for the logical file.
You can specify *NONE two or more times on the following lines to displace a key field to a key position for which a comparison of key field attributes is relevant to your application.
Figure 8 shows *NONE as the key field.
Figure 8. Specifying *NONE as the Key Field
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A R RECORD1 PFILE(PF1) A FIELD1 6A A FIELD2 4A A FIELD3 10A A K FIELD1 A K FIELD2 A K FIELD3 A A R RECORD2 PFILE(PF2) A FLD1 3A A FLD2 4A A FLD3 12A A K *NONE A K FLD2 A A R RECORD3 PFILE(PF3) A F1 6A A F2 4A A F3 10A A K F1 A K F2 A K F3 A
In Figure 8, the attributes for FIELD2, FLD2, and F2, must be identical. Since you specified *NONE for the first key field of the second record, then FIELD1 and F1 (first key field of the first record and first key field of the third record) must have identical attributes. FIELD3 and F3 must also have identical attributes; there is no corresponding field in the second record format.
Figure 9 through Figure 12 field.
Figure 9. Specifying the Key Field (Example 1)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R CLSHST PFILE(CLSHSTP) 00020A K EMPNBR (1) 00030A K CLSDTE (2) 00040A* 00050A R JOBHST PFILE(JOBHSTP) 00060A K EMPNBR (1) 00070A K JOBDTE (2) A
Record Format | Key Positions |
|
---|---|---|
| (1) | (2) |
CLSHST | EMPNBR | CLSDTE |
JOBHST | EMPNBR | JOBDTE |
In Figure 9, a logical file views records of two physical files through two different record formats: CLSHST (class history) and JOBHST (job history). In the logical file, the records from the two physical files can be merged together and sequenced by employee identification number (EMPNBR) by specifying EMPNBR in key position 1.
All records that have the same key value for EMPNBR pertain to the same employee. To merge and sequence all records for a given employee into a single history of classes and job assignments, specify CLSDTE (date of class) and JOBDTE (date of job assignment) in key position 2 for the two record formats, as shown in Figure 9.
Suppose that the job assignment dates and class dates are the dates
(month/year) that the class or assignment started. Records for three
students are retrieved in the following order:
EMPNBR | CLSDTE | JOBDTE | Description |
---|---|---|---|
1005 | 3/79 |
| Completed class |
1005 | 4/79 |
| Left to begin new job |
1005 |
| 4/79 | Completed job |
1005 | 6/79 |
| Completed class |
1006 |
| 1/79 | Completed job |
1006 |
| 2/79 | Completed job |
1006 | 3/79 |
| Completed class |
1006 | 5/79 |
| Transferred to new location |
1007 |
| 1/79 | Completed job |
1007 |
| 4/79 | Completed job |
1007 |
| 7/79 | Completed job |
1007 | 8/79 |
| Left because of illness |
The above report provides a continuous history for each student.
In Figure 10, another logical file views the same two physical files as in Example 1, but the second record format in the logical file has *NONE specified in key position 2.
Figure 10. Specifying the Key Field (Example 2)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R CLSHST PFILE(CLSHSTP) 00020A K EMPNBR (1) 00030A K CLSDTE (2) 00040A* 00050A* 00060A R JOBHST PFILE(JOBHSTP) 00070A K EMPNBR (1) 00080A K *NONE (2) 00090A K JOBDTE (3) A
Record Format | Key Positions |
|
|
---|---|---|---|
| (1) | (2) | (3) |
CLSHST | EMPNBR | CLSDTE | *NONE |
JOBHST | EMPNBR | *NONE | JOBDTE |
As in Figure 9, all records from the two physical files are first merged
and sequenced together on employee number (EMPNBR). However, the
records for each student are merged and sequenced first on class date (CLSDTE)
and then on job assignment date (JOBDTE). The set of records used for Figure 9 are now retrieved as follows:
EMPNBR | CLSDTE | JOBDTE | Description |
---|---|---|---|
1005 | 3/79 |
| Completed class |
1005 | 4/79 |
| Left to begin new job |
1005 | 6/79 |
| Completed class |
1005 |
| 4/79 | Completed job |
1006 | 3/79 |
| Completed class |
1006 | 5/79 |
| Transferred to new location |
1006 |
| 1/79 | Completed job |
1006 |
| 2/79 | Completed job |
1007 | 8/79 |
| Left because of illness |
1007 |
| 1/79 | Completed job |
1007 |
| 4/79 | Completed job |
1007 |
| 7/79 | Completed job |
When several adjacent record formats have *NONE in the same key position, they form a set, relative to record formats specified before and after them, that functions in sequencing as an individual record format. Key fields specified after *NONE serve to merge and sequence records of the formats within the set. The following example shows how several record formats function as a set.
In Figure 11, consider a logical employee file over five physical files.
Figure 11. Specifying the Key Field (Example 3)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R EMPMST PFILE(EMPMSTP) 00020A K EMPNBR (1) 00030A* 00040A R CLSREG PFILE(CLSREGP) 00050A K EMPNBR (1) 00060A K CLSDTE (2) 00070A* 00080A R CLSHST PFILE(CLSHSTP) 00090A K EMPNBR (1) 00100A K CLSDTE (2) 00110A* 00120A R JOBHST PFILE(JOBHSTP) 00130A K EMPNBR (1) 00140A K *NONE (2) 00150A K JOBDTE (3) 00160A* 00170A R ACTHST PFILE(ACTHSTP) 00180A K EMPNBR (1) 00190A K *NONE (2) 00200A K ACTDTE (3) A
Record Format | Key Positions |
|
|
---|---|---|---|
| (1) | (2) | (3) |
EMPMST | EMPNBR | *NONE | *NONE |
CLSREG | EMPNBR | CLSDTE | *NONE |
CLSHST | EMPNBR | CLSDTE | *NONE |
JOBHST | EMPNBR | *NONE | JOBDTE |
ACTHST | EMPNBR | *NONE | ACTDTE |
The records are merged and sequenced as follows:
Specifying *NONE in the key definitions achieves this sequencing as follows:
The record sequence defined by the previous key specifications is totally dependent on the order in which the formats are specified. For example, if JOBHST had been specified before CLSHST, key position 2 would read:
*NONE, CLSDTE, *NONE, CLSDTE, *NONE
Here, the values of CLSDTE within CLSREG would not have been sequenced with the values of CLSDTE within CLSHST, and JOBDTE would not have been sequenced with ACTDTE.
In Figure 12, assume that an employee has repeated a class. To sequence two records with the same values for EMPNBR and CLSDTE, a third key field, DATE, is specified in record format CLSHST. However, DATE cannot be specified in the next available key position (position 3) because JOBDTE and ACTDTE appear in that position for other formats. If DATE is specified in this position, the attributes of DATE are compared with the attributes of CLSHST and JOBHST, and the key definitions are rejected.
To obtain the sequencing necessary, specify *NONE before DATE, displacing DATE to key position 4.
The DATE field can be shown in position 4 as in Figure 12.
Figure 12. Specifying the Key Field (Example 4)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R EMPMST PFILE(EMPMSTP) 00020A K EMPNBR 00030A* 00040A R CLSREG PFILE(CLSREGP) 00050A K EMPNBR 00060A K CLSDTE 00070A* 00080A R CLSHST PFILE(CLSHSTP) 00090A K EMPNBR 00100A K CLSDTE 00110A K *NONE (1) 00120A K DATE (1) 00130A* 00140A R JOBHST PFILE(JOBHSTP) 00150A K EMPNBR 00160A K *NONE 00170A K JOBDTE 00180A* 00190A R ACTHST PFILE(ACTHSTP) 00200A K EMPNBR 00210A K *NONE 00220A K ACTDTE A
Record Format | Key Positions |
|
|
|
---|---|---|---|---|
| (1) | (2) | (3) | (4) |
EMPMST | EMPNBR | *NONE | *NONE | *NONE |
CLSREG | EMPNBR | CLSDTE | *NONE | *NONE |
CLSHST | EMPNBR | CLSDTE | *NONE | DATE |
JOBHST | EMPNBR | *NONE | JOBDTE | *NONE |
ACTHST | EMPNBR | *NONE | ACTDTE | *NONE |
Specifying DATE in key position 4 enables records from physical file CLSHSTP with identical values for EMPNBR and CLSDTE to be merged and sequenced according to the value for DATE.
Use select or omit fields to tell the OS/400 program how to select or omit records when your program retrieves them using this record format. The only records affected are those from the physical file(s) specified for the PFILE or JFILE keyword for this record format.
The following rules apply to select/omit fields in logical files:
If the field name is specified more than once, the first occurrence is used.
The field name on a CONCAT or RENAME keyword and the associated field name in positions 19 through 28 cannot both be specified as select/omit fields.
The parameter name on the SST keyword is not valid as a select/omit field unless it is defined elsewhere in the logical file record format.
For join logical files, the select/omit field name you specify must be specified at the field level in positions 19 through 28.
When using the select/omit fields, specify either S or O in position 17. By specifying either S or O, the select and omit comparison statements are ORed together. The system treats the ORed select and omit comparison statements independently from one another. That is, if the select or omit comparison condition is met, the record is either selected or omitted. If the condition is not met, the system proceeds to the next comparison.
By specifying a blank in position 17, the select and omit comparison statements are ANDed together. The combined comparisons must be met before the record is selected or omitted. See Figure 13 and Figure 14. In positions 19 through 28, specify a field name whose contents at processing time determine whether the record is to be selected or omitted based on the select/omit keyword specified for this field. The select/omit keywords are COMP, RANGE, and VALUES. The last select/omit specification can be made with the ALL keyword, but a field name is not permitted.
The field must appear in both the physical file record format and the logical file record format. Select/omit statements must follow all field and key field level entries for the record format. You can specify both select and omit for the same record format. The following information applies:
There are limits to the number of select/omit statements you can specify in a single logical file. If you specify many select/omit statements and you cannot create the file, reduce the overhead for the file through the following changes in the specifications, in decreasing order of importance:
You cannot specify a floating-point field as a select/omit field.
It is possible to have an access path with select/omit and process the file in arrival sequence. For example, CPYF can be specified with FROMRCD(1) or the high-level language may not request keyed processing. In this case, the processing is the same as if the DYNSLT keyword had been specified.
Figure 13 shows how to specify the select/omit field using ANDed select statements.
Figure 13. Specifying the Select/Omit Field (Example 1)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PARTS) 00020A PNO 00030A DSC 00040A UPR 00050A QOH 00060A K PNO 00070A S UPR COMP(GT 5.00) 00080A QOH COMP(LT 10) 00090A O ALL A
In Figure 13, records are selected only if they satisfy two select statements: the first statement selects records in which the value of field UPR is greater than 5.00, and the second statement selects records in which the value of field QOH is less than 10. S is not specified in position 17 for field QOH. Therefore, these select statements are ANDed together. For a record to be read by a program, both conditions specified must be true.
Figure 14 shows how to specify the select/omit field using an omit statement ORed with two select statements ANDed together.
Figure 14. Specifying the Select/Omit Field (Example 2)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PARTS) 00020A PNO 00030A DSC 00040A UPR 00050A QOH 00060A K PNO 00070A O DSC COMP(EQ 'HAMMER') 00080A S UPR COMP(GT 5.00) 00090A QOH COMP(LT 10) 00100A O ALL A
In Figure 14, records are supplied to the program if they pass both of the following tests:
Figure 15 shows several ways to specify the same select/omit logic.
Figure 15. Specifying the Select/Omit Field (Example 3)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A S ST COMP(EQ 'NY') 00020A REP COMP(EQ 'JSMITH') (1) 00030A YEAR COMP(LT 78) 00040A O ALL A 00050A O YEAR COMP(GE 78) 00060A S ST COMP(EQ 'NY') (2) 00070A REP COMP(EQ 'JSMITH') 00080A O ALL A 00090A O REP COMP(NE 'JSMITH') 00100A O ST COMP(NE 'NY') (3) 00110A S YEAR COMP(LT 78) 00120A O ALL A
In Figure 15, you want to select all the records before 1978 for a sales representative named JSMITH in the state of New York. There are three ways to code this example.
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.