Configuring Informix Dynamic Server with WebSphere Application Server

Resolving The Problem

1. Introduction

WebSphere Application Server is a transaction server that features high performance and an extremely scalable transaction engine capable of handling high-volume secure transactions and Web services. Application servers are middleware that handle application functions between users and the back-end applications and databases of a business.

Informix Dynamic Server is an extensible object-relational database server that supports on-demand computing, WebSphere, and OLTP and OLAP data management applications in the SQL, Dynamic SQL, C++, and Java™ language

Informix database can be used as the back-end resources for the WebSphere Application Servers.

2. Overview

This article provides a brief overview of configuring Informix Dynamic server with WebSphere Application Server. Though it is easy to setup WebSphere Application Server along with Informix Dynamic Server, understanding some of the nuances of the setup can be necessary for correct operation at runtime. In this article, we attempt to describe some of the common problems one might encounter and their resolutions. We also include tips on installation and using custom properties to setup Informix Dynamic Server environment variables.

3. Installation of WebSphere Application Server and Informix Dynamic Server

Informix Dynamic Server and WebSphere Application Server are products that need to be installed separately. The intent of this article is not to give detailed instructions for installation, but to give additional tips on addressing problems encountered during the deployment. In the following two sections, we only give a brief overview of the installation process. For a detailed account of the installation process please consult the respective product documentation (Reference (6.1.1) and Reference (6.1.2))

3.1 Installing WebSphere Application Server:
IBM WebSphere Application Server is an integrated platform that contains an Application Server, a set of Web development tools, a Web server, and additional supporting software and documentation. The product can be installed in various topologies on one or more machines:

Scenario 1: Single-machine installation of WebSphere Application Server
Scenario 2: Single-machine installation of WebSphere Application Server and a Web server
Scenario 3: Two-machine installation of WebSphere Application Server and a Web server
Scenario 4: Creating multiple profiles that use one installation of WebSphere Application Server

Installation of the product requires the user to log on as a member of the administrator group. Some steps of the installation procedure require the administrator group ID to have the following advanced user rights - Act as part of the operating system and Log on as a service.

The launchpad console is the starting point for installing WebSphere Application Server products.
The launchpad program is available on the root directory of the product CD in a program named:
launchpad.bat or launchpad.sh. Once the launchpad is started, it provides the users with the options of installing – WebSphere Application Server, IBM HTTP Server installation, Web Server Plug-in installation, Application Client installation. Application Server toolkit installation.

For detailed instructions for planning, installing and configuring WebSphere Application Server please consult the “Installing you application serving environment “topic in the WebSphere Application Server information. (Reference (6.1.1))

3.2 Installing Informix Dynamic Server Informix Dynamic Server
Informix Dynamic Server for Windows® includes a Windows launch pad, a graphical interface with the options to view the manual or the release notes, start the installation, or browse the CD. The launch pad will start automatically when the CD is inserted.

For detailed instructions for planning, installing and post installation tasks for Informix Dynamic Server, please consult the “Installing Informix Dynamic Server Enterprise and Workgroup Edition “topic in the Informix Dynamic Server Information center. (Reference (6.1.2))

4. Configuring
This section describes the steps for creating a JDBC provider for Informix Dynamic Server in WebSphere and configuring a datasource by adding and modifying values in the datasource Custom Properties.
4.1 Creating JDBC providers
WebSphere administrative console can be used to create various JDBC providers for Informix Dynamic Server. The two implementation types described here are XA datasource and Connection Pool datasource

For XA Datasource JDBC providers, user needs to provide the following in the CLASSPATH:

${INFORMIX_JDBC_DRIVER_PATH}/ifxsqlj.jar
${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbc.jar
${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbcx.jar

And implementation class name is set to:

com.informix.jdbcx.IfxXADataSource

For Connection Pool datasource, user needs to provide the following the CLASSPATH

${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbc.jar
${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbcx.jar

And implementation class name is set to:

com.informix.jdbcx.IfxConnectionPoolDataSource

4.2 Setting Informix environment variables in WebSphere Application Server

A datasource can be created under either of the two JDBC providers described in the previous section, depending on the application requirement. After the datasource creation, additional environment parameters required by the Informix dynamic server can be added or modified using the Custom Property link in the WebSphere Application Server administrative console. User can navigate to Custom properties link:

Resource > JDBC Provider > XA or non-XA JDBC provider Name > Datasource name

used by the application.

New environment parameters required by Informix Dynamic Server can be added using the “New” button under Custom properties. To modify an existing parameter, click on the required parameter and provide the new value. Apply the changes. The change would be made in the local configuration now. Clicking “Save” at the top of the page (inside the Message box) to applies the changes to the master configuration. The WebSphere Application Server may need to be restarted for these changes to take effect.

5. Troubleshooting
In this section we address some of the commonly encountered problems and ways to address them. An attempt is also made to list out some of the XA Transaction problems and general errors observed when using Informix Dynamic Server with WebSphere Application Server and their resolutions.

5.1 XA Transaction problems

This section lists some of the error symptoms and ways to address them.

5.1.1 Symptom: Enabling tightly coupled XA transactions in Informix Dynamic server

When tightly coupled transactions are not enabled in the Informix Dynamic Server, user might come across one of the following symptoms. The exception is thrown as follows:
java.sql.SQLException: ISAM error: record is locked.
java.sql.SQLException: Could not do a physical-order read to fetch next row.

The ISAM message is slightly different depending on whether "set lock mode to wait <seconds>" is set or not.

If the “set lock mode not set”, the message is:

ISAM error: record is locked.

With "set lock mode to wait <seconds>", the application waits for the timeout, and then throws the exception:

ISAM error: Lock Timeout Expired

5.1.2 Symptom: “Could not do a physical-order read to fetch next row” error in the SystemOut.log of WebSphere Application Server

When multiple users or clients access the same Informix database doing XA transactions concurrently, you might get the following error in the SystemOut.log of WebSphere Application Server:

<code>
Caused by: java.sql.SQLException: Could not do a physical-order read to fetch next row.DSRA0010E:
SQL State = IX000, Error Code = -244
at com.informix.jdbc.IfxSqli.a (IfxSqli.java (Compiled Code))
at com.informix.jdbc.IfxSqli.E (IfxSqli.java(Compiled Code))
</code>

Resolution for 5.1.1, 5.1.2:
You might need to tune the Informix database server and set some informix environment variables in WebSphere Application Server custom properties.

The following Informix configuration parameter needs to be set:

As per the XA/Open specifications, if a transaction is rollback by the server, for other than an XA rollback by the client, the XID is not forgotten and the transaction is in Rollback only state. This is the default behavior for the 10.00 Informix dynamic servers. To get old behavior, use the following ONCONFIG parameter in the informix dynamic server:
DISABLE_B162428_XA_FIX
0 = (Default) Frees transactions only when an XA rollback is called
1 = Frees transactions if transaction rollback for other than an XA rollback
takes effect when the database server is shut down and restarted

Set DISABLE_B162428_XA_FIX to 1 to immediately free all global transactions after a transaction rollback, which is the default for Dynamic Server 9.40 and earlier versions. The default behavior for Dynamic Server 10.0 is to free global transactions after an xa_rollback is called, and this behavior is required to confirm to the XA state table that a transaction can be freed only after xa_rollback is called. Setting DISABLE_B162428_XA_FIX to 1 ensures that applications written for the earlier version of Dynamic server work properly.

For the 9.40 Informix dynamic servers, the ONCONFIG parameter ENABLE_B162428_XA_FIX is set to 1 to get the XA_SPEC+ compliant behavior .

You can override the DISABLE_B162428_XA_FIX configuration parameter of the server for a client session with the IFX_XASTDCOMPLIANCE_XAEND environment variable. This environment variable can be particularly useful when the server instance is disabled for new behavior by the ONCONFIG parameter, but one client requires the new behavior. The behavior of XA_END when XA_RB* is returned is specified by the setting of IFX_XASTDCOMPLIANCE_XAEND.It can take the following values:

1 XID is not forgotten. Transaction is in Rollback only state. This is XA_SPEC+ compliant and is the default behavior with Informix Dynamic Server 10.0.

0 XID is forgotten. Transaction is Nonexistent. This is default behavior with Informix Dynamic Server 9.40.

The following Informix specific environment variables need to be set in WebSphere Application Server custom properties:

Turn on the IFX_XASPEC variable

Activating the property enforces tight coupling of XA transactions within the same global transaction ID, and enables the transactions to share lock space. The IFX_XASPEC only applies to XA connections and cannot be specified in a database URL. It can be specified by dataSource or by setting a System (JVM) property with the same name. The DataSource property will override the System property. Any values for the properties other than y, Y, n, or N are ignored. IfxDataSource.getIfxIFX_XASPEC returns the final IFX_SPEC value, which is either y or n. For example if the value of DataSource IFX_XASPEC equals n and the value of the System IFX_XASPEC equals Y or y, n will be returned.

How to set this property in WebSphere Application Server custom property: Logon WebSphere Application Server administrative console; Create a new custom property IfxIFX_XASPEC for the informix XA datasource; set it to Y or y. Please note that it is not a Boolean value and your setting for the property overrides the Informix database system setting.
· Set “Informix Lock Mode Wait” for the datasource to a higher value

Although not required, this property enables you to set the number of seconds that Informix dynamic server waits for a lock. By default, Informix dynamic server code throws an exception if it cannot acquire a lock immediately. Informix Lock Mode Wait can be set to 1000.

The following Informix Dynamic Server tuning can be performed:

• Run the command "update statistic high" against the database. The UPDATE STATISTICS statement updates the statistics in the system catalogs that the optimizer uses to determine the lowest-cost query plan. If many transactions will be updating individual rows, change to row locking mode for a particular table using the following sql statement:
• ALTER TABLE <table name> LOCK MODE (ROW)
  Create indexes for the column that your application is querying or updating.

Server /JDBC Version with the fix:
9.40. UC6 and higher version of Informix Dynamic Server
10.00. UC1 and higher version of Informix Dynamic Server
3.00. JC1 JDBC driver

5.1.3 Symptom: Informix transaction isolation settings in a session are not getting propagated across XA global transactions

User might see the following exception:
A SQLException "Could not position within a table" with a nested SQLException "Lock Timeout Expired" thrown.

This happen because Informix dynamic server resets the transaction isolation setting to the database default when the XA transaction ends. The change happens on the database, so WebSphere is not aware that it changed and may not reset the transaction isolation level the next time the connection is retrieved from the pool. This causes the connection and the Informix dynamic server to get out of synchronization.

The transaction isolation setting will be reset to default setting depending on the Informix database logging type:
o Database without transaction logging Read Uncommitted
o Database with logging that is not ANSI-compliant Read Committed
o Database with logging that is ANSI-complaint Serializable

If attempting to use READ_UNCOMMITTED default read transaction isolation with the WebSphere scheduler service, all scheduler read operations will block and the customer may receive "Lock Timeout Expired" SQLException exceptions for any long-running tasks included in the read operation.



Resolution

In order to enable the tightly coupled XA transactions in Informix Dynamic server, please refer to the resolution for symptom 5.1.1. With the fix, the values for isolation level and lock mode are complete-connection level setting. Complete-connection level setting allows propagation of value set in local environment to all new/resume transactions and if the value is changed within a transaction, then it will be propagated back to the local environment and also to all subsequent (new/resume) transactions.

Server /JDBC Version with the fix:
9.40. UC7W1 and later versions of Informix Dynamic Server
10.00. UC3W4 and later versions of Informix Dynamic Server
3.00. JC3 JDBC driver


5.2 Some of the other problems and their resolutions

5.2.1 Symptom: “Null Pointer Exception on BLOB/SMARTBLOB column” in the SystemOut.log of WebSphere Application Server


A Null Pointer Exception is returned in the SystemOut.log of WebSphere Application Server when IfxBblob or IfxCblob is created using serialized Object and send to the server

Resolution
IfxBblob (inputStream), IfxCblob (inputStream), IfxCblob (inputStream) and IfxCblob (locator) cannot be used to access the server. It is MODE_CLIENT_ONLY.


5.2.2 Symptom: Messaging engine of WebSphere Application Server can't startup due to a defect in the Informix JDBC Driver 3.00JC1

When the messaging engine uses the Informix JDBC driver 3.00JC1 to store its data,
the following error message is observed in the application Server's SystemOut.log,
and the messaging engine can't startup too.

[…..] CWSIS0002E: The messaging engine encountered an exception while starting.
Exception: com.ibm.ws.sib.msgstore.PersistenceException: CWSIS1501E: The dataSource has produced an unexpected exception: java.sql.BatchUpdateException: Unique
Constraint (informix.u114_62) violated.


Resolution
The problem observed is due to a defect in the Informix JDBC Driver 3.00JC1.
Upgrade the Informix JDBC Driver to 3.00JC2.


5.2.3 Symptom: ApplicationNotFindException in the SystemOut.log of WebSphere Application Server when trying to access modules generated by EJB

When an entity (CMP) gets the string from the table, "white spaces" are inserted into the result set, which can cause ApplicationNotFindException

For example:
A failed event can be stored in the informix database and the event’s Destination_module_name" is "HelloWorldWithBO"(16 chars).
To get the details of this failed event, FailedEventManagerEJBBean.getFailedEventWithParameters is called, which in turn executes loadClassContext (fel.getDestination_module_name ());
fel.getDestination_module_name () returns:
"HelloWorldWithBO "(255 chars),
instead of "HelloWorldWithBO"(16 chars).
So the module can't be found, thereby ApplicationNotFindException is thrown.

Resolution
EJB generated schema DDL uses CHARACTER for the column type by default. In the example mentioned above, Destination_Module_Name is defined as CHARACTER (255)

Use VARCHAR or LVARCHAR datatype instead of character datatype:

• CHAR - stores single-byte or multibyte text strings of fixed length (up to 32,767 bytes); supports code-set order in collation of text data. Default size is 1 byte
• VARCHAR - stores single-byte or multibyte text strings of varying length (up to 255 bytes); supports code-set order collation of text data.
• LVARCHAR (Informix Dynamic Server) - stores single-byte or multibyte text strings of varying length (up to 32,739 bytes). The size of other columns in the same table can further reduce this upper limit. Default size is 2,048 bytes.

5.2.4 Symptom: Unable to Connect using localhost when Informix Dynamic server is installed locally

When dynamic server is installed locally along with WebSphere Application Server the connection to Informix Dynamic Server fails when using localhost. The same issue is observed when configuring dataSource in WebSphere Application Server.

Resolution

• In Windows Platform, use “*<hostname>” in the host information tab of setnet32 to get both IP address (127.0.0.1) and localhost to will work for informix connections.
• In Solaris platform the DATABASESERVERALIASES ONCONFIG parameter can be used to resolve the localhost problem.
  
  For example: Set DATABASESERVERALIASES to patriot1_local in ONCONFIG file of Informix Dynamic Server and add the following line $INFORMIXSQLHOSTS ($INFORMIXDIR/etc/sqlhosts)

patriot1_local ontlitcp 127.0.0.1 18551

5.2.5 Symptom: Informix 2.21.JCX JDBC driver returns wrong holdability value

Informix 2.21.JCX JDBC driver returns wrong holdability value. It is required for WebSphere Application Server 6.0

Resolution

The fix is available in later version of Informix JDBC driver

JDBC Version with the fix:
3.00. JC1 JDBC driver

5.2.6 DATABASE METADATA GETTABLES FAILS FOR ANSI DB

The informix dynamic server converts the schema/owner to uppercase for ANSI database.
But the JDBC driver converts schema/owner to lowercase . So getTables(...) fails
for some cases.

Resolution

The fix is available in later version of Informix JDBC driver

JDBC Version with the fix:
3.00. JC1 JDBC driver

6. References

6.1.1 For more information on WebSphere Application Server
How do I install an application serving environment? provides information on installing WebSphere Application Server in Windows and UNIX® platform in the different configuration.


6.1.2 For more information on Informix Dynamic Server
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp

• Details on the configuration parameters for Informix Dynamic Server (ONCONFIG) can be obtained from the Administrator’s Reference guide under the IDS Library.
• The JDBC related environment parameters can be obtained form JDBC programming guide
• Details on installation of Informix Dynamic Server can be viewed under the “Installing IDS Enterprise and Workgroup Edition“ in Getting Started topic