Easytrieve Sort Keys

Major-to-Minor Hierarchy

1
SORT PERSNL TO SORTWRK USING (REGION, BRANCH, DEPT, EMP#)

REGION is the major key: all region 10 rows form one contiguous group before any region 20 row appears—when ascending. Within region 10, BRANCH orders branches. Within equal REGION and BRANCH, DEPT orders departments. EMP# breaks final ties among employees in the same department. Swapping DEPT before REGION would sort departments globally across regions, destroying regional reporting. Always list keys in the same order you want CONTROL breaks and subtotals.

Keys Must Match DEFINE Layout

Sort compares bytes at the offset and length you declared—not at a business meaning you intended. If EMP# is defined 9 5 N but payroll writes employee number at position 1 length 9, sort order follows wrong bytes. After every FILE or copybook change, revalidate key positions against the upstream layout document. Hex dumps of two records that sort wrong often reveal overlapping fields or missing sign nibble on packed decimals.

1
2
3
4
5
6
FILE PERSNL FB(150 1800)
REGION   1  2 N
BRANCH   3  4 N
DEPT     7  3 N
EMP#     9  5 N
NAME    17 20 A

Field Types and Comparison Behavior

Alternate collating sequences may be installed site-wide or via PARM SORT options when available. Test keys with known low, middle, and high values in a ten-record file before trusting production order on A fields with special characters.

Invalid Key Types

• V — varying length fields cannot be sort keys.
• K and M — invalid key types per Broadcom sort documentation.
• Keys 256 bytes or longer — rejected.
• Undefined or misspelled field names — compile errors.

If you need to sort on variable text, copy into a fixed-length W field in BEFORE procedure, SELECT the record, and sort on the W copy—keeping within length limits.

SEQUENCE Keys on Reports

1
2
3
4
REPORT R1
  SEQUENCE REGION BRANCH PAY-NET D
  CONTROL REGION BRANCH
  LINE REGION BRANCH EMP# PAY-NET

SEQUENCE uses the same major-to-minor and D suffix rules as USING. Keys need not appear on LINE. Spool records contain only fields the report captured at PRINT—ensure PRINT happens after any W fields used as SEQUENCE keys are assigned. SEQUENCE sorts fewer bytes than a full-file SORT when LINE lists only a subset of the input record.

Choosing Good Keys

1. Start from the report or file consumer—what order do they need?
2. Match CONTROL break fields when reports group data.
3. Prefer stable unique tie-breakers (EMP#, transaction ID) as minor keys.
4. Avoid sorting on derived fields unless computed before PRINT or in BEFORE.
5. Minimize key count—each extra key costs compare overhead.

Keys and Duplicate Detection

Sort keys define order; they do not deduplicate. Equal keys may appear on many consecutive rows. After sorting, JOB logic can test DUPLICATE, FIRST-DUP, and LAST-DUP when comparing adjacent records on the same key fields—see the Duplicate handling page. Choose keys that reflect business identity (account plus date) before writing duplicate suppression.

Common Sort Key Mistakes

• Minor key listed before major key in USING.
• Sorting on redefined overlapping fields.
• Using display-formatted zoned fields when packed source exists.
• CONTROL omits a key present in USING.
• SEQUENCE on a field never populated at PRINT time.

Explain It Like I'm Five

Sort keys are the rules for lining up kids for a photo. First rule: tallest to shortest. Second rule: if same height, alphabetical first name. The first rule is major; the second breaks ties. If you use the wrong measurement (shoe size instead of height), the line looks neat but makes no sense—that is a bad key definition.

Exercises

1. Write USING with three keys and explain each tie-break level.
2. Map DEFINE offsets for keys on a sample FILE layout.
3. Design CONTROL to match your USING list.
4. List two invalid key types and why they fail.
5. Contrast SEQUENCE keys with full-record SORT keys.