Choosing Physical Tuning Options › Relationship Tuning Options › Additional Sort Options

Additional Sort Options

Standard and natural collating sequence

You can specify either of two collating sequences for sorted relationships:

• Standard collating sequence for sorted relationships orders key fields based on their EBCDIC collating sequence without regard to data type.
• Natural collating sequence for sorted relationships orders key fields based on their data type. This means that negative numeric values will collate lower than positive values.

In the example below, assume that the values are packed or zoned decimal numbers. They are ordered first using the natural collating sequence and then using the standard collating sequence.

Duplicates options

You can specify options for relationships indicating how nonunique occurrences will be logically placed in a sorted relationship. You can specify duplicates first or duplicates last

• Duplicates first— The duplicate entity occurrence will be logically placed in the relationship before the entity occurrence already having that sort key.
• Duplicates last— The duplicate entity occurrence will be logically placed in the relationship after the entity occurrence already having that sort key.

Duplicates not allowed in the non-SQL definition is equivalent to unique.

A relationship can be sorted in either ascending or descending order. The duplicates option for a sorted relationship determines what happens when a user tries to store an entity with a duplicate sort key value.

You can order the sorted relationship entity occurrences with duplicate key values as duplicates first, duplicates last, as discussed above, or in child db-key sequence. This option speeds retrieval by reducing I/O.

Use sorted relationships to simplify programming

Sorted relationships simplify programming effort by allowing the programmer to specify a symbolic key value for storage, retrieval, and positioning of an entity occurrence in the database. By using sorted relationships, the programmer need issue only one DML statement to locate an entity in the database. To locate an entity in a FIRST, LAST, NEXT, or PRIOR relationship, the programmer must walk the relationship by issuing several DML statements.

The diagram below shows the use of sorted relationships to simplify programming.

Unsorted                             Sorted

0200-GETREC.                         0200-GETREC.
  OBTAIN NEXT B WITHIN A-B.            MOVE 'B15' TO B-KEY.
  IF DB-END-OF-SET                     OBTAIN B WITHIN A-B USING B-KEY.
     THEN GO TO 0900-NOREC.            IF DB-REC-NOT-FOUND
  PERFORM IDMS-STATUS.                    THEN GO TO 0900-NOREC.
  IF B-KEY NOT = 'B15'                 PERFORM IDMS-STATUS.
     THEN GO TO 0200-GETREC.

Use sorted relationships to enhance online or batch processing

Since sort routines incur considerable CPU overhead, they are rarely used in online programs. Sorted relationships are therefore useful for sequencing data for online display. They are also useful in the batch environment: a batch program can process sorted input transactions very efficiently in sorted relationships.