Storing and restoring transaction and compensation logs for high availability

You can, optionally, choose to store your WebSphere® Application Server 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. You can also restore your transaction and compensation logs from the relational database to the operating system files.

(2 * the number of potential servers being peer recovered) + 2

Complete the following steps:

1. Configure a non-transactional data source for transaction and compensation recovery log storage:
   1. Create a JDBC provider for your 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 that are used to connect to the RDBMS. The credentials that are defined in the RDBMS must have sufficient authority to create tables in the database.
      • Access to the database is made under the JAAS J2C authentication data alias that is associated with the data source that is configured for the transaction logs storage. As such, the JAAS J2C authentication data alias needs at least SESSION privilege to connect to the database. If the user ID associated with the JAAS J2C authentication data that is accessing the database owns the schema in which the transaction logs are to be stored, WebSphere Application Server has sufficient privilege to manipulate the tables. Otherwise, the data alias requires SELECT, INSERT, UPDATE, and DELETE privileges on the tables that comprise the transaction logs. Additionally, if the log tables are not present on server startup and are to be created automatically, the data alias requires sufficient privilege to create tables and indexes in the transaction logs schema. The data alias also requires a space quota in the default tablespace of the owner of the schema in which the transaction logs are to be stored.
   3. Create a data source by using the JDBC provider that is 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 your 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 your changes.
2. Configure the transaction service to store transactions in a relational database:
   1. In the WebSphere Application Server administrative 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 that you want your 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 you must set to uniquely label each member of your HA cluster.

      Restriction: If you are using 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 WebSphere Application Server administrative 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 that you want your 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 you must set to uniquely label each member of your HA cluster.

      Restriction: If you are using 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, you must 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. If you plan to use Compensation or Activity Services, then a third compensation log table is also needed.

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

   • AppClusterMember1
   • AppClusterMember2
   • AppClusterMember3
   • AppClusterMember4

   So you 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_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)

   CREATE TABLE WAS_COMP_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 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)

5. Optional: Restore the transaction and compensation logs from the relational database to the operating system files.
   1. Stop the application server normally.
   2. Verify that all transactions are complete by checking the system output log for the WTRN0105I message:

      WTRN0105I: The transaction service has shutdown successfully with no transactions requiring recovery.

      If WTRN0105I is not in the log, then restart the application server in recovery-only mode. This mode requires a non-HA environment.

   3. Modify the transaction log directory setting for the application server and synchronize the nodes.
   4. Restart the application server.

   1. Modify the transaction log directory only when ready to stop the application server. Then, stop the application server normally.
   2. Verify that all transactions are complete by checking the system output log for the WTRN0105I message:

      WTRN0105I: The transaction service has shutdown successfully with no transactions requiring recovery.

      If WTRN0105I is not in the log, transactions might not have completed. You might need to recover incomplete transactions. The simplest fix to this problem might be to send the log to IBM Support.

   3. Restart the application server.