The daily edition.

Scope and Architectural Overview

This article covers REXX database connectivity via the DSNREXX interface in Rocket Enterprise Developer / Enterprise Server 12.0 for Linux/UNIX.

In mainframe z/OS batch processing, administrative and automation scripts written in REXX frequently interact with relational databases using IBM's DSNREXX host command environment. Rocket Enterprise Developer provides mainframe-compatible DSNREXX Support, enabling REXX execs executed in JCL jobstreams or standalone shell environments to issue static and dynamic SQL commands directly against relational database managers.

Under Enterprise Server, DSNREXX operates through the ODBC XA switch module. On Linux/UNIX platforms, DSNREXX is certified for use with DB2 LUW (with SQL Server supported under Windows environments). Attempting to use DSNREXX with an uncertified DBMS results in an immediate connection failure.

Subcommand Environment Registration and Connection Management

Before a REXX exec can execute embedded SQL statements, it must verify or register the DSNREXX host command environment using the RXSUBCOM utility function:

/* REXX - Connect to DB2 LUW via DSNREXX */
SSID = "DB2T"
ADDRESS MVS "SUBCOM DSNREXX"
IF RC THEN S_RC = RXSUBCOM('ADD', 'DSNREXX', 'DSNREXX')

ADDRESS DSNREXX "CONNECT" SSID
IF RC /= 0 THEN DO
   SAY "FAILURE TO CONNECT TO DATABASE, RC="RC
   EXIT 8
END

Once connected, all SQL operations are addressed to DSNREXX. When finished, the connection is closed using ADDRESS DSNREXX "DISCONNECT". While the syntax RELEASE connection is accepted for mainframe compatibility, in the Enterprise Developer implementation it performs only a DISCONNECT.

SQL Syntax Rules and Copybook Limitations

Executing SQL statements through DSNREXX follows strict formatting and structure rules:

1. The EXECSQL Prefix: Every SQL operation must begin with the keyword EXECSQL, followed by the SQL command enclosed in quotes or passed via a host variable:

``rexx SQLSTMT = "SELECT FIRSTNME, LASTNAME FROM EMP WHERE EMPNO >= ? WITH UR" ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT" ``

2. Unsupported Copybooks: As documented in Copybooks (DSNREXX), the EXECSQL INCLUDE statement is not valid in REXX. Host variables cannot be imported from COBOL copybooks; all host variables must be defined directly in the REXX exec as single data items (or referenced via an SQLDA descriptor).
3. Statement Boundaries: DSNREXX supports standard cursor navigation (DECLARE, OPEN, FETCH, CLOSE), prepared statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE), and DESCRIBE INPUT. However, stored procedure CALL statements only support non-result-set procedures, while DESCRIBE CURSOR, DESCRIBE PROCEDURE, SET CONNECTION, and SET CURRENT SCHEMA are unsupported.

Error Checking: RC, SQLCODE, and SQLCA Stem Variables

Mainframe embedded SQL programs often rely on EXEC SQL WHENEVER to trap warnings and errors. As noted in SQL Error Checking, EXECSQL WHENEVER is not valid in REXX.

Developers must explicitly inspect return status after every EXECSQL call:

• The RC Special Variable: Set by the DSNREXX interface upon command completion:
  • 0: Success; no SQL warning or error occurred.
  • +1: An SQL warning occurred.
  • -1: An SQL error occurred (e.g., connection failed or targeting an unsupported DBMS).
  • -3: Invalid or unsupported token found after DSNREXX.
• Predefined SQLCA Variables: Enterprise Developer automatically exposes SQL Communication Area fields directly to the REXX script without prior definition:
  • SQLCODE: Relational database return code (e.g., 0 for success, 100 for not found).
  • SQLSTATE: Five-character standard SQLSTATE code.
  • SQLERRMC: Error message tokens.
  • SQLERRP: Database engine procedure reporting the condition.
  • SQLERRD.1 through SQLERRD.6: Integer diagnostics, including rows affected in SQLERRD.3.
  • SQLWARN.0 through SQLWARN.10: Diagnostic warning indicators.

Controlling Isolation Levels with SET CURRENT PACKAGESET

Because standard transactional isolation syntax cannot be executed directly in dynamic SQL strings, DSNREXX adopts the IBM DB2 z/OS packageset convention documented in Setting the Isolation Level.

To set the transaction isolation level, execute SET CURRENT PACKAGESET pointing to one of four reserved package identifiers:

/* Set transaction isolation level to Repeatable Read */
ADDRESS DSNREXX "EXECSQL SET CURRENT PACKAGESET='DSNREXRR'"

Supported packageset mappings include:

• DSNREXRR: Repeatable Read (RR), equivalent to SET TRANSACTION ISOLATION REPEATABLE READ.
• DSNREXRS: Read Stability (RS), equivalent to SET TRANSACTION ISOLATION SERIALIZABLE.
• DSNREXCS: Cursor Stability (CS), equivalent to SET TRANSACTION ISOLATION READ COMMITTED.
• DSNREXUR: Uncommitted Read (UR), equivalent to SET TRANSACTION ISOLATION READ UNCOMMITTED.

Attempting to issue SET CURRENT PACKAGESET with any name outside this list returns an immediate SQL error.

Documentation Takeaway

In Rocket Enterprise Developer 12.0 for Linux/UNIX, REXX scripts query DB2 LUW via DSNREXX over the ODBC XA switch module; because EXECSQL WHENEVER and EXECSQL INCLUDE are invalid, developers must register the environment via RXSUBCOM, evaluate SQLCODE and RC explicitly after every call, declare host variables inline, and manage transaction isolation using reserved DSNREXxx packageset names.