Easytrieve vs SQL

Two Data Paradigms

SQL thinks in tables, rows, columns, joins, and set operations. Easytrieve FILE thinks in record layouts—fields at byte positions, control breaks on sorted files, detail lines on paper. Mainframe history left both paradigms in production: Db2 for applications, sequential extracts for reporting because batch posting still emits tapes and files.

When SQL Wins

• Many-table joins with referential integrity in Db2.
• Aggregations with GROUP BY, HAVING, window functions.
• Application OLTP: account lookup, order insert, claim update.
• Ad hoc analyst queries without compile cycle.
• Feeding cloud warehouses via SQL exports or replication.
• Reports sourced only from relational data with no flat-file intermediate.

When Easytrieve Wins

• Nightly files already produced by COBOL posting—report from file, not Db2.
• Fixed regulatory layouts archived for decades.
• Control-break printed reports with titles and multiple total levels.
• Teams and schedulers built entirely around file-based batch.
• Performance path: read large sequential once without SQL engine overhead.

Easytrieve Plus SQL FILE (Bridge)

Easytrieve Plus blurs the line by declaring SQL tables as FILEs. Broadcom 11.x automated much SELECT/INTO list construction. You can mix SQL FILE input with REPORT formatting— relational data, report generator output semantics.

Conceptual pattern (simplified—not exact syntax for all releases):

1
2
3
4
5
6
7
8
9
FILE POLICY-SQL SQL TABLE POLICY_TABLE
  POL-NUM      ...
  POL-PREMIUM  ...
 
REPORT PREMIUM-RPT
  LINE POL-NUM POL-PREMIUM
  IF POL-PREMIUM > 5000
    PRINT
  END-IF

POLICY-SQL maps to a Db2 table instead of a DD dataset. Field names correspond to columns. REPORT logic stays Easytrieve. COMMIT behavior and cursor management follow Plus rules documented in Broadcom release notes—important when mixing SQL updates with reporting.

Equivalent SQL for a File Report Mindset

An Easytrieve report that lists high premiums by region might be expressed in SQL as:

1
2
3
4
SELECT REGION, POLICY_NUM, PREMIUM
  FROM POLICY_TABLE
 WHERE PREMIUM > 5000
 ORDER BY REGION, POLICY_NUM;

SQL returns a result set—formatting for print, page breaks, and control totals moves to a report tool, Easytrieve, COBOL, or BI. Easytrieve embeds formatting in the same program that reads data; SQL separates retrieval from presentation unless you use SQL FILE plus REPORT.

Hybrid Architectures

Pattern A: SQL extract → Easytrieve report

Db2 UNLOAD or EXPORT step produces a sequential file; Easytrieve REPORT reads it. SQL owns complex joins; Easytrieve owns layout. Clear separation of concerns.

Pattern B: Easytrieve Plus SQL FILE

Single program reads Db2 directly and prints. Fewer steps; SQL and report logic in one compile unit. Good for moderate complexity.

Pattern C: SQL-only with BI

Warehouse SQL feeds Power BI or similar; Easytrieve retired when print archive no longer required. Common in modernization programs.

Decision Guide

Explain It Like I'm Five

SQL is like asking the library computer which books match your search—it works with cards in a big catalog linked together. Easytrieve is like reading a stack of checkout slips in order and highlighting the ones you care about. If the library only has slips (files), use Easytrieve. If everything is in the computer catalog (Db2), SQL asks questions faster—unless you still need the same printed list format from before.

Exercises

1. Write SQL equivalent to an Easytrieve IF filter on one field.
2. Describe Pattern A hybrid job with step names and DD flow.
3. When would SQL FILE in Plus fail to replace a COBOL-produced flat file?
4. List three risks of duplicating business rules in both SQL and Easytrieve.
5. Research COMMIT in Easytrieve Plus SQL and explain why it matters.