SQLJ for DB2 for z/OS

Why SQLJ still exists

JDBC is flexible and verbose. SQLJ is checked earlier: the translator sees the SQL, host variable types, and iterator columns. At run time Db2 uses a bound package (stable access paths, BIND EXPLAIN, APREUSE) instead of a full dynamic prepare for every statement. Shops that already live on static SQL for COBOL often want the same model in Java.

You can mix SQLJ and JDBC: obtain a JDBC Connection, wrap it in a connection context, or call JDBC on the connection underneath the context. Java stored procedures may use either API with jdbc:default:connection.

SQLJ clauses

Executable clauses wrap SQL in braces. Host variables take a colon: :empno. Expressions are allowed, for example :((int)n * 100). Indicator variables use SQLJ ExecutionContext constants (DBNonNull, DBDefault) when you must pass NULL or DEFAULT the COBOL way. CALL uses :IN / :OUT / :INOUT as needed.

1
2
3
4
5
6
7
8
9
10
11
12
13
#sql context Ctx;
 
Ctx myConnCtx = new Ctx(
    "jdbc:db2://sysmvs1.example.com:5021/NEWYORK",
    userid, password, false);
 
String empname;
#sql [myConnCtx] {
  SELECT LASTNAME INTO :empname
    FROM EMPLOYEE
   WHERE EMPNO = '000010'
};
#sql [myConnCtx] {COMMIT};

If you omit [myConnCtx], SQLJ uses the default context. CICS inherited connections cannot pass a user ID and password on the context constructor.

Connection context

#sql context Ctx; generates a class. Constructors take a JDBC URL (type 2 or type 4, same rules as JDBC), user/password, Properties, autocommit flag, or an existing Connection. You can declare a context with a dataSource logical name for JNDI. Passing a context into another program is how you share one unit of work.

Iterators

An iterator is a typed cursor.

• Named iterator — #sql iterator EmpIter(String LASTNAME, String EMPNO); columns become getter methods (emp.lastName() depending on naming). Good when the SELECT list is fixed.
• Positioned iterator — #sql iterator ByPos(String, Date); then FETCH INTO host variables, like COBOL. Order of types must match the SELECT list.

1
2
3
4
5
6
7
8
9
10
#sql iterator ByPos(String, java.sql.Date);
ByPos positer;
#sql [ctxt] positer = {
  SELECT LASTNAME, HIREDATE FROM EMPLOYEE
};
while (true) {
  #sql { FETCH :positer INTO :name, :hrdate };
  if (positer.endFetch()) break;
}
positer.close();

Attributes on the iterator declaration pick sensitivity, updatability, and holdability (similar to JDBC ResultSet types). Always close iterators.

SQLJ translator

The sqlj command (SQLJ translator) reads .sqlj source and writes:

• A .java file with the #sql clauses replaced by calls into sqlj.runtime and the driver.
• One or more serialized profiles (.ser), often named ClassName_SJProfile0.ser.

Compile the generated Java with javac. Translator options control JDBC compliance, null-handling, and whether online checking against a database is attempted. Syntax errors in SQL show up at translate time when checking is on—that is a large part of the value.

SQLJ profiles, packages, and binding

db2sqljcustomize (class com.ibm.db2.jcc.sqlj.Customizer) connects with -url, -user, and -password, rewrites the .ser file for that server, and by default binds static packages (-automaticbind yes).

1
2
3
4
5
6
java com.ibm.db2.jcc.sqlj.Customizer ^
  -url jdbc:db2://db2a.example.com:448/DB2A:sslConnection=true; ^
  -user BINDID -password *** ^
  -rootpkgname PAYSQLJ ^
  -collection SQLJCOLL ^
  MyApp_SJProfile0.ser

Default customize creates four packages, one per isolation level. -rootpkgname is a short name (often up to seven characters when it must look like a PDS member); suffixes 1–4 distinguish UR/CS/RS/RR packages. Customizing several profiles in one command requires -rootpkgname; a single profile can default the name from the .ser file.

Bind options pass through -bindoptions (QUALIFIER, OWNER, ISOLATION defaults, VALIDATE, EXPLAIN, and the rest of BIND PACKAGE). -onlinecheck NO and VALIDATE RUN are the usual pair when objects do not exist yet in the bind environment.

db2sqljbind rebinds customized profiles (new statistics, new QUALIFIER) without translating again. db2sqljprint shows what SQL landed in the profile when a package will not bind.

Deploy customized .ser files on the runtime class path before any uncustomized copies inside a JAR. If you replace profiles in a JAR, keep the same package directory structure the translator used.

SQLJ collection is not jdbcCollection. Driver JDBC packages stay in NULLID (or whatever DB2Binder used). SQLJ packages go where -collection says. PKLIST on the plan must include that collection if you still use a plan that lists packages the old way; type 4 SQLJ uses the driver connection and the customized package names directly.

Runtime notes

ExecutionContext gives SQLCODE, SQLSTATE, update counts, and warning chains—similar to SQLCA. Autocommit on the context constructor matches JDBC. Isolation at run time selects which of the four packages runs. REBIND PACKAGE on the SQLJ collection works like any other static package: PLANMGMT, APREUSE, EXPLAIN(YES).

Prefer SQLJ for stable DML and JDBC PreparedStatement for truly dynamic SQL (optional ORDER BY columns, generated filters). Do not concatenate user text into an SQLJ literal any more than you would in JDBC.

Explain It Like I'm Five

JDBC writes the recipe on a sticky note in the kitchen (dynamic). SQLJ prints the recipe in a cookbook before dinner (static). The translator turns your illustrated cookbook (#sql) into plain Java plus a packing list (.ser). The customizer takes that packing list to the Db2 warehouse and pre-packs four boxes (packages) for four “careful-ness” levels (isolation). At dinner you open the matching box instead of cooking from scratch.

Exercises

1. Write an SQLJ context, a SELECT INTO, and a COMMIT against EMPLOYEE.
2. Declare a named iterator with LASTNAME and EMPNO and sketch the loop that prints rows.
3. List the files sqlj MyProg.sqlj creates and which tool consumes the .ser file.
4. Why does customize create four packages, and how does run-time isolation pick one?
5. Explain why customized profiles must appear first on the class path.