Easytrieve Table Performance

Where Table Time Goes

Table performance splits into three phases: load, lookup, and maintenance overhead outside Easytrieve. Load happens once per run for external tables or once at compile for INSTREAM. Lookup happens per SEARCH call—cheap individually, expensive in aggregate. Maintenance—sorting reference files, validating order, recompiling INSTREAM—is human and JCL time but prevents lookup failures that cause reruns. Tuning focuses on load memory and lookup multiplication.

Memory Sizing Formula Intuition

Approximate each row as ARG bytes plus DESC bytes plus small overhead. Multiply by row count reserved in max-entries. Sum across all TABLE files in the program. A program with eight external tables at five thousand rows each, fifty-byte average row payload, sits near two megabytes of table data before stacks and buffers—usually fine. The same pattern with five hundred thousand rows per table is not a TABLE problem—it is a SQL problem.

SEARCH CPU: Multiplication Not Logarithm

Binary search on ten thousand rows is at most fourteen compares. Multiply fourteen by three million SEARCH calls and CPU matters. Profile mentally: input records times SEARCH calls per record times log(table size). Reducing calls per record dominates reducing table size when row counts stay in thousands. A program searching twelve tables per employee should ask whether all twelve are needed on every row or only for subsets filtered by IF earlier in JOB logic.

1
2
3
4
5
6
7
8
JOB INPUT PAYROLL
  IF DEPT GT 0
     SEARCH DEPTTAB WITH DEPT GIVING DEPT-NAME
  END-IF
  IF PLAN-CODE NE ' '
     SEARCH PLANTAB WITH PLAN-CODE GIVING PLAN-NAME
  END-IF
  PRINT PAYRPT

Guard expensive lookups behind conditions so zero-department rows skip DEPTTAB SEARCH entirely. Apply same pattern for optional codes, terminated employees, or records failing earlier validation.

Consolidating Versus Splitting Tables

Consolidate when

• Same keys always decode together on every row.
• Maintenance file is produced from one source system.
• One SEARCH replaces three with composite ARG.

Split when

• Different update cadences—zip table monthly, department table yearly.
• Only subset of rows needs optional decode table.
• Composite key would be sparse and waste memory.

INSTREAM Versus External Performance

Anti-Pattern Cost: GET Loop Versus TABLE

Replacing TABLE SEARCH with per-record reference GET can increase CPU orders of magnitude—see Sequential search tutorial. Performance remediation often starts by converting GET-loop decodes to FILE TABLE. Measure elapsed time before and after on representative production file sizes, not ten-row test decks.

Initiation Failures and REGION

Storage abends at JOB start after adding large external tables usually mean undersized REGION or sum of tables exceeding available memory. Increase JCL REGION, reduce max-entries realism, shrink DESC, or move largest table to SQL. Initiation load is synchronous—no SEARCH runs until load completes successfully.

I/O and DISPLAY Overhead

TABLE lookup itself avoids disk per SEARCH after load. DISPLAY on every matched row floods SYSPRINT. WRITE exception records for bad keys are appropriate; logging every successful decode is not. Exception paths should be low volume—if half of rows trigger IF NOT, fix reference data instead of tuning SEARCH.

When TABLE Stops Scaling

1. Row counts approach hundreds of thousands in one table.
2. Multiple programs duplicate same large INSTREAM tables in every load module.
3. Reference data changes hourly and recompile is unacceptable.
4. Lookups need multi-table joins or non-key predicates.
5. Memory abends persist after REGION increases.

Graduate to SQL FILE with indexed Db2 columns. Keep small static decodes in TABLE for speed and simplicity; centralize large shared reference in database.

Performance Testing Checklist

1. Run with production-scale input record count in test region.
2. Count TABLE files and SEARCH calls per input row in source review.
3. Verify max-entries exceeds maintenance file row count.
4. Time initiation separately from JOB loop if external tables are large.
5. Compare elapsed time after removing diagnostic DISPLAY.
6. Regression test after reference file size doubles.

Operational Monitoring

• Track exception report volumes after reference delivery.
• Alert when maintenance row count approaches max-entries ninety percent.
• Document table sizes in run book for capacity planning.
• Schedule sort validation in reference-generation JCL.

Common Performance Mistakes

• Twelve unconditional SEARCH calls on every input row.
• max-entries far above need wasting memory—or below need truncating.
• Hundred-byte DESC when report prints twenty characters.
• GET-loop lookup on medium reference files.
• Duplicating same INSTREAM table in twenty program variants.
• DISPLAY every successful lookup in production.

Explain It Like I'm Five

Table performance is not making each dictionary lookup super fast—it is not asking the dictionary a million questions when you only need a few, and not carrying twenty heavy dictionaries in your backpack when one combined book would do. Easytrieve looks up words quickly; your job design decides how many times you ask.

Exercises

1. Count SEARCH calls per input row in a sample program and estimate total for 250,000 rows.
2. Propose conditional IF guards to eliminate unnecessary lookups.
3. Estimate memory for three tables with given row counts and DESC widths.
4. Write criteria list for when your shop moves a table to Db2.
5. Design performance test comparing GET-loop versus TABLE on same data.