 | Level: Intermediate Anju Sudhakar (anjus@us.ibm.com), Quality Assurance Engineer, IBM
16 Feb 2006 WebSphere® Application Server (Application Server) is a high-performance transaction server that features 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 (IDS) 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™ languages. You can use IDS as the backend resource for Application Server.
Installing Application Server and IDS
You must install IDS and Application Server separately. For a detailed account of the installation process, including planning and configuring, see the product documentation links in the resources section.
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. You can install WebSphere Application Server in various configurations on one or more systems. The following list includes typical installation scenarios:
- Single-system installation of WebSphere Application Server
- Single-system installation of WebSphere Application Server and a Web server
- Two-system installation of WebSphere Application Server and a Web server
- Creating multiple profiles that use one installation of WebSphere Application Server
Installing WebSphere Application Server requires you to log on as a member of the Administrator group. Some steps of the installation procedure require the Administrator group ID to act as part of the operating system and to log on as a service.
The WebSphere Launchpad is the starting point for installing WebSphere Application Server products. The launchpad program is available in the root directory of the product CD in a program named launchpad.bat or launchpad.sh. After the launchpad starts, you can select the following components to install:
- WebSphere Application Server
- IBM HTTP Server
- Web Server Plug-in
- Application Client
- Application Server toolkit
Installing Informix Dynamic Server
Informix Dynamic Server for Windows® includes the Informix Dynamic Server Launchpad from which you can view the documentation or release notes, install IDS, or browse the CD.
Configuring
This section describes the steps for creating a JDBC™ provider for IDS in WebSphere and configuring a data source by adding and modifying values in the data source Custom Properties.
Creating JDBC providers
You can use the WebSphere Administrative Console to create JDBC providers for IDS. The two implementation types covered in this article are XA data source and Connection Pool data source:
- For XA data source JDBC providers, set the implementation class name to
com.informix.jdbcx.IfxXADataSource. Also, create the following CLASSPATH entries, where ${INFORMIX_JDBC_DRIVER_PATH} is the path where your JDBC driver is installed. The ifxsqlj.jar is used when using SQLJ with WebSphere Application Server.
${INFORMIX_JDBC_DRIVER_PATH}/ifxsqlj.jar
${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbc.jar
${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbcx.jar |
- For Connection Pool data sources, set the implementation class name to
com.informix.jdbcx.IfxConnectionPoolDataSource. Also create the following CLASSPATH entries:
${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbc.jar
${INFORMIX_JDBC_DRIVER_PATH}/ifxjdbcx.jar |
Setting Informix environment variables in Application Server
You can create a data source under either of the two JDBC providers that are described in the previous section, depending on the application requirements. After you create the data source, you can use the Custom Property link in the Application Server Administrative Console to add or modify environment parameters that Informix Dynamic Server requires. You can navigate to the Custom properties link from Resource -> JDBC Provider -> JDBC provider Name (either XA or Connection Pool) -> Data source name used by the application.
You can also add new environment parameters that are required by Informix Dynamic Server using the New button under Custom Properties. To modify an existing parameter, click the required parameter and type the new value. Click Apply to immediately make the change in the local configuration. Click Save at the top of the page (inside the message box) to save the changes to the master configuration. You might need to restart Application Server for these changes to take effect.
Troubleshooting
This section addresses common problems and their resolutions. Some of the XA transaction problems and general errors that you might receive when using IDS with Application Server are also addressed.
XA transaction problems
Both of these problems have a common resolution.
- Symptom: SQL Exceptions because tightly coupled transactions are not enabled in IDS.
When tightly coupled transactions are not enabled in IDS, you might receive one of the following exceptions:
- java.sql.SQLException: ISAM error: The 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. If you set it to SET LOCK MODE NOT WAIT, 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.
- Symptom: Message "Could not do a physical-order read to fetch next row" error in the SystemOut.log of Application Server.
When multiple users or clients access the same Informix database doing XA transactions concurrently, you might get the following error in the Application Server file SystemOut.log:
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))
|
Resolution: You might need to tune IDS and set some Informix environment variables in Application Server custom properties. The following Informix configuration parameter needs to be set:
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.
According to the XA/Open specifications, if a transaction is rolled back 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 behavior is the default for Informix Dynamic Server, Version 10.0. To return to the previous behavior, set the following ONCONFIG parameter in IDS:
Set DISABLE_B162428_XA_FIX to 1 to immediately free all global transactions after a transaction rollback, which is the default for Dynamic Server V9.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 Informix Dynamic Server Version 9.40, the ONCONFIG parameter ENABLE_B162428_XA_FIX is set to 1 for 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:
IFX_XASTDCOMPLIANCE_XAEND
- 1: XID is not forgotten. Transaction is in Rollback-only state. This is XA_SPEC+ compliant and is the default behavior with IDS 10.0.
- 0: XID is forgotten. Transaction is Nonexistent. This is default behavior with IDS V9.40.
The following Informix-specific environment variables must be set in Application Server custom properties:
- Turn on the
IFX_XASPEC variable.
Activating this variable enforces tight coupling of XA transactions within the same global transaction ID, and enables the transactions to share lock space. IFX_XASPEC only applies to XA connections and cannot be specified in a database URL. It can be specified by data source or by setting a system property (of the JVM) with the same name. The data source property overrides 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 data source IFX_XASPEC equals n and the value of the system IFX_XASPEC equals Y or y, n is returned.
How to set this property in Application Server custom property:
- Log on to the Application Server Administrative Console.
- Create a new custom property ifxIFX_XASPEC for the Informix XA data source; set it to Y or y. This value is not a Boolean value, and your setting for the property overrides the database system setting.
- Set Informix Lock Mode Wait for the data source 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 throws an exception if it cannot acquire a lock immediately. Informix Lock Mode Wait can be set to 1000 seconds.
You can try the following tuning steps:
- Run the command UPDATE STATISTICS HIGH against the database. This command 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.
Software with the update:
- Informix Dynamic Server, Version 9.40.UC6
- Informix Dynamic Server, Version 10.00.UC1
- 3.00. JC1 JDBC driver
- Symptom: Informix transaction isolation settings in a session are not getting propagated across XA global transactions.
You might see the following exception message:
A SQLException "Could not position within a table" with a nested
SQLException "Lock Timeout Expired" thrown. |
This exception is caused because IDS 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 might not reset the transaction isolation level the next time the connection is retrieved from the pool. This causes the connection and IDS to get out of synchronization.
When the transaction isolation setting is reset, it differs depending on the Informix database logging type:
Transaction isolation defaults differ by IDS logging type
| Logging type | Default transaction isolation |
|---|
| Database without transaction logging | Read uncommitted | | Database with logging that is not ANSI-compliant | Read committed | | Database with logging that is ANSI-complaint | Serializable |
If you attempt to use READ_UNCOMMITTED default read transaction isolation with the WebSphere scheduler service, all scheduler read operations will be blocked, and you might receive lock timeout expired SQLException exceptions for long-running tasks that were part of the read operation.
Resolution: To enable the tightly coupled XA transactions in Informix Dynamic Server, follow the resolution for Symptom: Enabling tightly coupled XA transactions in Informix Dynamic Server. With the fix, the values for isolation level and lock mode are complete-connection level setting. Complete-connection level setting allows propagation of values that were set in the local environment to all transactions. If the value is changed within a transaction, the changed value is propagated back to the local environment and to all subsequent (new and resume) transactions.
Software 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
Other problems
- Symptom: Message "Null Pointer Exception on BLOB/SMARTBLOB column" in the SystemOut.log of Application Server
A null pointer exception is returned in the Application Server file SystemOut.log when IfxBblob or IfxCblob is created using a serialized object and sent to the server.
Resolution: Do not use IfxBblob (inputStream), IfxCblob (inputStream), IfxCblob (inputStream) or IfxCblob (locator) to access the server. It runs as MODE_CLIENT_ONLY.
- Symptom: Messaging engine of Application Server cannot start due to a problem with the Informix JDBC Driver 3.00JC1
When the messaging engine uses the Informix JDBC driver 3.00JC1 to store its data, the messaging engine cannot start. You can find the following message in the application server file SystemOut.log:
[…..] 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: Upgrade the Informix JDBC Driver to 3.00JC2
- Symptom: ApplicationNotFindException in the WAS file SystemOut.log when trying to access modules generated by EJB™ components.
When an entity (CMP) gets the string from the table, blank spaces are inserted into the result set that can cause the error ApplicationNotFindException.
For example:
A failed event can be stored in the Informix database and the event’s Destination_module_name is HelloWorldWithBO (16 characters).
To get the details of this failed event, FailedEventManagerEJBBean.getFailedEventWithParameters is called, which executes the following code:
loadClassContext (fel.getDestination_module_name ());
fel.getDestination_module_name () returns:
"HelloWorldWithBO " (255 characters),
instead of "HelloWorldWithBO" (16 characters).
|
So the module cannot be found, and ApplicationNotFindException is thrown.
Resolution: The schema DDL generated by the EJB components uses the data type CHARACTER for the column by default. In the example above, Destination_Module_Name is defined as CHARACTER (255).
Use data type VARCHAR or LVARCHAR instead of CHAR.
- 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 (IDS) 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. The default size is 2,048 bytes.
- Symptom: Unable to connect to localhost when Informix Dynamic Server is installed locally
When you locally install IDS with Application Server, the connection to IDS fails when using localhost. The same issue is observed when configuring a data source in Application Server.
Resolution:
- On Windows operating systems, use
*[hostname] in the host information tab of setnet32 to get both IP address 127.0.0.1 and localhost to work for Informix connections.
- On the Solaris Operating Environment, use the ONCONFIG parameter DATABASESERVERALIASES to resolve the localhost problem.
For example: Set DATABASESERVERALIASES to patriot1_local in the IDS ONCONFIG file and add the following line in
$INFORMIXSQLHOSTS ($INFORMIXDIR/etc/sqlhosts):
patriot1_local ontlitcp 127.0.0.1 18551 |
- 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 Application Server V6.0.
Resolution: The fix is available in later versions of the Informix JDBC driver. Use the 3.00 JC1 JDBC driver.
- Symptom: Database metadata gettables fails for ANSI databases.
Informix Dynamic Server converts the schema owner to uppercase for ANSI databases,
but the JDBC driver converts the schema owner to lower case. So getTables(...) fails for some cases.
Resolution: The fix is available in later versions of the Informix JDBC driver. Use the 3.00. JC1 JDBC driver.
Resources
About the author | | | Anju Sudhakar is a Quality Assurance Engineer who has worked on Informix Dynamic Server for over 7 years. Anju works in the interoperability testing of Informix Dynamic Server against WebSphere Application Server and migration testing of various source databases to IBM databases (including DB2 and IDS). |
Rate this page
| |
