How SQL Executes in a Migrated PL/I Application

Table of Contents

1. Overview
2. The SQLCA: how your program learns the result
3. Working with cursors
4. How PL/I data maps to SQL columns
5. Dynamic SQL: PREPARE, EXECUTE, and DESCRIBE
6. Who owns the database connection
7. Concurrency: each thread has its own SQL state
8. A checklist for your programs
9. Summary
10. Related articles

Audience: developers and operations engineers running PL/I applications migrated with Heirloom that use embedded EXEC SQL.

Overview

Your migrated program keeps its embedded EXEC SQL statements unchanged. Underneath, the runtime turns them into standard JDBC calls against a DB2-compatible database, and gives your program back exactly the feedback it expects on the mainframe: an SQLCA to check, cursors to fetch from, and host variables filled with converted values.

This article explains how that execution works and what you interact with — the result/diagnostic block, cursors, data-type conversion, dynamic SQL, who owns the database connection, and how multiple concurrent transactions stay isolated. It sits between two companion articles: the configuration how-to (how to wire up a connection) and the supported-features reference (what statements are covered).

Note: The single most important habit carries over from the mainframe unchanged: check SQLCODE after every SQL statement. Everything below builds on that.

The SQLCA: how your program learns the result

After every SQL operation, the runtime populates the SQL Communication Area (SQLCA) — the same diagnostic block your program reads on the mainframe. Your code branches on it to decide success, end-of-data, or error.

The fields you will use most:

Common SQLCODE values you should handle:

The runtime maps the underlying JDBC/DB2 error into these fields automatically — when the database reports DB2-specific diagnostics, they are extracted into the SQLCA; otherwise a generic SQLCODE is set. You do not change how you read the SQLCA.

Working with cursors

Multi-row result sets work through cursors, with the familiar lifecycle:

EXEC SQL DECLARE C1 CURSOR FOR
         SELECT COL1, COL2 FROM T ORDER BY COL1;
EXEC SQL OPEN C1;

DO WHILE (SQLCODE = 0);
   EXEC SQL FETCH C1 INTO :COL1, :COL2;
   IF SQLCODE = 0 THEN
      /* process the row */;
END;

EXEC SQL CLOSE C1;

Each FETCH sets SQLCODE = 0 when a row was returned and SQLCODE = 100 when there are no more rows — that is how your loop knows to stop.

The runtime supports the cursor features your programs rely on:

• Positioned updates/deletes — declare the cursor FOR UPDATE OF ... and issue UPDATE ... WHERE CURRENT OF C1 inside the fetch loop.
• WITH HOLD — a held cursor survives a commit so you can keep fetching across syncpoints.
• Re-open — a cursor can be closed and opened again to re-run the query.
• Multi-row fetch — fetching into array host variables retrieves a block of rows at once; SQLERRD(3) tells you how many rows actually came back.

How PL/I data maps to SQL columns

Host variables are converted between PL/I storage and SQL column types automatically. The common mappings:

A detail worth knowing: TIMESTAMP values are returned in the 26-character PL/I format yyyy-mm-dd-hh.mm.ss.nnnnnnnnnn. Programs that overlay a timestamp on a fixed-length field depend on this exact width, so it is preserved precisely.

You can also use the standard SQL special registers — CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP — in your statements as usual.

Dynamic SQL: PREPARE, EXECUTE, and DESCRIBE

Statements built at run time are fully supported:

• PREPARE a statement string, then EXECUTE it (optionally USING host variables), or open a cursor over a prepared SELECT.
• EXECUTE IMMEDIATE for one-off statements with no parameters.
• DESCRIBE populates an SQLDA descriptor area so your program can inspect the number, names, and types of the result columns or parameter markers before binding them.
• Stored procedures via CALL, with IN, OUT, and INOUT parameters; OUT values are read back after the call returns.

Who owns the database connection

There are two ways your program gets a connection, and the difference matters for commit behavior:

1. Transaction programs (the common case). The execution platform creates, pools, owns, and enlists the JDBC connection in the current unit of work. Your COMMIT/ROLLBACK happen at the transaction boundary (SYNCPOINT), not as standalone statements. Do not close the connection yourself — it is reused across the programs in a transaction, and closing it breaks that reuse.
2. Standalone programs (no managed transaction). The runtime builds a JDBC connection from your sql.<schema>.* configuration and manages autocommit/isolation directly. Here COMMIT and ROLLBACK apply immediately.

Connections are selected by schema: the runtime uses sql.<schema>.* settings when present and falls back to the default configuration otherwise. See How to Configure DB2 / SQL Connectivity for the property keys, and How Transactions Are Managed for the commit/rollback boundary.

Concurrency: each thread has its own SQL state

A migrated application typically serves many transactions on pooled threads. The runtime keeps the SQL state fully isolated per thread: each thread has its own SQLCA, its own open cursors, and its own connection per schema. Two transactions can therefore declare cursors with the same name and run independent units of work at the same time without interfering with one another. You do not add any locking in your program for this — it is automatic.

(For the bigger picture of how the runtime serializes program logic while letting database I/O overlap, see How Heirloom Handles Concurrency.)

A checklist for your programs

• Check SQLCODE after every statement — and treat 100 as end-of-data, not an error.
• Exit fetch loops on SQLCODE = 100.
• Retry on -911/-913 (deadlock/timeout) rather than failing the unit of work outright.
• Let the platform commit for transaction programs — don't issue standalone COMMITs or close the connection.
• Expect the 26-character timestamp format if you overlay timestamps on fixed-length storage.

Summary

• Your EXEC SQL runs on JDBC but behaves like DB2: you read an SQLCA, drive cursors, and get converted host variables.
• SQLCODE (0 / 100 / negative) plus SQLSTATE and SQLERRD(3) are how your program learns what happened.
• Cursors support positioned updates, WITH HOLD, re-open, and multi-row fetch.
• Data types convert automatically; timestamps keep the 26-character PL/I format.
• Dynamic SQL (PREPARE/EXECUTE/DESCRIBE/CALL) and special registers are supported.
• The execution platform owns and pools the connection for transaction programs — commit at the transaction boundary and never close it yourself.
• SQL state is isolated per thread, so concurrent transactions are safe with no extra locking.

Related articles

• How to Configure DB2 / SQL Connectivity — connection setup and commit/rollback timing.
• Supported SQL Features — the full list of supported EXEC SQL features.
• How Transactions Are Managed (ETP + PL/I Runtime) — the transaction boundary and resource lifecycle.
• How Heirloom Handles Concurrency — why concurrent transactions stay isolated.
• JVM Options & Runtime Configuration — enabling trace for diagnostics.