DB2 remote SQL and distributed units of work

Remote SQL

Remote SQL is any statement that runs at a server other than the local subsystem you started on. From z/OS COBOL that is usually another Db2 for z/OS location, Db2 LUW, or a DRDA partner defined in the communications database. From JDBC the “remote” server is z/OS itself—the Java side is the requester.

Packages still matter. Static SQL at a remote location needs a package bound there (or a three-part bind). Dynamic SQL uses DRDA packages at the server. SQLCODE and SQLSTATE come back through DRDA; some codes are unique to distributed failures (communications, authorization at the remote AUTHID, in-doubt UOW).

Three-part names and four-part names

A three-part name is location.schema.object:

1
2
3
4
5
6
SELECT EMPNO, LASTNAME
  FROM DB2WHSE.HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';
 
CREATE ALIAS HR.EMP_WHSE FOR DB2WHSE.HR.EMPLOYEE;
SELECT EMPNO FROM HR.EMP_WHSE;

Db2 reads the location qualifier, looks up how to reach it (SYSIBM.LOCATIONS / IPNAMES), and runs the statement at that server. Implicit connect/disconnect can happen for that object. Aliases let programs keep two-part names while operations move tables between locations.

A four-part name qualifies a column: location.schema.table.column. Use it in expressions or join lists when the table itself is remote and you need an unambiguous column. Most application SQL uses three-part table names and unqualified column names inside the statement.

Restrictions exist: some data types and features (certain array references, some SQL PL) cannot ride a three-part name. If the precompiler complains after CONNECT, check IBM’s remote-restriction list rather than assuming local SQL always ships.

CONNECT, SET CONNECTION, and RELEASE

CONNECT TO location makes that server the current server. SQL without a location qualifier then runs there. CONNECT can use a host variable for the name. USER/USING supplies credentials when you are not using the already-verified RACF id.

1
2
3
4
5
6
7
       EXEC SQL CONNECT TO DB2WHSE END-EXEC.
       EXEC SQL
            SELECT COUNT(*) INTO :HV-CNT
              FROM HR.EMPLOYEE
       END-EXEC.
       EXEC SQL COMMIT END-EXEC.
       EXEC SQL CONNECT RESET END-EXEC.

SET CONNECTION location makes an existing dormant connection current. It does not create a new one. If the name is unknown, you get a connection-state error (SQLSTATE 08003 class).

RELEASE location (or RELEASE ALL) puts the connection in release-pending. The TCP conversation goes away on the next successful COMMIT, not at the RELEASE statement itself. After RELEASE you cannot put that connection back to held; plan to CONNECT again later if needed.

Remote unit of work

Remote unit of work (RUW) uses CONNECT type 1 rules. The application is connected to one application server at a time. To CONNECT somewhere else you must be in a connectable state—typically after COMMIT or ROLLBACK so no in-flight work remains. This matches “call the warehouse, finish the transaction, then call payroll.”

RUW is easier to reason about and enough for many COBOL requesters. Bind or precompiler options select type 1 versus type 2 CONNECT semantics (CONNECT(1) / CONNECT(2) and related SQL processing options).

Distributed unit of work

Distributed unit of work (DUW) uses CONNECT type 2. You may hold several connections: exactly one is current, the others are dormant. SET CONNECTION (or CONNECT with SQLRULES(DB2) to an existing server) switches which one is current. COMMIT or ROLLBACK applies to the whole unit of work—Db2 uses two-phase commit so either all locations commit or all roll back.

1
2
3
4
5
6
CONNECT TO DB2HR;
UPDATE HR.EMPLOYEE SET SALARY = SALARY * 1.03
 WHERE EMPNO = '000010';
CONNECT TO DB2PAY;
INSERT INTO PAY.AUDIT(EMPNO, ACTION) VALUES ('000010', 'RAISE');
COMMIT;

SQLRULES(DB2) versus SQLRULES(STD)

With SQLRULES(DB2), CONNECT to a server you already have is treated like SET CONNECTION. With SQLRULES(STD), CONNECT to an existing connection is an error; you must SET CONNECTION instead. STD matches SQL standard connection rules; DB2 is the friendlier IBM default for many z/OS shops.

Dormant connections keep cursors, locks, and prepared statements as they were when you left. Switching back in the same UOW does not reopen the world from scratch. That is powerful and easy to leak: a WITH HOLD cursor on location A is still open while you update location B.

Connection pooling

Two layers use the words “pool”:

• Client pooling (JDBC DataSource, WAS, CICS JVMSERVER libraries) reuses TCP connections so each HTTP request does not pay a full DRDA handshake
• DDF pooling (CMTSTAT INACTIVE) reuses DBATs while inactive connections keep the socket

Oversized client pools can pin thousands of CONDBAT slots and starve MAXDBAT. Size the application pool to real concurrency, not “open 500 connections just in case.” Trusted connections exist because a naive pool would otherwise run every end user as the same table owner.

Trusted connections

A trusted context (CREATE TRUSTED CONTEXT) names a SYSTEM AUTHID, a connection trust attribute (IP, encryption, job name), and default roles. Middleware connects once as the system user, then switches to the end-user id for each request. Db2 audits and authorizes the switched user. This is the z/OS answer to “connection pool plus real user identity.”

Trusted connections do not replace table GRANTs. They replace the anti-pattern of embedding a privileged password in every client and sharing it among thousands of users. Pair them with SECPORT/TLS so the SYSTEM AUTHID cannot be replayed on the open internet.

Explain It Like I'm Five

Three-part names are writing a friend’s house address on the letter so the post office (DDF) knows which library to visit. CONNECT is walking into that library and staying at the desk. RUW means you finish one library visit, go home (COMMIT), then you may visit another. DUW means you keep several library cards in your pocket, but you only stand at one desk at a time (SET CONNECTION), and when you say “I’m done” both librarians stamp the card together (two-phase commit). RELEASE is hanging your coat by the door so the next COMMIT actually leaves the building. A trusted connection is a school bus driver (SYSTEM AUTHID) who is allowed in the gate, then each kid shows their own library card once they are inside.

Exercises

1. Write a three-part SELECT and an ALIAS that hides the location.
2. Describe one COBOL flow that is RUW-only and one that needs DUW.
3. When would SET CONNECTION fail with SQLSTATE 08003?
4. Why does RELEASE plus ROLLBACK not “take back” the release-pending flag?
5. Give one reason a JDBC pool of 1000 connections can hurt a subsystem with MAXDBAT 200.