Part 6 — DB2/SQL Support¶

6.1 SQL Overview¶

The Heirloom PL/I platform provides comprehensive DB2/SQL support through 18 compiler-level EXEC SQL statement handlers and 22 runtime classes. PL/I programs containing EXEC SQL statements are transpiled to Java code that invokes the Heirloom SQL layer via JDBC.

Quick Start: See Getting Started: 0.4 CICS Example for SQL usage in CICS programs

Complete Reference: See Runtime Reference: 4.4.2 SQL Package for SQL implementation details

6.1.1 SQL Architecture¶

┌────────────────────────────────────────────────────┐
│              SQL/DB2 Emulation Layer                │
│                                                    │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────┐ │
│  │ SQLBuilder   │  │ SQL Commands │  │ SQLCA    │ │
│  │ (context &   │  │              │  │ (comm.   │ │
│  │  execution)  │  │ SELECT       │  │  area)   │ │
│  │              │  │ INSERT       │  │          │ │
│  │              │  │ UPDATE       │  │ SQLCODE  │ │
│  │              │  │ DELETE       │  │ SQLSTATE │ │
│  │              │  │ MERGE        │  │ SQLERRD  │ │
│  └──────────────┘  │ CALL         │  └──────────┘ │
│                    │ PREPARE      │                │
│  ┌──────────────┐  │ COMMIT       │  ┌──────────┐ │
│  │ Cursor Mgmt  │  │ ROLLBACK     │  │ Connec-  │ │
│  │ DECLARE      │  │ TRUNCATE     │  │ tion     │ │
│  │ OPEN         │  │ SET          │  │ Factory  │ │
│  │ FETCH        │  │ GET DIAG     │  │          │ │
│  │ CLOSE        │  └──────────────┘  └──────────┘ │
└────────────────────────────────────────────────────┘

6.1.2 Compiling SQL Programs¶

• Enable with --sql compiler option
• Optionally set --dateformat (EUR, ISO, USA, JIS, LOCAL)
• EXEC SQL statements transpiled to SQLBuilder calls

6.1.3 EXEC SQL Code Generation Pattern¶

/* PL/I Source */
EXEC SQL SELECT NAME, SALARY
    INTO :WS_NAME, :WS_SALARY
    FROM EMPLOYEE
    WHERE EMPNO = :WS_EMPNO;

sqlca = SQLBuilder.getInstance(transenv)
    .select("@SQL:SELECT NAME, SALARY FROM EMPLOYEE WHERE EMPNO = ?")
    .param("WS_EMPNO", ws_empno)
    .into("WS_NAME", ws_name)
    .into("WS_SALARY", ws_salary)
    .execute(this);

See also: Runtime Reference: 4.4.1 QR TCB Lock Management for thread safety during SQL operations

6.2 SQLCA — SQL Communication Area¶

6.2.1 SQLCA Fields¶

6.2.2 SQLCODE Values¶

6.2.3 DSNTIAR — Error Message Formatting¶

Format SQLCA error information into readable text messages.

6.3 SQL Data Manipulation Statements¶

6.3.1 SELECT INTO¶

Single-row queries with host variables.

Features: - INTO clause with host variables (:variable) - Indicator variables for NULL handling - HEX() and other SQL functions in projections - WITH HOLD option - Date format handling (EUR/ISO/USA/JIS/LOCAL)

6.3.2 INSERT¶

Insert rows into a table.

Features: - INSERT INTO ... VALUES with host variables - Multi-row INSERT - INSERT ... SELECT (insert from subquery)

6.3.3 UPDATE¶

Update existing rows.

Features: - UPDATE ... SET ... WHERE with host variables - Positioned UPDATE (WHERE CURRENT OF cursor)

6.3.4 DELETE¶

Delete rows from a table.

Features: - DELETE FROM ... WHERE with host variables - Positioned DELETE (WHERE CURRENT OF cursor)

6.3.5 MERGE¶

Merge (upsert) rows into a table.

Features: - MERGE INTO ... USING ... - WHEN MATCHED THEN UPDATE - WHEN NOT MATCHED THEN INSERT

6.3.6 TRUNCATE¶

Remove all rows from a table (fast delete).

6.3.7 SET¶

Assign a value to a host variable via SQL expression.

EXEC SQL SET :WS_VAR = expression;

6.4 SQL Cursor Operations¶

6.4.1 DECLARE CURSOR¶

Declare a cursor for multi-row retrieval.

Features: - WITH HOLD — Cursor persists across COMMIT - Parameterized cursor queries - Positioned access

6.4.2 OPEN¶

Open a declared cursor for fetching.

Features: - USING clause for dynamic parameter binding

6.4.3 FETCH¶

Retrieve the next row from an open cursor.

Features: - FETCH INTO with host variables - FOR n ROWS (multi-row / array fetch) - Indicator variable support

6.4.4 CLOSE¶

Close an open cursor and release resources.

6.5 Dynamic SQL¶

6.5.1 PREPARE¶

Prepare a dynamically constructed SQL statement.

EXEC SQL PREPARE STMT1 FROM :WS_SQL_TEXT;

6.5.2 EXECUTE¶

Execute a previously prepared statement.

6.5.3 DESCRIBE¶

Retrieve metadata about a prepared statement's result columns.

EXEC SQL DESCRIBE STMT1 INTO :SQLDA;

6.6 Transaction Control¶

6.6.1 COMMIT¶

Commit the current unit of work.

6.6.2 ROLLBACK¶

Roll back the current unit of work.

6.7 Stored Procedures¶

6.7.1 CALL¶

Call a stored procedure with parameters.

Features: - IN, OUT, INOUT parameter directions - Host variable binding - Helper JSON sqlProc configuration for parameter direction

EXEC SQL CALL MY_PROC(:PARAM1, :PARAM2, :PARAM3);

6.8 SQL Diagnostics¶

6.8.1 GET DIAGNOSTICS¶

Retrieve diagnostic information about the last SQL operation.

Features: - CONDITION information (SQLSTATE, MESSAGE_TEXT, etc.) - STATEMENT information (ROW_COUNT, etc.)

EXEC SQL GET DIAGNOSTICS :ROW_CT = ROW_COUNT;
EXEC SQL GET DIAGNOSTICS CONDITION 1
    :MSG_TEXT = MESSAGE_TEXT;

6.9 SQL Date Format Support¶

Configure via --dateformat compiler option.

6.10 SQL Connection Management¶

6.10.1 SQLConnectionFactory¶

JDBC connection management: - Connection pooling configuration - Multiple datasource support - Transaction/connection lifecycle management - Batch vs. CICS connection strategies

6.10.2 LoggableStatement¶

SQL statement logging and diagnostics: - Full SQL text logging with parameter values - Execution timing - Error diagnostics