SQL FILE turns a Db2 table into a file the JOB can read. Easytrieve manages the cursor. You define the columns as fields, and JOB INPUT fetches the next row on each pass. The DBA still binds the plan and grants the authid. A wrong scale on an amount field will compile and print a number that does not tie to the table.
123456789FILE EMPSQL SQL (EMP) EMPNO 1 6 A WORKDEPT 7 3 A SALARY 10 5 P 2 JOB INPUT EMPSQL IF WORKDEPT EQ 'D11' PRINT DEPT-RPT END-IF
The name in parentheses is the table. Field positions and types have to match the select list your release builds for that file. Packed salary with 2 decimals matches a DECIMAL column with scale 2. Scale 0 in the program prints one hundred times the table value.
| Option | What it does |
|---|---|
| SQL (table) | Names the table and asks Easytrieve to manage the cursor |
| UPDATE | Allows column updates, and DELETE or INSERT through the file |
| DEFER | Delays automatic processing until you are ready, per the release rules |
| CODE | Exposes SQL code handling for the file |
FB, CREATE, and PRINTER stay on QSAM and print files. Mixing them onto an SQL FILE is a compile error, not a runtime surprise you want at 2 a.m.
STEPLIB concatenates CBAALOAD and the Db2 SDSNLOAD (names differ by site). The plan or package is attached the way your proc documents, often through the Db2 call attachment the Easytrieve install chose. A missing Db2 library is an S806 on a Db2 module, which is a different failure from an SQLCODE -204 on the table name.
The table is a cabinet in another room. SQL FILE is asking the clerk to walk the cabinet and hand you one folder at a time. JOB INPUT is you saying next folder. You still cannot take a folder home unless the clerk's boss wrote your name on the list.
1. An SQL file is declared with:
2. FILE options that apply to SQL are:
3. UPDATE on an SQL FILE is required to:
4. JOB INPUT on an SQL file:
5. A wrong decimal on a SQL amount column: