Distributed Transactions (XA) on HPE Nonstop using JDBC T4 driver

This article describes about how distributed transaction (XA) can be set up and used with HPE Nonstop systems NonStop JDBC Type 4 driver.

Introduction

A distributed transaction or global transaction is a set of two or more related transactions that must be managed in a coordinated way. The transactions that constitute a distributed transaction might be in the same database but more typically are in different databases and often in different locations. Each individual transaction of a distributed transaction is referred to as a transaction branch.

For example, financial institutions use HPE NonStop systems to process trillions in securities and derivatives trades annually. In these environments, distributed transactions often implemented via the XA protocol with the JDBC Type 4 driver are essential for ensuring that every trade is final, accurate, and resilient to failure.

XA transactions are crucial when a single business operation spans multiple, different data stores.

• • Atomic Data Consistency: Ensures that either all databases are updated, or none are (e.g., updating a local database and a remote Nonstop database simultaneously).
  • Heterogeneous Environment Integration: Allows Nonstop SQL/MX to act as a resource manager in a mixed environment (e.g., bridging Java EE applications on Linux with NonStop SQL/MX).
  • Reliability: In case of network failure, the XA protocol allows the transaction manager to recover and complete or roll back in-doubt transactions.
  • NonStop SQL/MX Integration: Enables modern Java applications to leverage the high availability of HPE NonStop without needing to write specialized non-standard code.

Distributed transaction functionality is built on top of connection pooling functionality, and XA standard for distributed transactions.

NOTE: XA is part of the X/Open standard and is not specific to Java.

The X/Open® XA specification describes a system-level interface through which a Resource Manager (RM), such as a SQL product, coordinates with a Transaction Manager (TM). The RM uses a global transaction identifier provided by TM.

A typical distributed transaction processing infrastructure comprises of the following roles:

• • The TM controls transaction boundaries and manages the two-phase commit protocol through the implementation of Java Transaction Application program interface (JTA).
  • JDBC drivers implement the XADataSource, XAConnection, and XAResource interfaces.
  • An application-visible implementation for the XADataSource object which interacts with the TM. The DataSource implementation is typically provided by an application server.
  • RMs manage the underlying data. In the context of the JDBC API, SQL/MX database and Transaction Management Facility (TMF) form RM.

Architecture of NonStop JDBC T4 XA

In HPE Nonstop XA, client transactions are coordinated by the external TM through the JTA interfaces available through the HPE Nonstop JDBC Type 4 Driver. The Nonstop JDBC Type 4 driver hands off these requests to the MXCS server, which in turn communicates with the XA Broker via the XA client library APIs. Finally, the XA Broker communicates with TMF via the XARM library. The following figure provides detailed process diagram for end-to-end usage of the HPE Nonstop XA products with an off-platform application environment involving the HPE Nonstop JDBC Type 4 Driver.

Setting up the XA environment on HPE Nonstop

Prerequisites

The following products must be installed on HPE Nonstop and the client machine:

• • HPE Nonstop JDBC Type 4 Driver

This product implements JTA APIs and resides on the client machine.

• • MX connectivity services

The MXCS server-side components to which JDBC JTA APIs communicate.

• • HPE Nonstop TMF XARM

XABroker uses this product to communicate with HPE Nonstop TMF. It resides on HPE Nonstop.

Installing T0804 (XA Broker)

XA Broker interfaces with the HPE Nonstop TMF XARM library and MXCS services and runs on HPE Nonstop. Initialize XABroker by running script initxabr located at /usr/tandem/xabroker/install which can be obtained by unpaxing T0804PAX.

Installing T0805 (XACI)

XACI executable can be obtained at location /usr/tandem/xabroker/bin by unpaxing T0805PAX.

Configuring XA DataSource

Configure XA DataSource through the MXCS service. Please follow below steps:

1.	>>Mode mxcs; CS> add ds <XA_datasource>, MaxServer <MaxServer count> , InitServer <InitServer count>,    startautomatic;
2.	CS> Mode sql; >>set schema NONSTOP_SQLMX_<system_name>.MXCS_SCHEMA;
3.	Select * from name2id;
4.	Control query default ODBC_METADATA_PROCESS 'ON';
5.	insert into EnvironmentValues (env_id, variable_sequence, variable_type, variable_value) values (<obj_id which got from previous select>, 0, 7, ‘-XABRK XABR -DM BRK_TEST_XABR -PATH /G/system/zxabrkr -CONN <Connection count> -TRANS <transaction count> -LVL 2’);
6.	run -name=/G/<XABROKER_process_name> /G/SYSTEM/ZXABRKR/XABRK -DM BRK_TEST_XABR -CONN <Connection count> -TRANS <transaction count> -LVL <MsgLvl 0/1/2> &

Alternatively, adding XA configuration and running XABROKER can also be achieved using Add XADEF and Run XABROKER command in XACI. Please refer to XACI Command Reference Manual for more details.

JDBC T4 XA application

Below code snippet describes how to create a XAConnection, XAResource.

XAConnection

public XAConnection getXAConnection() {
        try {
            xads = new SQLMXXADataSource();
            xads.setUrl(url);
            xads.setCatalog(catalog);
            xads.setSchema(schema);
            xads.setServerDataSource(serverDS);
            xads.setUser(user);
            xads.setPassword(password);
            xaconn = (SQLMXXAConnection) xads.getXAConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return xaconn;
    }

XAResource and twoPhaseCommit:

public void twoPhaseCommit() {
        try {
            System.out.println("Two phase commit...");
            System.out.println();
            xaconn = getXAConnection();
            xares = xaconn.getXAResource();
            Connection conn1 = xaconn.getConnection();
            XID_def xid2 = getXID();
            xares.start(xid2, XAResource.TMNOFLAGS);
            conn1.prepareStatement("insert into xasample values(200,'TwoPhase Commit')").executeUpdate();
            xares.end(xid2, XAResource.TMSUCCESS);
            // Ask the resource manager to prepare for a transaction commit/rollback of the transaction specified in xid2.
            xares.prepare(xid2);
            xares.commit(xid2, false);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (xaconn != null) xaconn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

For complete Sample application code, illustrating application for commit, rollback, two-phase commit, and two-phase rollback methods – please refer to HPE Nonstop SQL/MX 4.0 JDBC Type 4 Driver Programmer’s Reference Manual. It does not need any TM because the application itself acts as a TM.

To execute the application, export JAVA_HOME and setup Classpath for JDBC Type 4 driver, Compile the application, configure t4sqlmx.properties file and create catalog and schema mentioned in properties file.

Example of a t4sqlmx.properties file:

catalog = mycat schema = myschema
url = jdbc:t4sqlmx://cancun.caclab.cac.cpqcorp.net:61234/: user = software.john
password = abcd serverDataSource = XADS

Conclusion:

Distributed Transactions (XA) with the HPE NonStop JDBC Type 4 driver allow Java applications running on external platforms (like Windows/Linux application servers) to participate in transactions that update HPE NonStop SQL/MX databases along with other resources in a single Atomic (ACID) operation. They are essential for ensuring data consistency across heterogeneous systems.

References:

HPE Nonstop SQL/MX 4.0 JDBC Type 4 Driver Programmer’s Reference

XACI Command Reference Manual