Informix database issues the "java.sql.SQLException" when operating in non-logging mode
 Technote (troubleshooting)
 
Problem(Abstract)
You are operating an Informix database in non-logging mode with IBM WebSphere Application Server V5.0. When your application attempts to use transactions, the following exception occurs:

java.sql.SQLException: No Transaction Isolation on non-logging db's
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:340)
at com.informix.jdbc.IfxSqliConnect.setTransactionIsolation(IfxSqliConnect.java:1564)
 
Cause
The default transaction logging mode for an Informix database is non-logging. Informix issues an exception when an unsupported transaction or an unsupported transaction isolation level is used in non-logging mode.

For example, WebSphere Connection Manager often calls setTransactionIsolation implicitly, even when your application invokes setTransactionIsolation directly.

 
Resolving the problem
Your Informix database must be explicitly set with a value of buffered or non-buffered using the dbaccess command; for example:

CREATE DATABASE db IN dbspace WITH BUFFERED LOG

Workarounds

WebSphere Application Server V5.0.1:

  1. Run getConnection()
  2. Then run
    Connection.setTransactionIsolation(Connection.TRANSACTION_NONE)

WebSphere Application Server V5.0.2:
  • WebSphere Application Server implicitly calls the method setTransactionIsolation() when running the getConnection() method. As a result, the V5.0.1 workaround does not work.
  • For V5.0.2, your application needs to:
    1. Subclass the InformixDataStoreHelper, and
    2. Override the getIsolationLevel method to always return TRANSACTION_NONE.
      • Instructions on how to subclass InformixDataStoreHelper are documented in the WebSphere Application Server V5.0 Information Center section, Example: Developing your own DataStoreHelper class.
      • Informix specific example:
        1. Create InformixDataStoreHelperNonLoggingMode.java (as below),
        2. compile it and put the InformixDataStoreHelperNonLoggingMode.class in install_root/classes directory, and
        3. Use com.company.package.InformixDataStoreHelperNonLoggingMode as "Datasource Helper Classname", instead of "com.ibm.websphere.rsadapter.InformixDataStoreHelper" for your data source:

          // InformixDataStoreHelperNonLoggingMode.java
          package com.company.package;
          import com.ibm.websphere.rsadapter.InformixDataStoreHelper;
          import java.util.Properties;

          public class InformixDataStoreHelperNonLoggingMode extends InformixDataStoreHelper
          {

          public InformixDataStoreHelperNonLoggingMode(Properties props)
          {
          super(props);
          }

          public int getIsolationLevel(com.ibm.websphere.appprofile.accessintent.AccessIntent aIntent) throws javax.resource.ResourceException
          {
          return 0;
          }

          } // End of InformixDataStoreHelperNonLoggingMode
      • You can also refer to the JavaDocâ„¢ for the class InformixDataStoreHelper.

Important Note
The getIsolationLevel method is declared as final() within the classes InformixDataStoreHelper, DB2DataStoreHelper, MSSQLDataStoreHelper, and CloudscapeDataStoreHelper. This prevents your application from subclassing as recommended.

The problem is reported in APAR PQ79683, which is fixed in WebSphere Application Server 5.0.2 Cumulative Fix 4 (recommended). If you cannot upgrade to Cumulative Fix 4, contact IBM WebSphere Technical Support to obtain the WAS_Adapter_11-21-2003_5.0.2_cumulative_Fix (or later).

The getIsolationLevel method is not declared as final within the classes GenericDataStoreHelper, OracleDataStoreHelper, Sybase, and Sybase11.
 
 
Cross Reference information
Segment Product Component Platform Version Edition
Application Servers WebSphere Application Server Enterprise General AIX, HP-UX, Linux, Solaris, Windows 5.0.1, 5.0.2, 5.0.2.2, 5.0.3 Edition Independent
Application Servers Runtimes for Java Technology Java SDK
 
 


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server > Java 2 Connectivity (J2C)
Operating system(s): Windows
Software version: 5.0.2.1
Software edition:
Reference #: 1166345
IBM Group: Software Group
Modified date: Sep 28, 2004