XA Transactions and IBM Informix Dynamic Server

Resolving The Problem

Complete the following steps to tune your Informix Dynamic Server:

1. Log into the administrative console for WebSphere Application Server.


2. Click Resources > JDBC Providers > JDBC_provider > Data sources > data_source.


3. Under Additional properties, click Custom properties > New.


4. Enter ifxIFX_XASPEC in the Name field and Y in the Value field to activate the IFX_XASPEC variable.
   
   When you activate this property, it enforces a tight coupling of XA transactions within the same global transaction ID and it requires that the transactions share lock space. The IFX_XASPEC variable only applies to XA connections and cannot be specified in a database URL. The property can be specified by a data source or by setting a system Java™ virtual machine (JVM) property with the same name. The data source property overrides the system property.
   Note: The value for this property is not a Boolean value. When you set this property value, it overrides the system setting in the Informix Dynamic Server.
   

• Set the Informix Lock Mode Wait property to a higher level than the default.




1. Click Resources > JDBC Providers > JDBC_provider > Data sources > data_source to return to the data source configuration panel.


2. For the Informix Lock Mode Wait value, specify a value greater than 2, which is the default.
   
   This property enables you to specify the number of seconds that the Informix Dynamic Server waits to acquire a record lock. By default, the Informix Dynamic Server provides an exception if it cannot acquire the lock immediately. It is recommended that you set the property to 1000 seconds.
   




• Use the Informix DB Access utility to run the following command against the Informix Dynamic Server:
  update statistics high
  This command updates the statistics in the system catalogs that the optimizer uses to determine the lowest-cost query plan.
  



• Change to the row locking mode for a particular table if many transactions will update individual rows in a table. You can specify the following SQL statement using the Informix DB Access Utility:
  ALTER TABLE table_name LOCK MODE(ROW)
  This SQL statement creates indexes for the column that your application queries or updates.
  



• Use the DISABLE_B162428_XA_FIX configuration parameter, which is an ONCONFIG parameter in the Informix Dynamic Server. If a transaction is rolled back by the server for another action other than a XA rollback by the client, the XID is not forgotten and the transaction is in the rollback state only. You can set this parameter to the following values:
  
  0
  The zero (0) value frees global transactions only when a XA rollback is called. This value is the default behavior for Informix Dynamic Server Version 10. This behavior is required to confirm to the XA state table that the server can free a transaction only after an XA rollback is called.
  
  1
  The one (1) value frees transactions if transaction rollback takes effect for other actions other than a XA rollback when the database server is shut down and restarted. This value immediately frees all of the global transactions after a transaction rollback, which is the default behavior for Informix Dynamic Server Version 9.4 and earlier. Set the parameter value to one (1), if you have applications that were written for previous versions of Informix Dynamic Server.
  
  You can override the DISABLE_B162428_XA_FIX configuration parameter of the server for a client session with the IFX_XASTDCOMPLIANCE_XAEND environment variable. You might use this environment variable when the server instance is disabled for the new behavior by the ONCONFIG parameter, but one client requires the new behavior. You can specify the behavior of XA_END when XA_RB* is returned by setting the IFX_XASTDCOMPLIANCE_XAEND environment variable. You can set this parameter to the following values:
  
  0
  The zero (0) value specifies that the XID is forgotten and the transaction is nonexistent. This behavior is the default for Informix Dynamic Server Version 9.4.
  
  1
  The one (1) value specifies that the XID is not forgotten and the transaction is in the rollback state only. This value is XA_SPEC+ compliant and the default behavior for Informix Dynamic Server Version 10.