DB2 JDBC connectivity and drivers

The Db2 JDBC driver

Do not start new work on the old Universal Driver class names in COM.ibm.db2 or on db2jcc.jar (JDBC 3). Current support is:

• JAR — db2jcc4.jar (JDBC 4.0 or later). License JARs as your download bundle requires.
• Class — com.ibm.db2.jcc.DB2Driver. JDBC 4+ can autoload it; older runtimes still use Class.forName.
• One instance — loading the driver once serves type 2 and type 4 connections at the same time.

You need a Java runtime IBM documents for that driver version (historically Java 5+; shops today run current IBM Semeru or Oracle JDK levels the driver lists). On a z/OS LPAR that has no Db2, the optional z/OS Application Connectivity to Db2 feature still provides type 4 to a remote data server.

XA / JTA distributed transactions are a type 4 feature against Db2 for z/OS Version 7 or later. Type 2 in CICS Liberty still goes through the CICS-Db2 attachment (EXEC SQL equivalent), which is a different performance and zIIP story.

Type 2 versus type 4

• Type 2 connectivity — native libraries plus Java. Use it when the JVM runs on the same z/OS LPAR as the target subsystem. In CICS it uses the CICS Db2 attachment; in stored procedures it uses the existing thread. Some native path is not zIIP-eligible.
• Type 4 connectivity — pure Java DRDA client. Use it from another LPAR, Linux, Windows, or a container. Traffic hits DDF (DBAT). Java is generally zIIP-eligible on z/OS. You must reach PORT or SECPORT, and the driver packages must be bound.

A CICS Liberty benchmark Redpaper (REDP-5208) is the usual citation when architects argue type 2 versus type 4 on-platform: type 2 avoids DDF but pays native CPU; type 4 is more offloadable and matches distributed clients.

JDBC URLs

Type 4 format

jdbc:db2://server:port/database:connection-options

• server — host name or IP of the DDF listener (group DVIPA for data sharing).
• port — TCPPORT or SECPORT (0–65535). Default 446 if omitted.
• database — DDF location name, uppercase, as in SYSIBM.LOCATIONS / DISPLAY DDF. The driver folds lowercase to uppercase for z/OS.
• connection-options — property=value; pairs with no spaces, each including the last ending in semicolon.

Prefix jdbc:db2j:net: is Cloudscape; jdbc:ids: is Informix; jdbc:ibmdb: means any IBM data server the driver supports. For Db2 for z/OS, jdbc:db2: is the everyday prefix.

Type 2 format

jdbc:db2:LOCATION or the compatibility forms jdbc:db2os390: and jdbc:db2os390sqlj:. jdbc:default:connection means “the thread I already have” in CICS, IMS, or a Java routine. You cannot pass a user ID and password on a CICS inherited connection.

1
2
3
4
5
6
String url =
    "jdbc:db2://db2a.example.com:448/DB2A"
        + ":user=APPUSER;password=secret;"
        + "sslConnection=true;"
        + "jdbcCollection=NULLID;";
Connection con = DriverManager.getConnection(url);

Connections: DriverManager and DataSource

Two standard ways to get a java.sql.Connection:

• DriverManager.getConnection(url, user, password) — simple, common in batch and samples. Put secrets in properties or a wallet, not source code.
• DataSource — javax.sql.DataSource (and IBM’s DB2DataSource / DB2XADataSource). Application servers bind a DataSource in JNDI. You lookup and call getConnection(). This is the production pattern: pooling, XA, and configuration live outside the application.

Useful driver properties (also valid on the URL):

• sslConnection=true — SSL socket. Pair with sslTrustStoreLocation or sslCertLocation, and SECPORT.
• jdbcCollection — collection of the driver packages (default NULLID). Must match DB2Binder -collection.
• currentPackageSet / currentSQLID / currentSchema — special-register style controls for the session.
• clientAccountingInformation, clientApplicationInformation — show up in DISPLAY THREAD and traces.

Always close Connection, Statement, and ResultSet (try-with-resources). A leaked connection is a leaked DBAT until idle timeout.

Driver packages on the server

Type 4 SQL runs through packages bound by the DB2Binder utility (com.ibm.db2.jcc.DB2Binder) into a collection, usually NULLID. If jdbcCollection does not match, you see package-not-found SQLCODEs at connect or first SQL. SQLJ uses a different collection from db2sqljcustomize -collection; jdbcCollection does not redirect SQLJ.

Connection pooling

A connection is not a cheap object: TCP, optional TLS handshake, RACF, DBAT allocation, special registers. Connection pooling keeps a set of authenticated connections ready.

• Application-server pools (WebSphere, Liberty, Tomcat) wrap a DataSource. Configure min/max size, aged timeout, and validation SQL.
• Sysplex workload balancing in the IBM driver can spread type 4 work across data-sharing members and reuse transports.
• DDF pooling / high-performance DBATs on the server (RELEASE(DEALLOCATE), KEEPDYNAMIC) complements the client pool; see the DDF threads page.

Pool rules beginners break: calling Connection.close() on a pooled connection returns it to the pool (good) only if you obtained it from the DataSource; wrapping and closing the physical socket yourself defeats the pool. Do not change autocommit or isolation on a borrowed connection without resetting them. Never share one Connection across threads.

Explain It Like I'm Five

JDBC is a universal plug. The IBM driver is the adapter that fits Db2. Type 2 is shouting through the wall to Db2 in the same house (same LPAR). Type 4 is phoning Db2 on the DDF number (host, port, location name). The URL is the phone number. A DataSource is the office receptionist who keeps a few lines already dialed (the pool) so you do not wait for the ringtone every time you ask a question.

Exercises

1. Write a type 4 URL for location PRODDB, group DVIPA db2g.example.com, SECPORT 448, TLS on.
2. When would jdbc:default:connection be correct, and what happens if you instead open jdbc:db2://localhost from a Java stored procedure?
3. Find which collection your driver packages use (SYSPACKAGE where name like 'SYSSTAT%' or the DB2Binder job) and match jdbcCollection.
4. List three properties you would set on a Liberty DataSource for a production CICS or WAS app.
5. Explain why a laptop test with type 2 URLs fails off-platform.