Easytrieve ETL Processing Projects

ETL Phases Mapped to Easytrieve

Extract Patterns

Full extract

Nightly copy of entire customer master to STAGE—file copy utility pattern with extract timestamp in header. Downstream transform assumes complete snapshot; deletes in source appear as missing keys in compare reports.

Delta extract

Source application writes change file with action code I/U/D. Extract JOB INPUT delta; transform applies upsert semantics on mart—UPDATE indexed mart file or rewrite partition sequential per design. Delta ETL reduces volume but demands strict change sequencing.

1
2
3
4
JOB INPUT DELTA-FILE
  ADD 1 TO EXTRACT-COUNT
  WRITE STAGE-FILE
END-JOB

Transform Patterns

TRANSFORM-RECORD PERFORM chain: VALIDATE-EXTRACT, ENRICH-CUSTOMER via READ CUSTOMER-MASTER keyed by ID, SEARCH PRODUCT-TABLE for hierarchy, COMPUTE extended amount, MAP status codes. Reject records WRITE REJECT-FILE; good records WRITE WORK-FILE. Enrichment READ failures set VAL-STATUS 41—missing dimension— common ETL data quality issue tracked in daily exception report.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
TRANSFORM-RECORD. PROC
  PERFORM VALIDATE-EXTRACT
  IF VAL-STATUS NE ZERO
     EXIT
  END-IF
  MOVE CUST-ID TO MAST-KEY
  READ CUSTOMER-MASTER
  IF EOF CUSTOMER-MASTER
     MOVE 41 TO VAL-STATUS
     EXIT
  END-IF
  MOVE MAST-REGION TO OUT-REGION
  MOVE TRAN-AMT TO OUT-AMT
  COMPUTE OUT-EXT-AMT = OUT-AMT * OUT-QTY
END-PROC

Aggregation Before Load

Fact loads sometimes need pre-aggregation—sales by store by day. SORT WORK by STORE and DATE, then JOB INPUT with CONTROL STORE DATE summing AMOUNT in REPORT—or procedural accumulation in PROC writing one output row per break. Control break processing pattern applies. Choose REPORT SUM when listing matches load layout; choose procedural when load format differs from printable detail.

Load Targets

Easytrieve loads flat files; loading DB2 directly may use DATA LOAD after file write or SQL interface where licensed. Most mainframe ETL ends at sequential mart files ingested by other processes.

Staging Discipline

1. STAGE retains raw extract immutable for audit—do not transform in place on production source.
2. Name STAGE GDG generations for seven-day retention minimum.
3. Trailer on STAGE with EXTRACT-COUNT and source hash.
4. Transform reruns read STAGE without re-extracting—saves production file contention.
5. Separate JCL steps for extract versus transform when extract window is time-critical.

Control and Reconciliation

TERM-JOB DISPLAY EXTRACT-COUNT TRANSFORM-IN LOAD-COUNT REJECT-COUNT. CONTROL-FILE WRITE one summary record per run for operations dashboard. Compare to source application control report—penny and row reconciliation. Mismatch triggers error-handling hard stop before BI consumers read bad mart.

Scheduling and Dependencies

ETL JCL runs after operational close—COND on predecessor job completion. Late extracts cascade delay; document acceptable finish time. Idempotent load design lets rerun overwrite same business-date partition without duplicate facts. Business-date field in every load record—not processing date alone—for correct restatement.

ETL Versus Reporting

ETL produces files consumed by others; reporting produces human-readable listings. Same Easytrieve skills; different outputs. Some jobs combine REPORT exception listing with ETL load in one program—REPORT on REJECT-FILE after transform phase. Keep activities separated in source for clarity even if one compile unit.

Common ETL Mistakes

• No staging—transform reads production twice under failure rerun.
• Missing dimension READ—silent blank attributes instead of reject.
• Load count mismatch ignored when rejects exist.
• Processing date used where business date required for restatement.
• Aggregation without SORT on break keys.

Explain It Like I'm Five

ETL is making lunch boxes for the whole school. Extract is collecting ingredients from the fridge. Transform is cutting sandwiches and peeling fruit the way each class likes. Load is putting boxes in the right classroom cubbies. Staging is a counter where ingredients sit before assembly so you do not open the fridge ten times. The lunch monitor counts boxes to make sure none were dropped.

Exercises

1. Sketch three-phase ETL with STAGE, WORK, and FACT file names.
2. Write TRANSFORM-RECORD with master READ and missing-key error code.
3. Define control total equation EXTRACT = LOAD + REJECT and who signs off.
4. Compare delta versus full extract for customer master—pros and cons.
5. List four items written to CONTROL-FILE after successful load.