Atomikos Forum

Where can I find documentation to configure XA connections to DB2/400 database to run stored procedures?

I am facing a strange issue since last week where in I am able to run the DB2/AS400 stored procedures fine the first time and it fails with the following error the second time after a sleep of few seconds.

Cause: java.sql.SQLException: [CEE9901] Application error.  *N unmonitored by *N at statement *N, instruction X'4000'.

-----------------------------------------------
Application Set-up
-----------------------------------------------
In my standalone Java application, I am using Spring 3.0.5 + Atomikos 3.7.M5 + Mybatis + JTA 1.1 + Oracle 10g XA Driver(ojdbc14.jar) + DB2 UDB Driver for iSeries(jt400.jar).

In Spring Configuration, I have defined 2 datasources, one for Oracle XA and one for DB2/400 XA. Both the datasources are fed into Mybatis Session Factories.

In my java classes, I am using the annotation @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW) to combine all Oracle method calls and DB2/400 method calls in one transaction context.

All of this works fine the first time, but when the application sleeps for few seconds and attempts to do the same, it fails.

Following is the Spring Configuration to set up XA datasources and Atomikos Transaction Manager:

----------------------------------------------------
ORACLE XA CONFIGURATION
----------------------------------------------------
    <!-- Oracle XA Data Source -->
<bean id="oracleXADataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean">
        <property name="uniqueResourceName">  <value>ORACLEXADBMS</value></property>
        <property name="xaDataSourceClassName" value="oracle.jdbc.xa.client.OracleXADataSource" />
        <property name="xaProperties">       
            <props>       
                <prop key="URL">${Oracle.JDBCConnectString}</prop>
                <prop key="user">${Oracle.User}</prop>
                <prop key="password">${Oracle.Password}</prop>
            </props>
        </property>
        <property name="poolSize"><value>10</value></property>   
    </bean>

----------------------------------------------------
AS400 XA CONFIGURATION
----------------------------------------------------
    <!-- AS400 XA Data Source -->
    <bean id="as400XADataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean">
        <property name="uniqueResourceName"><value>AS400XADBMS</value></property>
        <property name="xaDataSourceClassName" value="com.ibm.as400.access.AS400JDBCXADataSource" />
        <property name="xaProperties">       
            <props>       
                <prop key="serverName">${AS400.Host}</prop>
                <prop key="user">${AS400.User}</prop>
                <prop key="password">${AS400.Password}</prop>
                <prop key="naming">${AS400.Connection.Naming}</prop>
                <prop key="dataTruncation">${AS400.Connection.DataTruncation}</prop>
            </props>
        </property>
        <property name="poolSize"><value>10</value></property>   
    </bean>

----------------------------------------------------
ATOMIKOS JTA CONFIGURATION
----------------------------------------------------
    <tx:annotation-driven/>
    
    <bean id="atomikosTransactionManager" class="com.atomikos.icatch.jta.UserTransactionManager" init-method="init" destroy-method="close">
        <property name="forceShutdown"><value>true</value></property>
    </bean>

    <bean id="atomikosUserTransaction" class="com.atomikos.icatch.jta.UserTransactionImp">
        <property name="transactionTimeout"><value>300</value></property>
    </bean>

    <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
        <property name="transactionManager"><ref bean="atomikosTransactionManager"  /></property>
        <property name="userTransaction"><ref bean="atomikosUserTransaction"  /></property>
    </bean>

Any leads on this will be very helpful.
Thanks in advance.
Karthik

Are you reusing the same statement across transaction boundaries?

Thank you for your quick response.

In my Mybatis config setup, I have set defaultExecutorType=SIMPLE, which does not reuse prepared statements. Other options for this parameter are: REUSE/BATCH. I have also set cacheEnabled=false. Running Stored procedures in Oracle works fine with the same settings, but not DB2/400.

Also, I face this problem only under 'REQUIRED' or 'REQUIRES_NEW' transaction propagation context. It runs fine the second time with other non-transactional settings. And If I replace the stored procedure with a simple DB2/400 SELECT statement, it works fine.

Hence, I was not sure if I am using the proper DB2 driver that Atomikos can work with, that is XA-aware. I followed the instructions on your site for Oracle XA and it works just fine as I can run stored procs/queries multiple times with no issues.

--------------------------------
DB2/400 MyBatis Config
--------------------------------
<configuration>
    <settings>
                <setting name="cacheEnabled" value="false" />
        <setting name="lazyLoadingEnabled" value="false" />
        <setting name="defaultExecutorType" value="SIMPLE" />
    </settings>
        .
        .
</configuration>

------------------------------
DB2/400 Stored Procedure Call
------------------------------
    <select id="transmitOrders" parameterType="orderTransmissionDTO" resultMap="messageListDTO" statementType="CALLABLE" useCache="false" flushCache="true">
        { call TX_ORDERS    (#{returnCode, jdbcType=VARCHAR, javaType=java.lang.String, mode=OUT}, #{userId, jdbcType=VARCHAR, javaType=java.lang.String, mode=IN}, #{requestCount, jdbcType=BIGINT, javaType=java.lang.Integer, mode=IN}, #{requestString, jdbcType=VARCHAR, javaType=java.lang.String, mode=IN}) }
      </select>

Thank you.

This issue has been resolved. It had nothing to do with Atomikos. It was due to an internal DB2 OS issue.

OK thanks for the update!