Storing transaction logs in a relational database

You can choose to store your Liberty transaction logs in a relational database rather than as operating system files. This feature provides high availability (HA) support without having to use a shared file system. Storing transaction service logs in a relational database is supported for production use.

About this task

The WebSphere® Application Server transaction service writes information to a transaction log for every global transaction that involves two or more resources, or that is distributed across multiple servers. These transactions are started or stopped either by applications or by the container in which they are deployed. The transaction service maintains transaction logs to ensure the integrity of transactions. Information is written to the transaction logs in the prepare phase of a distributed transaction, so that if a server with active transactions restarts after a failure, the transaction service is able to use the logs to replay any indoubt transactions. This allows the overall system to be brought back to a consistent state.

The default configuration is to store transaction logs as operating system files. This HA transaction support requires the use of a shared file system to host the transaction logs, such as an NFSv4-mounted network-attached storage (NAS) or a storage area network (SAN).

But you can choose to store the transaction logs in a relational database management system (RDBMS). This configuration option is aimed at customers who work in an HA environment. This feature enables customers, particularly those customers with an investment in HA database technology, to use their HA database as a shared repository for the transaction logs, as an alternative to using a shared file system. You can use any database type that Liberty supports.

You can configure an application server to recover the logs of a different application server. The original owning application server must not be running when using this procedure. It is typically employed to perform any outstanding transactional recovery when the transaction service logs are available but the original owning application server cannot be started.

The principal of recovery for Liberty is the same for as for WebSphere Application Server traditional. See the following resources for more information about recovery:

Remember: Do not configure application servers to use the same tables. Doing so might result in data integrity issues and log corruption. If a suffix is specified, then the suffix is appended directly to both the name of the table and the index.

Procedure

To configure the Liberty transaction logs to be stored in an RDBMS, complete the following steps:

Configure a dedicated, non-transactional data source in the Liberty server.xml file.

The following example extract from the server.xml file shows how to configure Liberty to store its transaction logs in a DB2® database:

<transaction> 
  <dataSource transactional="false">
    <jdbcDriver libraryRef="DB2JCC4LIB"/>
    <properties.db2.jcc currentSchema="CBIVP"
      databaseName="SAMPLE" driverType="4"
      portNumber="50000" serverName="localhost" 
      user="db2admin" password="{xor}Oz1tPjsyNjE=" />
  </dataSource> 
</transaction> 

<library id="DB2JCC4LIB">
  <fileset dir="C:/SQLLIB/java" includes="db2jcc4.jar db2jcc_license_cu.jar"/>
</library>

Optional: Create the database tables.

Liberty attempts to create the necessary database tables when the server first starts. When creating these databases is not possible, due to insufficient permission for example, the server fails to start. Under these circumstances, you must create the two database tables manually.

For storing transaction logs in an RDBMS for an application server, each server must have its own tables. For a WebSphere Application Server traditional configuration, you specify a table suffix in the custom URL that is unique for each application server. WebSphere Application Server: Storing transaction and compensation logs in a relational database for high availability applies to the traditional product, but demonstrates the creation of the suffix and table names. In Liberty, the suffix is specified by using the transaction element transactionLogDBTableSuffix attribute. See Transaction Manager (transaction) for information about transactionLogDBTableSuffix.

The following DDL structures show how to create the tables on DB2:

CREATE TABLE WAS_TRAN_LOG(
  SERVER_NAME VARCHAR(128),
  SERVICE_ID SMALLINT,
  RU_ID BIGINT,
  RUSECTION_ID BIGINT,
  RUSECTION_DATA_INDEX SMALLINT,
  DATA BLOB)

CREATE TABLE WAS_PARTNER_LOG(
  SERVER_NAME VARCHAR(128),
  SERVICE_ID SMALLINT,
  RU_ID BIGINT,
  RUSECTION_ID BIGINT,
  RUSECTION_DATA_INDEX SMALLINT,
  DATA BLOB)

The following DDL structures show how to create the tables on the old DB2 version:

CREATE TABLE WAS_TRAN_LOG(
  SERVER_NAME VARCHAR(128),
  SERVICE_ID SMALLINT,
  RU_ID BIGINT,
  RUSECTION_ID BIGINT,
  RUSECTION_DATA_INDEX SMALLINT,
  DATA LONG VARCHAR FOR BIT DATA)

CREATE TABLE WAS_PARTNER_LOG(
  SERVER_NAME VARCHAR(128),
  SERVICE_ID SMALLINT,
  RU_ID BIGINT,
  RUSECTION_ID BIGINT,
  RUSECTION_DATA_INDEX SMALLINT,
  DATA LONG VARCHAR FOR BIT DATA)

The following DDL structures show how to create indexes for these tables:

CREATE INDEX IXWSTRAN_LOG ON WAS_TRAN_LOG (RU_ID ASC, SERVICE_ID ASC, SERVER_NAME ASC)
CREATE INDEX IXWSPARTNER_LOG ON WAS_PARTNER_LOG (RU_ID ASC, SERVICE_ID ASC, SERVER_NAME ASC)

The following DDL structures show how to create the database table on Oracle:

CREATE TABLE WAS_TRAN_LOG(
  SERVER_NAME VARCHAR(128),
  SERVICE_ID SMALLINT,
  RU_ID NUMBER(19),
  RUSECTION_ID NUMBER(19),
  RUSECTION_DATA_INDEX SMALLINT,
  DATA BLOB)

CREATE TABLE WAS_PARTNER_LOG(
  SERVER_NAME VARCHAR(128),
  SERVICE_ID SMALLINT,
  RU_ID NUMBER(19),
  RUSECTION_ID NUMBER(19),
  RUSECTION_DATA_INDEX SMALLINT,
  DATA BLOB)

The following DDL structures show how to create indexes for these tables:

CREATE INDEX IXWSTRAN_LOG ON WAS_TRAN_LOG ( "RU_ID" ASC, "SERVICE_ID" ASC, "SERVER_NAME" ASC)
CREATE INDEX IXWSPARTNER_LOG ON WAS_PARTNER_LOG ( "RU_ID" ASC, "SERVICE_ID" ASC, "SERVER_NAME" ASC)

The following DDL structures show how to create the database table on postgreSQL:

CREATE TABLE WAS_TRAN_LOG (
SERVER_NAME VARCHAR(128),
SERVICE_ID SMALLINT,
RU_ID BIGINT,
RUSECTION_ID BIGINT,
RUSECTION_DATA_INDEX SMALLINT,
DATA BYTEA)

CREATE TABLE WAS_PARTNER_LOG (SERVER_NAME VARCHAR(128),
SERVICE_ID SMALLINT,
RU_ID BIGINT,
RUSECTION_ID BIGINT,
RUSECTION_DATA_INDEX SMALLINT,
DATA BYTEA)

The following DDL structures show how to create indexes for these tables:

CREATE INDEX IXWSTRAN_LOG ON WAS_TRAN_LOG ( RU_ID ASC, SERVICE_ID ASC, SERVER_NAME ASC)
CREATE INDEX IXWSPARTNER_LOG ON WAS_PARTNER_LOG ( RU_ID ASC, SERVICE_ID ASC, SERVER_NAME ASC)

The following DDL structures show how to create the database table on Microsoft SQL Server:

CREATE TABLE WAS_TRAN_LOG (
SERVER_NAME VARCHAR(128),
SERVICE_ID SMALLINT,
RU_ID BIGINT,
RUSECTION_ID BIGINT,
RUSECTION_DATA_INDEX SMALLINT,
DATA VARBINARY(MAX))

CREATE TABLE WAS_PARTNER_LOG (
SERVER_NAME VARCHAR(128),
SERVICE_ID SMALLINT,
RU_ID BIGINT,
RUSECTION_ID BIGINT,
RUSECTION_DATA_INDEX SMALLINT,
DATA VARBINARY(MAX))

The following DDL structures show how to create indexes for these tables:

CREATE INDEX IXWSTRAN_LOG ON WAS_TRAN_LOG ( "RU_ID" ASC, "SERVICE_ID" ASC, "SERVER_NAME" ASC)
CREATE INDEX IXWSPARTNER_LOG ON WAS_PARTNER_LOG ( "RU_ID" ASC, "SERVICE_ID" ASC, "SERVER_NAME" ASC)