Describing the access path for a database file

An access path describes the order in which records are to be retrieved. Records in a physical or logical file can be retrieved using an arrival sequence access path or a keyed sequence access path. For logical files, you can also select and omit records based on the value of one or more fields in each record.

Arrival sequence access path for database files

The arrival sequence access path is based on the order in which the records arrive and are stored in the file. For reading or updating, records can be accessed:

An externally described file has an arrival sequence access path when no key fields are specified for the file.

An arrival sequence access path is valid only for the following:

Notes:

  1. Arrival sequence is the only processing method that allows a program to use the storage space previously occupied by a deleted record by placing another record in that storage space. This method requires explicit insertion of a record given a relative record number that you provide. Another method, in which the system manages the space created by deleting records, is the reuse deleted records attribute that can be specified for physical files. For more information and tips on using the reuse deleted records attribute, see Database file processing: Reusing Deleted Records. For more information about processing deleted records, see Deleting Database Records.

  2. Through your high-level language, the Display Physical File Member (DSPPFM) command, and the Copy File (CPYF) command, you can process a keyed sequence file in arrival sequence. You can use this function for a physical file, a simple logical file based on one physical file member, or a join logical file.

  3. Through your high-level language, you can process a keyed sequence file directly by relative record number. You can use this function for a physical file, a simple logical file based on one physical file member, or a join logical file.

  4. An arrival sequence access path does not take up any additional storage and is always saved or restored with the file. (Because the arrival sequence access path is nothing more than the physical order of the data as it was stored, when you save the data you save the arrival sequence access path.)

Keyed sequence access path for database files

A keyed sequence access path is based on the contents of the key fields as defined in DDS. This type of access path is updated whenever records are added or deleted, or when records are updated and the contents of a key field is changed. The keyed sequence access path is valid for both physical and logical files. The sequence of the records in the file is defined in DDS when the file is created and is maintained automatically by the system.

Key fields defined as character fields are arranged based on the sequence defined for EBCDIC characters. Key fields defined as numeric fields are arranged based on their algebraic values, unless the UNSIGNED (unsigned value) or ABSVAL (absolute value) DDS keywords are specified for the field. Key fields defined as DBCS are allowed, but are arranged only as single bytes based on their bit representation.

Arranging Key Fields Using an Alternative Collating Sequence

Keyed fields that are defined as character fields can be arranged based either on the sequence for EBCDIC characters or on an alternative collating sequence. Consider the following records:
Record Empname Deptnbr Empnbr
1 Jones, Mary 45 23318
2 Smith, Ron 45 41321
3 JOHNSON, JOHN 53 41322
4 Smith, ROBERT 27 56218
5 JONES, MARTIN 53 62213

If the Empname is the key field and is a character field, using the sequence for EBCDIC characters, the records would be arranged as follows:
Record Empname Deptnbr Empnbr
1 Jones, Mary 45 23318
3 JOHNSON, JOHN 53 41322
5 JONES, MARTIN 53 62213
2 Smith, Ron 45 41321
4 Smith, ROBERT 27 56218

Notice that the EBCDIC sequence causes an unexpected sort order because the lowercase characters are sorted before uppercase characters. Thus, Smith, Ron sorts before Smith, ROBERT. An alternative collating sequence could be used to sort the records when the records were entered using uppercase and lowercase as shown in the following example:
Record Empname Deptnbr Empnbr
3 JOHNSON, JOHN 53 41322
5 JONES, MARTIN 53 62213
1 Jones, Mary 45 23318
4 Smith, ROBERT 27 56218
2 Smith, Ron 45 41321

To use an alternative collating sequence for a character key field, specify the ALTSEQ DDS keyword, and specify the name of the table containing the alternative collating sequence. When setting up a table, each 2-byte position in the table corresponds to a character. To change the order in which a character is sorted, change its 2-digit value to the same value as the character it should be sorted equal to. For more information about the ALTSEQ keyword, see DDS Reference . For information about sorting uppercase and lowercase characters regardless of their case, the QCASE256 table in library QUSRSYS is provided for you.

Arranging Key Fields Using the SRTSEQ Parameter

You can arrange key fields containing character data according to several sorting sequences available with the SRTSEQ parameter. Consider the following records:
Record Empname Deptnbr Empnbr
1 Jones, Marilyn 45 23318
2 Smith, Ron 45 41321
3 JOHNSON, JOHN 53 41322
4 Smith, ROBERT 27 56218
5 JONES, MARTIN 53 62213
6 Jones, Martin 08 29231

If the Empname field is the key field and is a character field, the *HEX sequence (the EBCDIC sequence) arranges the records as follows:
Record Empname Deptnbr Empnbr
1 Jones, Marilyn 45 23318
6 Jones, Martin 08 29231
3 JOHNSON, JOHN 53 41322
5 JONES, MARTIN 53 62213
2 Smith, Ron 45 41321
4 Smith, ROBERT 27 56218

Notice that with the *HEX sequence, all lowercase characters are sorted before the uppercase characters. Thus, Smith, Ron sorts before Smith, ROBERT, and JOHNSON, JOHN sorts between the lowercase and uppercase Jones. You can use the *LANGIDSHR sort sequence to sort records when the records were entered using a mixture of uppercase and lowercase. The *LANGIDSHR sequence, which uses the same collating weight for lowercase and uppercase characters, results in the following:
Record Empname Deptnbr Empnbr
3 JOHNSON, JOHN 53 41322
1 Jones, Marilyn 45 23318
5 JONES, MARTIN 53 62213
6 Jones, Martin 08 29231
4 Smith, ROBERT 27 56218
2 Smith, Ron 45 41321

Notice that with the *LANGIDSHR sequence, the lowercase and uppercase characters are treated as equal. Thus, JONES, MARTIN and Jones, Martin are equal and sort in the same sequence they have in the base file. While this is not incorrect, it would look better in a report if all the lowercase Jones preceded the uppercase JONES. You can use the *LANGIDUNQ sort sequence to sort the records when the records were entered using an inconsistent uppercase and lowercase. The *LANGIDUNQ sequence, which uses different but sequential collating weights for lowercase and uppercase characters, results in the following:
Record Empname Deptnbr Empnbr
3 JOHNSON, JOHN 53 41322
1 Jones, Marilyn 45 23318
6 Jones, Martin 08 29231
5 JONES, MARTIN 53 62213
4 Smith, ROBERT 27 56218
2 Smith, Ron 45 41321

The *LANGIDSHR and *LANGIDUNQ sort sequences exist for every language supported in your system. The LANGID parameter determines which *LANGIDSHR or *LANGIDUNQ sort sequence to use. Use the SRTSEQ parameter to specify the sort sequence and the LANGID parameter to specify the language.

Arranging Key Fields in Ascending or Descending Sequence

Key fields can be arranged in either ascending or descending sequence. Consider the following records:
Record Empnbr Clsnbr Clsnam Cpdate
1 56218 412 Welding I 032188
2 41322 412 Welding I 011388
3 64002 412 Welding I 011388
4 23318 412 Welding I 032188
5 41321 412 Welding I 051888
6 62213 412 Welding I 032188

If the Empnbr field is the key field, the two possibilities for organizing these records are:

When you describe a key field, the default is ascending sequence. However, you can use the DESCEND DDS keyword to specify that you want to arrange a key field in descending sequence.

Using More Than One Key Field

You can use more than one key field to arrange the records in a file. The key fields do not have to use the same sequence. For example, when you use two key fields, one field can use ascending sequence while the other can use descending sequence. Consider the following records:
Record Order Ordate Line Item Qtyord Extens
1 52218 063088 01 88682 425 031875
2 41834 062888 03 42111 30 020550
3 41834 062888 02 61132 4 021700
4 52218 063088 02 40001 62 021700
5 41834 062888 01 00623 50 025000

If the access path uses the Order field, then the Line field as the key fields, both in ascending sequence, the order of the records in the access path is:
Record Order Ordate Line Item Qtyord Extens
5 41834 062888 01 00623 50 025000
3 41834 062888 02 61132 4 021700
2 41834 062888 03 42111 30 020550
1 52218 063088 01 88682 425 031875
4 52218 063088 02 40001 62 021700

If the access path uses the key field Order in ascending sequence, then the Line field in descending sequence, the order of the records in the access path is:
Record Order Ordate Line Item Qtyord Extens
2 41834 062888 03 42111 30 020550
3 41834 062888 02 61132 4 021700
5 41834 062888 01 00623 50 025000
4 52218 063088 02 40001 62 021700
1 52218 063088 01 88682 425 031875

When a record has key fields whose contents are the same as the key field in another record in the same file, then the file is said to have records with duplicate key values. However, the duplication must occur for all key fields for a record if they are to be called duplicate key values. For example, if a record format has two key fields Order and Ordate, duplicate key values occur when the contents of both the Order and Ordate fields are the same in two or more records. These records have duplicate key values:
Order Ordate Line Item Qtyord Extens
41834 062888 03 42111 30 020550
41834 062888 02 61132 04 021700
41834 062888 01 00623 50 025000

Using the Line field as a third key field defines the file so that there are no duplicate keys:
(First Key Field) Order (Second Key Field) Ordate (Third Key Field) Line Item Qtyord Extens
41834 062888 03 42111 30 020550
41834 062888 02 61132 04 021700
41834 062888 01 00623 50 025000

A logical file that has more than one record format can have records with duplicate key values, even though the record formats are based on different physical files. That is, even though the key values come from different record formats, they are considered duplicate key values.

Preventing Duplicate Key Values

The AS/400 database management system allows records with duplicate key values in your files. However, you may want to prevent duplicate key values in some of your files. For example, you can create a file where the key field is defined as the customer number field. In this case, you want the system to ensure that each record in the file has a unique customer number.

You can prevent duplicate key values in your files by specifying the UNIQUE keyword in DDS. With the UNIQUE keyword specified, a record cannot be entered or copied into a file if its key value is the same as the key value of a record already existing in the file. You can also use unique constraints to enforce the integrity of unique keys. For details on the supported constraints, see Controlling the integrity of your database with constraints.

If records with duplicate key values already exist in a physical file, the associated logical file cannot have the UNIQUE keyword specified. If you try to create a logical file with the UNIQUE keyword specified, and the associated physical file contains duplicate key values, the logical file is not created. The system sends you a message stating this and sends you messages (as many as 20) indicating which records contain duplicate key values.

When the UNIQUE keyword is specified for a file, any record added to the file cannot have a key value that duplicates the key value of an existing record in the file, regardless of the file used to add the new record. For example, two logical files LF1 and LF2 are based on the physical file PF1. The UNIQUE keyword is specified for LF1. If you use LF2 to add a record to PF1, you cannot add the record if it causes a duplicate key value in LF1.

If any of the key fields allow null values, null values that are inserted into those fields may or may not cause duplicates depending on how the access path was defined at the time the file was created. The *INCNULL parameter of the UNIQUE keyword indicates that null values are included when determining whether duplicates exist in the unique access path. The *EXCNULL parameter indicates that null values are not included when determining whether duplicate values exist. For more information, see DDS Reference .

The following shows the DDS for a logical file that requires unique key values: 



|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A* ORDER TRANSACTION LOGICAL FILE (ORDFILL)
     A                                      UNIQUE
     A          R ORDHDR                    PFILE(ORDHDRP)
     A          K ORDER
     A
     A          R ORDDTL                    PFILE(ORDDTLP)
     A          K ORDER
     A          K LINE
     A

In this example, the contents of the key fields (the Order field for the ORDHDR record format, and the Order and Line fields for the ORDDTL record format) must be unique whether the record is added through the ORDHDRP file, the ORDDTLP file, or the logical file defined here. With the Line field specified as a second key field in the ORDDTL record format, the same value can exist in the Order key field in both physical files. Because the physical file ORDDTLP has two key fields and the physical file ORDHDRP has only one, the key values in the two files do not conflict.

Arranging Duplicate Keys

If you do not specify the UNIQUE keyword in DDS, you can specify how the system is to store records with duplicate key values, should they occur. You specify that records with duplicate key values are stored in the access path in one of the following ways:

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 on 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 the DDS Reference .

The LIFO or FIFO order of records with duplicate key values is not determined by the sequence of updates made to the contents of the key fields, but solely 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 first-in-first-out order), and that the following 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, ascending key):
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 could 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, descending key):
Record Number Key Value
5 D
3 C
4 C
2 B
1 A

If physical record 1 is changed such that the key value is C, the sequence of the access path for the physical file is (FIFO, ascending key):
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, descending key):
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 example above, after record 1 is changed such that the key value is C, the sequence of the access path (FCFO, ascending key only) is:
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.

Using Existing Access Path Specifications

You can use the DDS keyword REFACCPTH to use another file's access path specifications. When the file is created, the system determines which access path to share. The file using the REFACCPTH keyword does not necessarily share the access path of the file specified in the REFACCPTH keyword. The REFACCPTH keyword is used to simply reduce the number of DDS statements that must be specified. That is, rather than code the key field specifications for the file, you can specify the REFACCPTH keyword. When the file is created, the system copies the key field and select/omit specifications from the file specified on the REFACCPTH keyword to the file being created.

Using floating point fields in database file access paths

The collating sequence for records in a keyed database file depends on the presence of the SIGNED, UNSIGNED, and ABSVAL DDS keywords. For floating-point fields, the sign is the farthest left bit, the exponent is next, and the significant is last. The collating sequence with UNSIGNED specified is:

A floating-point key field with the SIGNED keyword specified, or defaulted to, on the DDS has an algebraic numeric sequence. The collating sequence is negative infinity--real numbers--positive infinity.

A floating-point key field with the ABSVAL keyword specified on the DDS has an absolute value numeric sequence.

The following floating-point collating sequences are observed:

You cannot use not-a-number (*NAN) values in key fields. If you attempt this, and a *NAN value is detected in a key field during file creation, the file is not created.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]