Easytrieve ACCESS Statement

When You Need ACCESS

Use ACCESS when program requirements explicitly reference database tables, SQL SELECT, or vendor database verbs—not when a flat file DD suffices. Merge job combining master file with DB2 rate table is classic pattern: READ employee from file, ACCESS rate table or open once at job start, fetch rate by key, compute pay. Inquiry SCREEN showing customer balance from DB2 may ACCESS at screen initiation. Pure sequential sort-report jobs never need ACCESS—avoid complexity until business data leaves file-only realm.

ACCESS Versus FILE and OPEN

Typical Placement in Program Flow

ACCESS often runs once at activity start—before loop reading driver file—or at SCREEN INITIATION PROC before displaying data. Repeated ACCESS inside every record loop wastes connect overhead unless documentation requires per-request scope. Pair with CLOSE or documented disconnect at activity end when connection pooling not automatic. Job restart mid-run may require idempotent ACCESS handling—coordinate with operations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
* Illustrative pattern—verify syntax on your release
JOB INPUT EMPFILE
  PERFORM DB-CONNECT
 
  DO WHILE EOF EQ N
     READ EMPFILE
     PERFORM FETCH-RATE-FROM-DB
     PERFORM CALC-PAY
     PRINT PAY-RPT
  END-DO
 
  PERFORM DB-DISCONNECT
STOP
 
DB-CONNECT. PROC
   ACCESS ...
END-PROC

Field Definitions for Database Columns

DEFINE working fields matching column types—character, decimal, date—as host variables receiving fetch results. Length and scale mismatch causes truncation or SQL conversion errors. Nullable columns need indicator handling per integration guide—separate indicator fields or special null constants. Do not reuse FILE record buffer for DB columns unless layout intentionally unified.

Authorization and Environment

DB2 plans, packages, and GRANT authority live outside Easytrieve source but block runtime ACCESS. Test region IDs may differ from production—ACCESS succeeds in dev and fails in prod with -922 or authorization codes. Document required DB privileges beside ACCESS statement in program readme. JCL may need STEPLIB and DB2 subsystem parameters beyond ordinary batch Easytrieve proc.

Error Handling After ACCESS

Failed ACCESS should not fall through to SQL or GET assuming connection live. DISPLAY SQLCODE or status, increment error counter, ABEND or route to exception when policy requires. Retry logic for timeout differs from permanent auth failure—do not infinite loop ACCESS on bad password. See lifecycle error handling and file functions for status patterns parallel to database return codes.

ACCESS in SCREEN Activities

Online transactions may ACCESS during INITIATION or BEFORE-SCREEN PROC, hold connection for session, disconnect on TERMINATION. Long-held connections affect DB2 thread limits— follow shop standard for pool versus per-transaction ACCESS. AFTER-SCREEN validation may re-query database to confirm update committed.

Performance Considerations

• Open ACCESS once per job when rate table shared across all employees.
• Fetch by key indexed on DB side—avoid full table scan per detail row.
• Batch commit intervals when updating many DB rows—transaction scope per manual.
• Compare DB path cost versus pre-extracted flat file when run window tight.

Common ACCESS Mistakes

• Using ACCESS on site without database option licensed.
• Skipping ACCESS before first SQL or DB GET.
• Column DEFINE mismatch with table DDL.
• No error branch on failed connect.
• ACCESS inside tight loop without caching lookup results.
• Confusing ACCESS with VSAM OPEN—different verbs and status.

Explain It Like I'm Five

FILE and READ are like reading a book on your desk. ACCESS is like calling the library and asking them to bring a special reference book you do not keep at home. You still need to know how to read the pages—DEFINE fields—but first you must connect to the library system. Not every Easytrieve program needs the library; many only read the book already on the desk.

Exercises

1. Explain when FILE alone suffices versus when ACCESS may be needed.
2. Sketch job flow with one ACCESS at start and READ loop on driver file.
3. List three prerequisites outside source code—DB grants JCL plan.
4. Describe error handling if ACCESS fails before loop.
5. Contrast ACCESS placement in batch JOB versus SCREEN activity.