Arranging duplicate keys in an i5/OS database file

Arranging duplicate keys

If you do not specify the Unique (UNIQUE) keyword in data description specifications (DDS), you can specify how the system stores records with duplicate key values. You specify that records with duplicate key values are stored in the access path in one of the following ways:
Last-in-first-out (LIFO). When the LIFO keyword is specified (1), records with duplicate key values are retrieved in LIFO order by the physical sequence of the records. Here is an example of DDS using the LIFO keyword.
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
      A* ORDERP2
      A                                 1  LIFO       A          R ORDER2
      A            .
      A            .
      A            .
      A          K ORDER       A
First-in-first-out (FIFO). If the FIFO keyword is specified, records with duplicate key values are retrieved in FIFO order by the physical sequence of the records.

First-changed-first-out (FCFO). If the FCFO keyword is specified, records with duplicate key values are retrieved in FCFO order by the physical sequence of the keys.

No specific order for duplicate key fields (the default). When the FIFO, FCFO, or LIFO keyword is not specified, no guaranteed order is specified for retrieving records with duplicate keys. No specific order for duplicate key fields allows more access path sharing, which can improve performance.
When a simple- or multiple-format logical file is based on more than one physical file member, records with duplicate key values are read in the order in which the files and members are specified on the DTAMBRS parameter of the Create Logical File (CRTLF) or Add Logical File Member (ADDLFM) command. Examples of logical files with more than one record format can be found in DDS concepts.
The LIFO or FIFO order of records with duplicate key values is not determined by the sequence of the updates made to the contents of the key fields, but only by the physical sequence of the records in the file member. Assume that a physical file has the FIFO keyword specified (records with duplicate keys are in FIFO order), and that the following table shows the order in which records were added to the file.

Order records were added to file Key value

1 A
2 B
3 C
4 C
5 D

The sequence of the access path is FIFO, with ascending key values.

Record number Key value

1 A
2 B
3 C
4 C
5 D

Records 3 and 4, which have duplicate key values, are in FIFO order. That is, because record 3 was added to the file before record 4, it is read before record 4. This would become apparent if the records were read in descending order. This can be done by creating a logical file based on this physical file, with the DESCEND keyword specified in the logical file.
The sequence of the access path is FIFO, with descending key values.

Record number Key value

5 D
3 C
4 C
2 B
1 A

If the key value of physical record 1 is changed to C, the sequence of the access path for the physical file is FIFO, with ascending key values.

Record number Key value

2 B
1 C
3 C
4 C
5 D

Finally, changing to descending order, the new sequence of the access path for the logical file is FIFO, with descending key values.

Record number Key value

5 D
1 C
3 C
4 C
2 B

After the change, record 1 does not appear after record 4, even though the contents of the key field were updated after record 4 was added.
The FCFO order of records with duplicate key values is determined by the sequence of updates made to the contents of the key fields. In the preceding example, after record 1 is changed such that the key value is C, the sequence of the access path is FCFO, with ascending key values only.

Record number Key value

2 B
3 C
4 C
1 C
5 D

For FCFO, the duplicate key ordering can change when the FCFO access path is rebuilt or when a rollback operation is performed. In some cases, your key field can change but the physical key does not change. In these cases, the FCFO ordering does not change, even though the key field has changed. For example, when the index ordering is changed to be based on the absolute value of the key, the FCFO ordering does not change. The physical value of the key does not change even though your key changes from negative to positive. Because the physical key does not change, FCFO ordering does not change.
If the reuse deleted records attribute is specified for a physical file, the duplicate key ordering must be allowed to default or must be FCFO. The reuse deleted records attribute is not allowed for the physical file if either the key ordering for the file is FIFO or LIFO, or if any of the logical files defined over the physical file have duplicate key ordering of FIFO or LIFO.

Parent topic:
Using keyed sequence access paths for database files

Related concepts

Sharing existing access paths between logical files