Arranging key fields in an alternative collating sequence

 

You can arrange key fields that are defined as character fields either in a sequence for EBCDIC characters or in 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 can 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 can 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 information about sorting uppercase and lowercase characters regardless of their case, the QCASE256 table in library QUSRSYS is provided for you.

 

Parent topic:

Using keyed sequence access paths for database files

 

Related concepts

DDS concepts