Easytrieve Lookup Tables Design Pattern

Problem the Pattern Solves

Input files are designed for storage efficiency and upstream system contracts. A customer extract might store REGION as 01, 02, or 03 while the business wants NORTHEAST, MIDWEST, and SOUTHWEST on every detail line. Hard-coding IF REGION EQ 01 MOVE NORTHEAST in fifty places creates unmaintainable logic when marketing reorganizes regions. Embedding full descriptions in the input file duplicates data and forces every upstream producer to refresh when descriptions change. The lookup tables pattern keeps codes on the input file and descriptions in a dedicated TABLE loaded once per job.

The pattern also centralizes audit: when an examiner asks why code 07 printed as UNKNOWN, you inspect one TABLE dataset or one INSTREAM block instead of tracing scattered IF chains. Failed lookups become visible through IF NOT table-file branches and optional exception reports listing keys that need table maintenance.

Core Building Blocks

Define a TABLE File in Library

1
2
3
4
5
6
7
8
9
FILE DEPTTAB TABLE 10 DISK
   ARG   3  NUM
   DESC 30  A
 
INSTREAM DEPTTAB
010 ACCOUNTING
020 HUMAN RESOURCES
030 INFORMATION TECHNOLOGY
ENDTABLE

The FILE statement names DEPTTAB and marks it TABLE. The 10 DISK operands follow your shop FILE conventions for table capacity. ARG is three numeric characters matching DEPT-CODE on input; DESC is thirty alphanumeric characters for the printable name. INSTREAM rows follow ARG then DESC layout without commas. ENDTABLE terminates instream data. Rows must be sorted by ARG ascending because SEARCH uses binary search—one out-of-order row causes valid keys to miss.

Decode During JOB Processing

1
2
3
4
5
6
7
8
9
JOB INPUT EMPLOYEE-FILE
   SEARCH DEPTTAB WITH DEPT-CODE GIVING DEPT-NAME
   IF DEPTTAB
      PRINT DETAIL-LINE
   ELSE
      DEPT-NAME = '** INVALID DEPT **'
      PERFORM LOG-DEPT-ERROR
      PRINT DETAIL-LINE
   END-IF

Each employee record triggers SEARCH with DEPT-CODE as the key. GIVING DEPT-NAME copies DESC when found. The IF DEPTTAB test is mandatory in production when business rules require accurate descriptions—never assume GIVING populated DESC on failure. The ELSE branch sets a visible default and optionally PERFORMs an error logger that increments W-DEPT-ERRORS and writes an exception line to a separate FILE.

Instream Versus External Tables

Instream TABLE

Data lives inside the Easytrieve source between INSTREAM and ENDTABLE. Advantages: no separate JCL DD for table data, table travels with program in source control, ideal for ten to two hundred rows that change rarely. Disadvantages: every code addition requires recompile and promote; operations cannot update table without developer access; large tables inflate compile time and listing size.

External TABLE

TABLE rows reside in a sequential dataset referenced at runtime. Advantages: operations refreshes table from a maintenance job without recompiling programs that SEARCH it; one physical table serves multiple reports; easier bulk load from spreadsheet exports. Disadvantages: JCL must allocate table DD correctly; version skew between program and table causes silent NOT FOUND; sort order validation becomes a separate data-quality step before job run.

Multi-Table Lookup Chain

Real records often need several decodes: department, pay grade, work location, employment status. Define one TABLE per domain rather than one wide TABLE with composite keys unless keys are genuinely combined in input. Sequential SEARCH calls keep each table maintainable.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
JOB INPUT PAYROLL-REC
   PERFORM DECODE-ALL-CODES
   PRINT PAY-LINE
 
DECODE-ALL-CODES. PROC
   SEARCH DEPTTAB WITH DEPT-CODE GIVING DEPT-NAME
   IF NOT DEPTTAB
      DEPT-NAME = 'UNKNOWN DEPT'
   END-IF
   SEARCH GRADETAB WITH GRADE-CODE GIVING GRADE-TEXT
   IF NOT GRADETAB
      GRADE-TEXT = 'UNKNOWN GRADE'
   END-IF
   SEARCH LOCTAB WITH LOC-CODE GIVING LOC-NAME
   IF NOT LOCTAB
      LOC-NAME = 'UNKNOWN LOCATION'
   END-IF
END-PROC

DECODE-ALL-CODES. PROC centralizes lookup logic so JOB INPUT stays readable and testers can focus regression on one module. Order SEARCH calls by business priority if early failure should skip later lookups—otherwise independent decodes can run in any sequence.

Shared Lookup PROC Pattern

When five reports decode the same DEPTTAB, extract SEARCH DEPTTAB WITH ... into a library PROC copied via MACRO or a standard include, or duplicate the small PROC in each program with identical field names. Parameter passing uses shared working-storage fields: set WS-DEPT-KEY before PERFORM DECODE-DEPT; read WS-DEPT-NAME after END-PROC. Document the contract in comments so callers do not pass varying-length keys.

Avoid nesting SEARCH inside report BEFORE-LINE hooks unless decode depends on break level fields available only at print time—most lookups belong in JOB before PRINT so detail lines carry full descriptions into report buffers.

Error Handling Strategies

• Default literal: print ** INVALID ** so operators spot bad data on the main report.
• Skip record: GOTO JOB or IF NOT combined with NEXT-RECORD logic to exclude bad rows from totals.
• Exception file: WRITE key and record identifier to ERRFILE for clerical correction.
• Counter summary: increment W-LOOKUP-MISS; print total in JOB footer TITLE for batch sign-off.
• Fail job: rare; use when zero lookup errors is a hard control requirement.

Pick one primary strategy per table and document in job runbook. Mixing silent defaults on one report and hard fails on another confuses parallel test teams validating the same input tape.

Table Maintenance Discipline

External tables need a sort step in the maintenance pipeline verifying ARG order. Duplicate ARG values make binary search pick an arbitrary row—deduplicate in source before load. Version tables with effective dates by either maintaining separate datasets per period or using composite ARG keys that include effective date if the product supports key width. Archive prior table generations for audit replay when regulators ask for historical report reproduction.

Instream tables belong in the same change ticket as program logic. Peer review must verify sort order and no duplicate keys. Consider generating INSTREAM blocks from a CSV tool in build pipeline to eliminate manual transcription errors.

Performance Considerations

Binary search per SEARCH is fast. Cost grows when input volume times table count is huge— ten SEARCH calls on fifty million rows still adds CPU. Consolidate rarely used decodes into one combined pass where business allows. Load external tables once at JOB initiation; avoid reloading within inner loops. Size TABLE max entries realistically in FILE statement to prevent memory waste on small tables.

Compare against sequential search only when documentation explicitly requires it for unsorted maintenance files—in most production patterns, sort the table file once upstream and keep binary SEARCH in the report program.

Pattern Versus Anti-Patterns

Testing Lookup Tables

1. Unit test SEARCH with known key at start, middle, and end of ARG range.
2. Test missing key triggers IF NOT branch and default literal.
3. Test empty TABLE edge case if program allows optional table DD.
4. Parallel run old IF-decode report against new TABLE report on production copy input.
5. Validate exception counter matches manual count of bad keys in test file.

Explain It Like I'm Five

Imagine a sticker chart on the wall: number 1 means Red team, number 2 means Blue team. Your homework paper only has the number 1 written on it. Before you show the paper to mom, you look at the chart and write Red team next to the number. The chart is the lookup table. SEARCH is you finding the right sticker row. IF NOT table-file is when the number on your paper is not on the chart—you write unknown instead of guessing.

Exercises

1. Build INSTREAM DEPTTAB with five departments and decode DEPT-CODE on a ten-record test file.
2. Add GRADETAB and chain two SEARCH calls inside DECODE-ALL-CODES. PROC.
3. Implement exception counter W-BAD-DEPT and print in final TITLE line.
4. Document whether your shop would externalize DEPTTAB and write sample JCL DD.
5. List three input fields that should not use TABLE lookup and explain why SQL or JOIN fits better.