Compiling EXEC SQL Programs (the `--sql` Option)

Table of Contents

1. Overview
2. What --sql does
3. How embedded SQL becomes Java
4. Supported EXEC SQL statements
5. Compile-time settings that affect SQL
6. What you do not configure at compile time
7. Summary
8. Related articles

Audience: developers and build engineers compiling PL/I programs that contain embedded EXEC SQL. Explains what the --sql compiler option does, how embedded SQL is translated to Java, and the compile-time settings that affect it. For runtime database setup, see "How to Configure DB2 / SQL Connectivity."

Overview

Many PL/I programs talk to DB2 through embedded SQL — EXEC SQL … ; blocks mixed into the procedural code. The --sql compiler option tells the Heirloom PL/I compiler to recognize and translate those blocks. Without it, EXEC SQL is not processed; with it, each statement becomes a Java call that runs your SQL over standard JDBC at run time.

This article covers the compile-time side: the option, what the generated Java looks like, how host variables and the SQLCA are handled, and the date-format setting. The run-time side — JDBC datasources, credentials, isolation, commit/rollback — is configuration, and is covered in How to Configure DB2 / SQL Connectivity.

Note: SQL and CICS processing are independent options. A program that uses both embedded SQL and CICS is compiled with --sql --cics together.

What --sql does

--sql

When set, the compiler:

1. Parses each EXEC SQL … ; block against its DB2 SQL grammar (rather than passing it through untouched).
2. Identifies host variables (:VAR), indicator variables (:VAR :IND), and the statement's shape (query, cursor, DML, transaction control).
3. Generates Java that runs the SQL through the runtime's SQL layer and returns status in the SQLCA.

If a particular EXEC SQL statement fails to parse, it is reported in the transpile log (look for SQL PARSE ERROR) — so SQL problems surface at compile time rather than silently disappearing.

How embedded SQL becomes Java

The compiler turns each statement into a call on a fluent SQL builder. The original SQL text is preserved (marked internally with an @SQL: prefix so the runtime can recognize it), and host variables are bound as named parameters or read back into your PL/I variables.

A SELECT … INTO like this:

EXEC SQL
   SELECT STATUS_LI INTO :DCLVAMLIK.STATUS_LI
   FROM   AMT.VAMLIK
   WHERE  LSNR = :DCLVAMLIK.LSNR
END-EXEC;

becomes (illustrative):

sqlca = SQLBuilder.getInstance(transenv)
    .select("@SQL:SELECT STATUS_LI INTO :STATUS_LI FROM AMT.VAMLIK WHERE LSNR = :LSNR")
    .into("STATUS_LI", dclvamlik.status_li)
    .param("LSNR", dclvamlik.lsnr)
    .execute(this);

if (sqlca.sqlcode == 0) {
    dclvamlik.status_li = sqlca.getString("STATUS_LI");
}

The key points to recognize when reading the output:

• :HOSTVAR → named binds. Input host variables become .param(...) / .value(...) binds; INTO targets become .into(...) and are read back with typed getters (getString, getInteger, getBigDecimal, getPLIString, getArray).
• Status comes back in sqlca. Every statement assigns to the SQLCA, so your existing SQLCODE / SQLSTATE checks keep working — sqlca.sqlcode == 0 for success, == 100 for end-of-data on a FETCH.
• The SQL text is preserved. It is embedded as-is (single-lined and escaped for Java), so the SQL you wrote is the SQL that runs.

Indicator variables (NULLs)

Indicator variables are translated into explicit null handling, so PL/I's -1-means-null convention is preserved:

... :DCLVAMLIP.WE_DAT :DCLVAMLIP_IND.IND_WE_DAT ...

• On input, an indicator of -1 sends NULL to the database.
• On output, the indicator is set to -1 when the column comes back NULL, 0 otherwise.

Cursors

Cursor processing is fully modeled: DECLARE CURSOR (including WITH HOLD, sensitivity, FOR FETCH ONLY), OPEN, FETCH … INTO (single row and multi-row FOR n ROWS / ROWSET), and CLOSE each translate to the corresponding builder calls, with row counts available through the SQLCA.

Supported EXEC SQL statements

The compiler has dedicated handlers for the common embedded-SQL statements:

Precompiler-only directives such as INCLUDE and WHENEVER are recognized and handled appropriately rather than emitted as runtime calls. See Supported SQL Features for the published feature list.

Note: The SQL dialect is DB2. DB2-specific constructs — special registers (CURRENT TIMESTAMP, CURRENT SQLID, …), isolation clauses (WITH RR/RS/CS/UR), VALUE(...), window functions, DECLARE GLOBAL TEMPORARY TABLE — are supported by the grammar.

Compile-time settings that affect SQL

Date format (--dateformat)

DB2 formats DATE values according to a date-format setting. Match it to your source system's expectation:

--dateformat ISO

Accepted values: EUR, ISO, USA, JIS, LOCAL. The default is EUR; an unrecognized value falls back to EUR with a warning.

Combine with other options

--sql composes with the strategy and CICS options. A typical online (CICS) program that also uses SQL is built like:

--sql --cics --strategy instance

When --cics is also set, the generated SQL calls participate in the CICS unit-of-work (the transactional context is passed through), so commit/rollback behave correctly under CICS syncpoint. See How Transactions Are Managed.

What you do not configure at compile time

Connection details are run-time configuration, not compiler options:

• the JDBC datasource / URL, user, and password,
• autocommit, isolation level, and XA participation,
• which logical connection a program uses.

These live in your runtime configuration (sql.<conn>.*) and are covered in How to Configure DB2 / SQL Connectivity. The compiler only produces SQL-aware Java; where it connects is decided at deployment.

Summary

• --sql makes the compiler parse and translate embedded EXEC SQL; without it, SQL is not processed.
• Each statement becomes a fluent builder call that preserves your SQL text, binds host variables as named parameters, reads results back into your PL/I variables, and returns status in the SQLCA (so SQLCODE/SQLSTATE keep working).
• Indicator variables preserve PL/I's -1-means-NULL behavior; cursors are fully modeled.
• The dialect is DB2; --dateformat (EUR/ISO/USA/JIS/LOCAL) sets date handling.
• Connectivity is run-time configuration, not a compiler option — see the DB2 connectivity article.

Related articles

• How the Heirloom PL/I Compiler Translates Your Code and Reading Your Generated Java.
• Compiling EXEC CICS Programs (the --cics Option).
• Static vs Instance Strategy.
• How to Configure DB2 / SQL Connectivity for EXEC SQL Programs — the run-time connection setup.
• Supported SQL Features and How Transactions Are Managed.