Tibero supports X/Open DTP (Distributed Transaction Processing) XA. XA handles distributed transactions using the two-phase commit protocol.

To ensure the integrity of a transaction in a distributed environment, the two-phase commit protocol is used.

When more than one node participates a single transaction, the node that receives a request from the user and starts the transaction first is the coordinator. In a system in which TP-Monitor exists, the TP-Monitor is the coordinator.

The following figure shows a basic example of the two-phase commit mechanism:

The two-phase commit mechanism is performed in two phases. The previous figure is described as follows:

When a database receives the first message of the two-phase commit mechanism, it prepares commitment by locking necessary resources for distributed transactions or by logging them. However, the database may not receive the second message (commitment or rollback) in the process of completing the preparation due to network problems.

In this case, the database cannot decide whether to commit or rollback the transaction and just waits until the next message is delivered, while holding the lock on the prepared resources. This type of transaction, that is prepared but holds resources without receiving the next message, is called an In-doubt transaction.

For example, an in-doubt transaction occurs when a server of a certain node goes down, a coordinator node's message is not delivered to a node due to a network problem, or a coordinator node does not receive a reply from a node although the coordinator node sends a message requesting preparation.

In this case, because the coordinator node does not receive a reply from a node even though it receives replies from all the other nodes, it marks the transaction as an in-doubt transaction and sends a message to all the other nodes requesting a rollback.

The following figure shows an example of an in-doubt transaction:

Although all nodes receive messages that commitment is prepared and send replies, if a node does not receive a message requesting commitment in the second phase, the transaction is an in-doubt transaction. If a coordinator node does not receive a reply that commitment is completed from a node, although the node completes requested commitment properly, the transaction is also an in-doubt transaction.

To resolve a transaction that is pending with locked resources, a coordinator node needs to rollback transactions of all the nodes, or a database administrator needs to extract the in-doubt transaction and handle it manually.

Data from VT_XA_BRANCH and DPV_2PC_PENDING can be used to extract an in-doubt transaction.

Tibero provides XA functions, but they can only be used by C programming language.

The following table lists XA functions:

The following table shows the fields used to call the xa_open function:

ESQL is used in general, when an application program is developed in C programming language.

The following is an example of an XA application program, using tbESQL provided in Tibero

#define XA_CONN_STR_TIGHT "TIBERO_XA:user=tibero, pwd=tmax," \
                          "db=sample, sestm=60, logdir=/home/path/to/xa_log"

/* When a global transaction is started in another branch by calling xa_start
* Tight-Coupling
*/
void test_xa_2branch_2pc_tight()
{
    int rc;
    XID xid1;
    XID xid2;
    struct xa_switch_t *tbxa = &XA_SWITCH_NAME;
    char *conn_str = XA_CONN_STR_TIGHT; /* tightly coupled */
    long gtrid = _GTRID_BASE;
    long bqual = 1;

    EXEC SQL BEGIN DECLARE SECTION;
        int cnt;
    EXEC SQL END DECLARE SECTION;

    /* xa_open */
    tbxa->xa_open_entry (conn_str, 0, TMNOFLAGS);
    xid1.formatID = 1;
    xid1.gtrid_length = sizeof(gtrid);
    xid1.bqual_length = sizeof(bqual);
    memcpy(&xid1.data[0], &gtrid, sizeof(gtrid));
    memcpy(&xid1.data[sizeof(gtrid)], &bqual, sizeof(bqual));

    bqual = 2;
    xid2.formatID = 1;
    xid2.gtrid_length = sizeof(gtrid);
    xid2.bqual_length = sizeof(bqual);
    memcpy(&xid2.data[0], &gtrid, sizeof(gtrid));
    memcpy(&xid2.data[sizeof(gtrid)], &bqual,
    sizeof(bqual));

    EXEC SQL DELETE FROM PERSON;
    EXEC SQL COMMIT WORK;

    /* (1, 1) start */
    /* xa_start -- sql statements starts */
    tbxa->xa_start_entry (&xid1, 0, TMNOFLAGS);
    EXEC SQL INSERT INTO PERSON VALUES ('1', 'LEE');
    EXEC SQL INSERT INTO PERSON VALUES ('2', 'KIM');

    /* xa_end -- */
    tbxa->xa_end_entry (&xid1, 0, TMSUCCESS);

    /* (1, 2) 시작 */
    /* xa_start -- sql statements starts */
    tbxa->xa_start_entry (&xid2, 0, TMNOFLAGS);
    EXEC SQL INSERT INTO PERSON VALUES ('2', 'PARK');
    EXEC SQL INSERT INTO PERSON VALUES ('3', 'JAKE');
    EXEC SQL INSERT INTO PERSON VALUES ('4', 'KID');
    EXEC SQL INSERT INTO PERSON VALUES ('5', 'CHANHO');

    /* xa_end -- */
    tbxa->xa_end_entry (&xid2, 0, TMSUCCESS);

    /* xa_prepare */
    tbxa->xa_prepare_entry (&xid1, 0, TMNOFLAGS);

    /* Tightly-Coupled assumed */
    /* xa_prepare */
    tbxa->xa_prepare_entry (&xid2, 0, TMNOFLAGS);

    /* xa_commit */
    tbxa->xa_commit_entry (&xid1, 0, TMNOFLAGS);

    /* xa_commit */
    tbxa->xa_commit_entry (&xid2, 0, TMNOFLAGS);
    EXEC SQL SELECT COUNT(*) into :cnt FROM PERSON;
    CuAssertIntEq(tc, cnt, 6);

    /* xa_close */
    tbxa->xa_close_entry ("", 0, TMNOFLAGS);
    return;
}

/* When xa_start is started through a different branch of the same Global Trasaction
* Loose-Coupling
*/
void test_xa_2branch_2pc_loose(CuTest *tc)
{
    int rc;
    XID xid1;
    XID xid2;
    struct xa_switch_t *tbxa = &XA_SWITCH_NAME;
    char *conn_str = XA_CONN_STR_LOOSE;
    long gtrid = _GTRID_BASE;
    long bqual = 1;

    EXEC SQL BEGIN DECLARE SECTION;
        int cnt;
    EXEC SQL END DECLARE SECTION;

    /* xa_open */
    tbxa->xa_open_entry (conn_str, 0, TMNOFLAGS);
    xid1.formatID = 1;
    xid1.gtrid_length = sizeof(gtrid);
    xid1.bqual_length = sizeof(bqual);
    memcpy(&xid1.data[0], &gtrid, sizeof(gtrid));
    memcpy(&xid1.data[sizeof(gtrid)], &bqual, sizeof(bqual));

    bqual = 2;
    xid2.formatID = 1;
    xid2.gtrid_length = sizeof(gtrid);
    xid2.bqual_length = sizeof(bqual);
    memcpy(&xid2.data[0], &gtrid, sizeof(gtrid));
    memcpy(&xid2.data[sizeof(gtrid)], &bqual,
    sizeof(bqual));

    EXEC SQL DELETE FROM PERSON;
    EXEC SQL COMMIT WORK;

    /* (1, 1) start */
    /* xa_start -- sql statements starts */
    tbxa->xa_start_entry (&xid1, 0, TMNOFLAGS);
    EXEC SQL INSERT INTO PERSON VALUES ('1', 'LEE');
    EXEC SQL INSERT INTO PERSON VALUES ('2', 'KIM');

    /* xa_end -- */
    tbxa->xa_end_entry (&xid1, 0, TMSUCCESS);

    /* (1, 2) start */
    /* xa_start -- sql statements starts */
    tbxa->xa_start_entry (&xid2, 0, TMNOFLAGS);
    EXEC SQL INSERT INTO PERSON VALUES ('2', 'PARK');
    EXEC SQL INSERT INTO PERSON VALUES ('3', 'JAKE');
    EXEC SQL INSERT INTO PERSON VALUES ('4', 'KID');
    EXEC SQL INSERT INTO PERSON VALUES ('5', 'CHANHO');

    /* xa_end -- */
    tbxa->xa_end_entry (&xid2, 0, TMSUCCESS);

    /* xa_prepare */
    tbxa->xa_prepare_entry (&xid1, 0, TMNOFLAGS);

    /* Loosely-Coupled assumed */
    /* xa_prepare */
    tbxa->xa_prepare_entry (&xid2, 0, TMNOFLAGS);

    /* xa_commit */
    tbxa->xa_commit_entry (&xid1, 0, TMNOFLAGS);

    /* xa_commit */
    tbxa->xa_commit_entry (&xid2, 0, TMNOFLAGS);
    EXEC SQL SELECT COUNT(*) into :cnt FROM PERSON;
    CuAssertIntEq(tc, cnt, 6);

    /* xa_close */
    tbxa->xa_close_entry ("", 0, TMNOFLAGS);
    return;
}

The JDBC XA interfaces supported in Tibero are:

The following is a list of XA interfaces implemented in Tibero.

The following is an example program using XA interfaces in a tbJDBC environment.

package test.com.tmax.tibero.cases;
import com.tmax.tibero.jdbc.ext.*;
import test.com.tmax.tibero.AbstractBase;
import javax.sql.XAConnection;
import javax.transaction.xa.XAResource;
import java.sql.*;

public class TestXATwoBranch extends AbstractBase{
    int formatID = 1;
    int row_count = 0;
    int pre1 , pre2 = 0;

    byte[] gtid1 = new byte[1];
    byte[] bq1 = new byte[1];
    byte[] gtid2 = new byte[1];
    byte[] bq2 = new byte[1];

    public TestXATwoBranch (String name) {
    super(name);
    }

    /* When tightly coupled */
    public void test_xa_2branch_2pc_tight () throws Exception {
        debug("test_xa_2branch_2pc_tight " + this._getClassName());

        create_table_for_xa();

        gtid1[0] = (byte)1; bq1[0] = (byte)1;
        gtid2[0] = (byte)1; bq2[0] = (byte)2;

        TbXADataSource xads1 = new TbXADataSource();
        xads1.setUrl(getXAurl());
        xads1.setUser(getXAuser());
        xads1.setPassword(getXApasswd());

        TbXADataSource xads2 = new TbXADataSource();
        xads2.setUrl(getXAurl());
        xads2.setUser(getXAuser());
        xads2.setPassword(getXApasswd());

        XAConnection xacon1 = xads1.getXAConnection();
        XAConnection xacon2 = xads2.getXAConnection();

        Connection conn1 = xacon1.getConnection();
        Connection conn2 = xacon2.getConnection();

        XAResource xars1 = xacon1.getXAResource();
        XAResource xars2 = xacon2.getXAResource();

        /* XID (1.1) creation */
        TbXid xid1 = new TbXid(formatID, gtid1,bq1 );

        /* XID (1.2) creation */
        TbXid xid2 = new TbXid(formatID, gtid2, bq2);
        try {
            /* (1.1) start */
            xars1.start(xid1, XAResource.TMNOFLAGS);

            PreparedStatement pstmt1;
            pstmt1 = conn1.prepareStatement("insert into author values (?,?)");

            pstmt1.setInt(1, 1);
            pstmt1.setString(2, "FOSCHIA");
            pstmt1.executeUpdate();

            pstmt1.setInt(1,2);
            pstmt1.setString(2, "AGNOS");
            pstmt1.executeUpdate();

            /* (1.1) end */
            xars1.end(xid1, XAResource.TMSUCCESS);

            /* (1.2) start */
            xars2.start(xid2, XAResource.TMNOFLAGS);
            PreparedStatement pstmt2;
            pstmt2 = conn2.prepareStatement("insert into author values (?,?)");

            pstmt2.setInt(1, 3);
            pstmt2.setString(2, "JELLA");
            pstmt2.executeUpdate();

            pstmt2.setInt(1,4);
            pstmt2.setString(2, "THIPHILO");
            pstmt2.executeUpdate();

            /* (1.2) end */
            xars2.end(xid2, XAResource.TMSUCCESS);

            /* (1,1) prepare */
            pre1= xars1.prepare(xid1);
            assertEquals(pre1, XAResource.XA_RDONLY);

            /* (1,2) prepare */
            pre2 = xars2.prepare(xid2);
            assertEquals(pre2, XAResource.XA_OK);

            /* (1.1) commit */
            try {
                xars1.commit(xid1, false);
            } catch(TbXAException e) {}

            /* (1.2) commit */
            try {
                xars2.commit(xid2, false);
            } catch(TbXAException e) {}

            Statement stmt1 = conn1.createStatement();
            ResultSet rs1 = stmt1.executeQuery("select * from author");

            while (rs1.next())
                row_count ++;

            assertEquals(4, row_count);

            rs1.close(); rs1 = null;
            stmt1.close(); stmt1 = null;

            pstmt1.close(); conn1.close(); xacon1.close();
            pstmt2.close(); conn2.close(); xacon2.close();

            pstmt1= null; conn1= null; xacon1=null;
            pstmt2= null; conn2= null; xacon2=null;
        } catch (TbXAException e) {}
    }

    /* When loosely coupled */
    public void test_xa_2branch_2pc_loose () throws Exception {
        debug("test_xa_2branch_2pc_loose " + this._getClassName());

        create_table_for_xa();

        gtid1[0] = (byte)1; bq1[0] = (byte)1;
        gtid2[0] = (byte)1; bq2[0] = (byte)2;

        TbXADataSource xads1 = new TbXADataSource();

        xads1.setUrl(getXAurl());
        xads1.setUser(getXAuser());
        xads1.setPassword(getXApasswd());

        TbXADataSource xads2 = new TbXADataSource();

        xads2.setUrl(getXAurl());
        xads2.setUser(getXAuser());
        xads2.setPassword(getXApasswd());

        XAConnection xacon1 = xads1.getXAConnection();
        XAConnection xacon2 = xads2.getXAConnection();

        Connection conn1 = xacon1.getConnection();
        Connection conn2 = xacon2.getConnection();

        XAResource xars1 = xacon1.getXAResource();
        XAResource xars2 = xacon2.getXAResource();

        TbXid xid1 = new TbXid(formatID, gtid1,bq1 );
        TbXid xid2 = new TbXid(formatID, gtid2, bq2);

        try {
            xars1.start(xid1, TbXAResource.TBRTRANSLOOSE);

            PreparedStatement pstmt1;
            pstmt1 = conn1.prepareStatement("insert into author values (?,?)");

            pstmt1.setInt(1, 1);
            pstmt1.setString(2, "FOSCHIA");
            pstmt1.executeUpdate();

            pstmt1.setInt(1,2);
            pstmt1.setString(2, "AGNOS");
            pstmt1.executeUpdate();

            xars1.end(xid1, XAResource.TMSUCCESS);

            xars2.start(xid2, TbXAResource.TBRTRANSLOOSE);

            PreparedStatement pstmt2;
            pstmt2 = conn2.prepareStatement("insert into author values (?,?)");

            pstmt2.setInt(1, 3);
            pstmt2.setString(2, "JELLA");
            pstmt2.executeUpdate();

            pstmt2.setInt(1,4);
            pstmt2.setString(2, "THIPHILO");
            pstmt2.executeUpdate();

            xars2.end(xid2, XAResource.TMSUCCESS);

            pre1= xars1.prepare(xid1);
            assertEquals(pre1, XAResource.XA_OK);

            pre2 = xars2.prepare(xid2);
            assertEquals(pre2, XAResource.XA_OK);

            xars1.commit(xid1, false);
            xars2.commit(xid2, false);

            Statement stmt1 = conn1.createStatement();
            ResultSet rs1 = stmt1.executeQuery("select * from author");

            while (rs1.next())
            row_count ++;

            assertEquals(4, row_count);

            rs1.close(); rs1 = null;
            stmt1.close(); stmt1 = null;

            pstmt1.close(); conn1.close(); xacon1.close();
            pstmt2.close(); conn2.close(); xacon2.close();

            pstmt1= null; conn1= null; xacon1=null;
            pstmt2= null; conn2= null; xacon2=null;
        } catch (TbXAException e) {}
    }
}

Tmax stands for Transaction Maximization. As a TP-Monitor, it not only guarantees that transactions are perfectly handled between heterogeneous systems, but it also distributes loads and handles errors. Furthermore, it supports various characteristics of transactions, offers optimal development environment to the users, provides best solutions in the client/server environment with high performance, and handles all types of failures.

Tmax complies with the X/Open DTP (Distributed Transaction Processing) model which is the international standard for distributed transaction processing. It has been developed according to APIs for the OSI(Open Systems Interconnection group) DTP services and system interface definitions. In addition, it supports transparent business handling between heterogeneous systems in the distributed environment and OLTP (On-Line Transaction Processing), and meets the ACID (Atomic, Consistent, Isolated, Durable: transaction properties) characteristics for transaction handling.

To test the following example program integrating Tmax with Tibero, Tmax and Tibero should first be properly installed.

The following example program is provided by the Tmax installer, by default. The program illustrates how a client accesses Tibero DB to search, add, modify, and delete data in a certain table via Tmax server. Descriptions for the test environment and each file used by the program are as follows:

Tmax can be integrated with Tibero in the following order:

Configuring Tmax

The following describes how to configure a Tmax environment.

Compiling TMS

As a Tmax system's component, TMS (Transaction Management Server) is a process that manages databases and handles distributed transactions. Before compiling TMS for Tibero, check whether Tibero-related environment variables such as TB_HOME, TB_SID, LD_LIBRARY_PATH, and PATH are properly set.

Move to the $TMAXDIR/sample/server directory to compile TMS with TMS makefile for Tibero as follows:

cd $TMAXDIR/sample/server
make –f tms_tbr.mk all

Compiling a server program

Move to the $TMAXDIR/sample/server directory to compile a server program that provides actual services using a build script as follows:

cd $TMAXDIR/sample/server
./compile tbc tbrtest

Compiling a client program

Move to the $TMAXDIR/sample/server directory to compile a client program that requests services using a build script as follows:

cd $TMAXDIR/sample/client
./compile c tbr_main

Creating a DB table

Access Tibero server using the tibero/tmax account to create the emp table as follows:

tbsqltibero/tmax

create table emp (
    empno number,
    ename char(16),
    job char(16),
    hiredate char(16),
    sal number
);

Executing an Example program

As a platform for handling distributed transactions, Tuxedo provides mainframe's scalability and high-performance in open distributed systems for programs written in C, C++, and COBOL. It can also rehost mainframe applications on mainstream hardware.

Tuxedo not only provides reliability and scalability in a cost-effective way for supporting hundreds of thousands of transactions, but it also extends the lifecycle of existing IT resources that are a part of an innovative architecture such as SOA. Oracle Tuxedo is an Oracle Fusion Middleware product for handling transactions.

To test the following example program integrating Tuxedo with Tibero, Tuxedo and Tibero should be properly installed.

The following example program illustrates how a client accesses Tibero DB to search and add data in a certain table via Tuxedo server. For convenience, assume that the host name of the test server is tux_machine, Tibero and Tuxedo are installed in path/to/tibero and /path/to/tuxedo respectively, and relevant files are located in /path/to/example.

Descriptions for the test environment and each file used by the program are as follows:

Tuxedo can be integrated with Tibero in the following order. Depending on the OS and the system environment, the example files should be modified accordingly.

Setting system environment variables

To test the integration with Tibero and Tuxedo, set the following system environment variables:

Configuring Tuxedo

Compiling TMS

Compile TMS for Tibero with the following command:

buildtms -o $TUXDIR/bin/tms_tibero -v -r TIBERO_XA

Compiling a server program

Compile a server program that provides actual services with the following build script, builds.sh.

#### transaction server precompile ####
PRECOMP=$TB_HOME/client/bin/tbpc
PRECOMPFLAGS="UNSAFE_NULL=YES"
LIB=$TB_HOME/client/lib
INC=$TB_HOME/client/include
CFLAGS="-ltbcli -ltbxa -lm -lrt -lpthread -ltbertl -g "
CC=gcc
rm -f trans_fml32.c
$PRECOMP INCLUDE=$TUXDIR/include UNSAFE_NULL=YES INCLUDE=$INC 
$PRECOMPFLAGS ONAME=trans_fml32.c trans_fml32.tbc

#### transaction server build ####
buildserver -o trans_fml32 -v -f trans_fml32.c -s INSERT_FML32,
SELECT_FML32 -r TIBERO_XA

Compiling client programs

Compile client programs, that request insert and select services, with a build script as follows:

buildclient -o insert -v -f insert.c
buildclient -o select -v -f select.c

Creating a DB table

Access Tibero server using the tibero/tmax account to create the emp table as follows:

tbsqltibero/tmax

drop table emp;
CREATE TABLE emp (
    empno           NUMBER,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2)
);

Executing an example program