How to Configure DB2 / SQL Connectivity for EXEC SQL Programs

Table of Contents

1. Overview
2. Configuring a connection
3. How it integrates with EXEC SQL
4. When your changes commit or roll back
5. A quick checklist
6. Summary
7. Related articles

Audience: developers and operations engineers running PL/I programs that use EXEC SQL, migrated to Java with Heirloom.

Overview

PL/I programs that use EXEC SQL keep their embedded SQL after migration. The Heirloom PL/I Runtime implements the SQL layer directly and connects to your database — typically DB2 — through standard JDBC. Your job is to tell the runtime how to reach the database; the SQL statements themselves are unchanged.

This article covers connection configuration (including credentials), how the runtime runs your EXEC SQL, and when your changes are committed or rolled back.

Note: All URLs, datasource names, and credentials below are examples. Replace them with your own. Never commit real credentials to source control — supply them through a secured datasource or properties file at deploy time.

Configuring a connection

Connections live in the sql.<connection>.* property namespace. The implicit connection is named default — what an ordinary EXEC SQL statement uses unless told otherwise.

You connect in one of two ways: a direct JDBC URL (with driver + credentials), or a container datasource (JNDI). Use one or the other.

A typical default connection using a container datasource (credentials managed by the server, so the URL/driver/user/password lines are not needed):

sql.default.autocommit=false
sql.default.datasource=jdbc/MyDataSource
sql.default.isolation=committed
sql.default.readonly=false
sql.xa.datasource=true
sql.log=true
#sql.default.url=jdbc:db2://db2host:50000/MYDB
#sql.default.driver=com.ibm.db2.jcc.DB2Driver
#sql.default.user=<db-user>
#sql.default.password=<db-password>

To connect with a direct URL and credentials instead of a datasource, set the commented lines and drop the datasource:

sql.default.url=jdbc:db2://db2host:50000/MYDB
sql.default.driver=com.ibm.db2.jcc.DB2Driver
sql.default.user=<db-user>
sql.default.password=<db-password>
sql.default.autocommit=false
sql.default.isolation=committed

Datasource vs. URL: prefer a container datasource on an application server — it pools connections and keeps credentials in the server, not the application. The direct URL form is handy for standalone/batch or local testing.

Named connections (multiple databases)

To target more than one database, configure additional named connections and have the program select one. Each name gets its own sql.<name>.* block:

sql.default.datasource   = jdbc/PrimaryDb
sql.reporting.datasource = jdbc/ReportingDb

How it integrates with EXEC SQL

The runtime translates each EXEC SQL statement into JDBC against the configured connection — SELECT / INSERT / UPDATE / DELETE / MERGE, cursors (DECLARE / OPEN / FETCH / CLOSE, which map to JDBC result sets), stored procedure CALL, and dynamic SQL. After each statement the standard SQLCA feedback (SQLCODE / SQLSTATE) is set, exactly as your program expects. The connection is shared for the program's unit of work, and cursors are released when the program completes. (For the full statement list, see Supported SQL Features.)

When your changes commit or roll back

This is the most important behavior to get right. With the recommended setting autocommit=false, each statement is part of a unit of work — nothing is saved on its own. The work is then resolved as follows:

Committed (saved) on success — two ways:

• With SYNCPOINT: when the program issues EXEC CICS SYNCPOINT, the unit of work is committed at that point — your SQL changes and any other transactional resources (e.g. MQ messages put under syncpoint) commit together.
• Without SYNCPOINT: if the program never issues an explicit syncpoint, the accumulated SQL work is committed implicitly at the normal, successful end of the transaction.

Rolled back (discarded) on failure — two ways:

• With SYNCPOINT ROLLBACK: EXEC CICS SYNCPOINT ROLLBACK explicitly discards the work done since the last commit.
• On ABEND: if the transaction ends abnormally, the unit of work is rolled back automatically — nothing is saved.

So the rule of thumb: success → commit (at SYNCPOINT, or at the end if none); ABEND → rollback. Because SQL joins the unit of work, it always commits or rolls back atomically with the transaction's other resources. With an XA datasource (sql.xa.datasource=true), DB2 participates in a global two-phase commit coordinated by the transaction manager, keeping the database and other XA resources consistent.

autocommit=true changes this. Each statement then commits immediately and SYNCPOINT / rollback no longer governs your SQL. Only use it when you truly want per-statement commit (rarely, for transactional programs).

A quick checklist

1. Choose a datasource (sql.default.datasource) or a URL (sql.default.url + driver + user + password).
2. Keep sql.default.autocommit=false so statements form a proper unit of work.
3. Set sql.default.isolation to match your application's needs (committed is typical).
4. Use sql.xa.datasource=true when the database must commit atomically with MQ / other resources.
5. Confirm the DB2 JDBC driver is on the runtime classpath.
6. Supply credentials via the datasource or a secured properties file — never in source.

Summary

• The runtime implements SQL over JDBC; your EXEC SQL is unchanged.
• Configure connections under sql.<connection>.* (default is implicit) — a datasource, or a URL with driver + user / password.
• Keep autocommit=false so work forms a unit of work; use an XA datasource to commit atomically with other resources.
• Success commits (explicitly at SYNCPOINT, or implicitly at transaction end); ABEND / SYNCPOINT ROLLBACK rolls back.

Related articles

• How Transactions Are Managed (ETP + PL/I Runtime) — the transaction boundary behind commit/rollback.
• Supported SQL Features — the embedded-SQL statements supported.
• How to Use IBM MQ Messaging — MQ work that commits in the same unit of work.