|
Problem |
A problem with two phase commit (XA) transactions and IBM®
Informix® Dynamic Server is identified. This issue could cause a number of
exceptions including, but not limited to:
"java.sql.SQLException: Could not position within a table"
or
"java.sql.SQLException: Could not do a physical-order read to fetch next
row."
This problem could affect a number of WebSphere® Application Server
applications that require XA transactions support from IBM Informix
Dynamic Server. |
|
Cause |
You should to tune the Informix Dynamic Server and set
some Informix environment variables as custom properties in WebSphere
Application Server. |
|
Solution |
Complete the following steps to tune your Informix Dynamic
Server:
- Log into the administrative console for WebSphere Application
Server.
- Click Resources > JDBC Providers > JDBC_provider
> Data sources > data_source.
- Under Additional properties, click Custom properties >
New.
- 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.
The following recommendations are optional:
- Set the Informix Lock Mode Wait property to a higher level than the
default.
- Click Resources > JDBC Providers > JDBC_provider
> Data sources > data_source to return to the data
source configuration panel.
- 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.
|
|
|
|
|
|
|