+

Search Tips   |   Advanced Search

(iseries)(dist)(v8550)

Storing transaction and compensation logs in a relational database for high availability

We can, optionally, choose to store the WAS transaction and compensation 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.

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. If a WAS with active transactions restarts after a failure, the transaction service is able to use the logs to replay any in-doubt transactions. This level of integrity allows the overall system to be brought back to a consistent state.

In previous releases of WebSphere Application Server, the transaction logs were stored as operating system files. In WebSphere Application Server v8.5.5 and later, this setup remains the default configuration. We can also choose to store the transaction logs in a relational database. This configuration option is primarily used for a high availability (HA) environment. Also, in previous releases of WAS, HA transaction support required 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). This new feature enables you, particularly if we have an investment in HA database technology, to use the HA database as a shared repository for the transaction logs as an alternative to using a shared file system.

The current implementation for storing the transaction and compensation logs in a relational database does not ensure support for some database-specific High Availability capabilities. For example, client-specific features such as DB2 HADR or Oracle RAC DataGuard that enable reconnection to another database instance in the event of a failure might not be supported.

In the current implementation, if unexpected JDBC exceptions are encountered by the core recovery log function, transaction logging is disabled and the server must be shut down so that in-flight transactions can be recovered. No attempt is made to reconnect until the server is restarted, and no attempt is made by the current implementation to determine whether the condition is transient.

In WebSphere Application Server v8.5.5 and later, we can use a similar facility, also aimed at customers that are working in a HA environment, to store the compensation recovery logs in a relational database. The WebSphere Application Server compensation service enables applications on disparate systems to coordinate activities that are more loosely coupled than atomic transactions. It stores information that is necessary to complete compensation after a system failure in its own dedicated recovery logs.

The datasource of the SQL Recovery Log needs a max pool size of (2 x the number of potential servers being peer recovered)+2. This max pool size will allow for the sufficient connections to the database to close all the related transaction logs. Not having a max pool size set to this value can lead to the error message, J2CA0045E, because there are not sufficient connections availablegotcha

We must configure the transaction log location and the compensation log location for each server in the cluster before we can enable high availability, by setting the TransactionLogDirectory and CompensationLogDirectory attributes for each server. Each server in a cluster must reference unique transaction log and compensation log locations by specifying a distinct tablesuffix property, so that multiple servers do not contend for relational database management system (RDBMS) resources. For example, if we have a cluster that is named AppCluster with the following four member servers:

We can define the following table suffixes for AppCluster:

Complete the following steps:

  1. Configure a non-transactional data source for transaction and compensation recovery log storage:

    1. Create a JDBC provider for the specific RDBMS implementation. Specify an implementation type of non-XA.

    2. Create a JAAS J2C authentication data alias. This data alias defines the security credentials used to connect to the RDBMS. The credentials defined in the RDBMS must have sufficient authority to create tables in the database.

    3. Create a data source using the JDBC provider created in step a. Its component managed authentication alias must be set to the JAAS alias created in step b. Define the URL for your data source to specify a connection to the RDBMS.

    4. Configure the new data source to be non-transactional by completing the following steps:

      1. Open the newly created data source.

      2. Under Additional Properties, click WebSphere Application Server data source properties.

      3. Select the Non-transactional data source check box.

      4. Save the changes.

  2. Configure the transaction service to store transactions in a relational database:

    1. In the WAS console, click Servers > Server Types > WebSphere application servers > server_name. The properties of the specified application server are displayed.

    2. In the Container Settings section, click Container Services > Transaction service. The transaction service settings page is displayed.

    3. Select the Configuration tab if it is not already displayed.

    4. In the Transaction log directory field, enter a custom string to indicate we want the logs to be stored in a database. The string must have the following format:

        custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=data_source_jndi_name,tablesuffix=suffix

      where data_source_jndi_name is the JNDI name of the non-transactional data source that was created previously, and suffix is a string that set to uniquely label each member of the HA cluster.

      Restriction: For an Oracle database, the length of the suffix string must not exceed 15 characters.

  3. Optional. Configure the compensation service to store transactions in a relational database if you plan to use Compensation or Activity services in WebSphere Application Server:

    1. In the WAS console, click Servers > Server Types > WebSphere application servers > server_name. The properties of the specified application server are displayed.

    2. In the Container Settings section, click Container Services > Compensation service. The compensation service settings page is displayed.

    3. Select the Configuration tab if it is not already displayed.

    4. In the Recovery log directory field, enter a custom string to indicate we want the logs to be stored in a database. The string must have the following format:

        custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=data_source_jndi_name,tablesuffix=suffix

      where data_source_jndi_name is the JNDI name of the non-transactional data source that was created previously, and suffix is a string that set to uniquely label each member of the HA cluster.

      Restriction: For an Oracle database, the length of the suffix string must not exceed 15 characters.

  4. Optional. Create the database tables.

    WebSphere Application Server attempts to create the necessary database tables when it first starts. When this creation is not possible, due to insufficient permission for example, the server fails to start. Under these circumstances, create the three database tables manually.

    The following DDL is representative and appropriate to a stand-alone server environment. In a stand-alone environment, two database tables, a transaction log and a partner log table are needed to support the transaction service. To use Compensation or Activity Services, then a third compensation log table is also needed.

    The table names can be tailored for the environment. For example, create four transaction log tables, four partner log tables and optionally, four compensation log tables if we have a cluster that is named AppCluster with the following four member servers:

    • AppClusterMember1

    • AppClusterMember2

    • AppClusterMember3

    • AppClusterMember4

    So we can define the following tables for AppCluster:

    Cluster Name Server Name Transaction Log Table Partner Log Table Compensation Log Table
    AppCluster AppClusterMember1 WAS_TRAN_LOGApp1 WAS_PARTNER_LOGApp1 WAS_COMP_LOGApp1

    		AppClusterMember2 WAS_TRAN_LOGApp2 WAS_PARTNER_LOGApp2 WAS_COMP_LOGApp2

    		AppClusterMember3 WAS_TRAN_LOGApp3 WAS_PARTNER_LOGApp3 WAS_COMP_LOGApp3

    		AppClusterMember4 WAS_TRAN_LOGApp4 WAS_PARTNER_LOGApp4 WAS_COMP_LOGApp4
    The following DDL shows how to create the database tables with the App1 table suffix on DB2: 

    CREATE TABLE WAS_TRAN_LOGApp1(
  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_LOGApp1(
  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_COMP_LOGApp1(
  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 the database table on Oracle: 

    CREATE TABLE WAS_TRAN_LOGApp1(
  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_LOGApp1(
  SERVER_NAME VARCHAR(128), SERVICE_ID SMALLINT, RU_ID NUMBER(19), RUSECTION_ID NUMBER(19), RUSECTION_DATA_INDEX SMALLINT, DATA BLOB)
    CREATE TABLE WAS_COMP_LOGApp1(
  SERVER_NAME VARCHAR(128), SERVICE_ID SMALLINT, RU_ID NUMBER(19), RUSECTION_ID NUMBER(19), RUSECTION_DATA_INDEX SMALLINT, DATA BLOB)


Example

If we have a cluster that is named AppCluster, with four member servers AppClusterMember1, AppClusterMember2, AppClusterMember3, and AppClusterMember4, then configure the log locations like this:

Cluster Name Server Name Transaction Log Location Compensation Log Location
AppCluster AppClusterMember1 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App1 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App1
AppClusterMember2 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App2 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App2
AppClusterMember3 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App3 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App3
AppClusterMember4 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App4 custom://com.ibm.rls.jdbc.SQLRecoveryLog?datasource=jdbc/tranlog,tablesuffix=App4
In this example, the table suffix is set to as follows:

The database tables with the following names are created: