Embedded SQL
SQL written inside a COBOL program, between EXEC SQL and END-EXEC.
Also written EXEC SQL, host variables
Why it matters. Batch programs read and change Db2 data as they go. Embedded SQL is how a program talks to the database.
See it
SQLCA brings in SQLCODE, the result of every SQL statement.
Syntax
EXEC SQL statement END-EXEC EXEC SQL INCLUDE SQLCA END-EXEC EXEC SQL SELECT column INTO :host-var [:indicator] FROM table WHERE condition END-EXEC
- EXEC SQL ... END-EXEC
- Marks the start and end of an SQL statement.
- INTO
- The COBOL field that receives the value.
- :host-var
- A COBOL field used inside SQL. The colon is needed.
- :indicator
- A small number field that shows if a column was NULL.
- INCLUDE SQLCA
- Brings in the communication area that holds SQLCODE.
- WHENEVER
- Sets a default action for an error or no row.
Try it EXEC SQL
- 1
In the program EMBSQL, read the balance of ACCT0001 into the field WS-BAL. Write it as one line, from the table ACCOUNTS. In the file you would split it over lines.
COBOL EXEC SQL SELECT BALANCE INTO :WS-BAL FROM ACCOUNTS WHERE ACCTNO = 'ACCT0001' END-EXEC - 2
Run EMBSQL. It shows the SQLCODE and the balance.
RUNCOBOL 'ZB0001.DEV.COBOL(EMBSQL)'
Remember
- EXEC SQL ... END-EXEC wraps SQL inside COBOL. A precompiler, or the compiler's SQL coprocessor, turns it into calls first.
- A colon marks a COBOL host variable inside SQL: :WS-BAL.
- Check SQLCODE after every EXEC SQL. EXEC SQL INCLUDE SQLCA END-EXEC brings it into the program.
The usual mistake
SELECT BALANCE INTO WS-BAL ...
Without the colon, Db2 reads WS-BAL as a column name. It is a COBOL field.
SELECT BALANCE INTO :WS-BAL ...
On real z/OS
MF/OS reads the SQL as the program runs. Real z/OS first runs the precompiler (or compiles with the SQL coprocessor), which writes a DBRM, then you bind it. Because of this, MF/OS never gives the bind-time errors -805 or -818.
Where can I practise Embedded SQL?
Right here, at ZedBank. You take a junior job at a simulated bank and use Embedded SQL in real work, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- COBOLThe programming language most business logic on the mainframe is written in. Verbose by design, and still running the world's payments.
- BINDPreparing a program's SQL for execution, working out how each statement will access the data and storing the result.
- SQLCODEThe result code DB2 returns after every SQL statement, saying whether it worked and what happened if it did not.
- CursorA mechanism for stepping through a set of rows one at a time, when a query returns more than one result.
- DCLGENA tool that generates COBOL declarations matching a DB2 table, so the program's fields line up with the columns.