4 Replies Latest reply on Oct 18, 2005 7:14 PM by Ramakrishna Pratapa

    JBOSS Informix XA Data Source

    Ramakrishna Pratapa Newbie

      Hi
      I am using Informix database for my transaction processing and we have created
      four databases 1. JMS, 2. tracking 3. lookups or more or less static tables and
      these are from same instances and stay on the same server. Another database is
      used to lookup and authentication purpose. Acodingly,

      Our application has following flow. MDBean reads the message from Queue and invoke a session bean for busisness
      logic and now the businesss logic is in the form of JAVA CLASSES and we are using JNDI to get the connection
      and update the database information.



      I have created four xml-files like.

      1.

      informix-destdb-xa-ds.xml
      ( happenes updates through this connection)

      2. informix-trackdb-xa-ds.xml( happenes updates through this connection

      3. informix-jms-ds.xml (not xa datasoruce)
      4. informix-fscdb-ds.xml ( for lookup purpose)


      Now my first xa data source XML
      informix-destdb-xa-ds.xml and informix-trackdb-xa-ds.xml are,

      <?xml version="1.0" encoding="UTF-8"?>
      <!-- ===================================================================== -->
      <!-- -->
      <!-- JBoss Server Configuration -->
      <!-- -->
      <!-- ===================================================================== -->
      
      <!-- $Id: informix-xa-ds.xml,v 1.4.2.2 2004/12/01 11:44:40 schrouf Exp $ -->
      
      
      <datasources>
       <xa-datasource>
       <jndi-name>InformixDestDB</jndi-name>
       <xa-datasource-class>com.informix.jdbcx.IfxXADataSource</xa-datasource-class>
       <!-- Not supported by latter drivers
       <xa-datasource-property name="IfxWAITTIME">10</xa-datasource-property>
       -->
       <xa-datasource-property name="Description">Something Descriptive</xa-datasource-property>
       <xa-datasource-property name="IfxIFXHOST">hostname</xa-datasource-property>
       <xa-datasource-property name="PortNumber">1551</xa-datasource-property>
       <xa-datasource-property name="DatabaseName">destdb</xa-datasource-property>
       <xa-datasource-property name="ServerName">ondevdb</xa-datasource-property>
       <user-name>username</user-name>
       <password>password</password>
       <no-tx-seperate-pools/>
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.InformixExceptionSorter</exception-sorter-class-name>
       <prepared-statement-cache-size>50</prepared-statement-cache-size>
       <min-pool-size>20</min-pool-size>
       <max-pool-size>50</max-pool-size>
       <idle-timeout-minutes>15</idle-timeout-minutes>
       <track-statements>false</track-statements>
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
       <!-- sql to call on an existing pooled connection when it is obtained from pool
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>InformixDB</type-mapping>
       </metadata>
       </xa-datasource>
      </datasources>
      


      where as


      informix-jms-ds.xml
      informix-fscdb-ds.xml
      are
      <?xml version="1.0" encoding="UTF-8"?>

      <!-- ===================================================================== -->
      <!-- -->
      <!-- JBoss Server Configuration -->
      <!-- -->
      <!-- ===================================================================== -->
      
      <!-- $Id: informix-ds.xml,v 1.2 2003/09/05 16:39:28 patriot1burke Exp $ -->
      
      
      <!--
      <connection-url>jdbc:informix-sqli://myhost.mydomain.com:1557/mydb:INFORMIXSERVER=myserver</connection-url>
       -->
      
      <datasources>
       <local-tx-datasource>
       <jndi-name>InformixDB</jndi-name>
       <connection-url>jdbc:informix-sqli://hostname:15512/fscdb:informixserver=instanceName</connection-url>
       <driver-class>com.informix.jdbc.IfxDriver</driver-class>
       <user-name></user-name>
       <password></password>
       <prepared-statement-cache-size>50</prepared-statement-cache-size>
       <min-pool-size>20</min-pool-size>
       <max-pool-size>50</max-pool-size>
       <idle-timeout-minutes>15</idle-timeout-minutes>
       <track-statements>false</track-statements>
      
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
      
       <!-- sql to call on an existing pooled connection when it is obtained from pool
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>InformixDB</type-mapping>
       </metadata>
      
       </local-tx-datasource>
      
      </datasources>





      Now I have two problems :

      I have three queues where in MDBeans are reading the queues and using stateless
      session bean to do business processing.

      case 1. If I use non XA connection or data source it is working fine for a single transaction or
      document porcessing. It mean if I post a message to first queue the MDBean takes the message and
      process the message and send to the next queue and so on.

      Problem : if I post 10 messages to Queue and up the Beans the I am getting the following message.

      java.sql.SQLException: Could not position within a table (informix.track_trans).
      2005-10-14 16:11:26,166 INFO [STDOUT] at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3204)

      case 2. Then I thought If I use XA datasoruce I may resolve this issue then I have used two XA connections
      for modifications or updating the database and two non XA for lookups and it is behaving different way

      if I use
      <track-connection-by-tx>true</track-connection-by-tx>
      then it
      is locking and giving time out message for second XA connection and JBOSS HANGS

      and if remove the property then for one message it is rollbacking many times and generating 12 messages in the queue.

      case 3. If I test the same on JBOSS 4.02 with XA then it is behaving as case 1.


      Do any one has any idea about XA with JBOSS.

      Thanks for any help.









        • 1. Re: JBOSS Informix XA Data Source
          Ulf Schroeter Novice

          These area INFORMIX database related locking problems, not JBoss XA problems. Your "could not position within table" error might be related to the database isolation level.

          Maybe a datasource entry of <new-connection-sql>set isolation to dirty read</new-connection-sql> will solve this issue. But be aware, that INFORMIX XA jdbc driver has other issues anyway and the INFORMIX blob support is buggy

          regards
          ulf

          • 2. Re: JBOSS Informix XA Data Source
            Ramakrishna Pratapa Newbie

            Schrouf,
            Thank you for the reply and I tried and as Per IBM web site they told to upgate the JDBC to V3.0 JC1 and I did that one too! along with setting the propery

            <xa-datasource-property name="IfxIFX_XASPEC">Y</xa-datasource-property>

            However, Still i am getting the problem. My informix database is set to buffer log mode. Do you think by setting to log mode or do we need to somthink else.

            Thank you for the reply

            Thanks

            • 3. Re: JBOSS Informix XA Data Source
              Ulf Schroeter Novice

              We found an INFORMIX 9.4 server XA transaction isolation and lock mode bug during our INFORMIX XA tests and forwarded it to IBM (bug 166834).

              This server side XA bug has been confirmed an will be fixed (hopefully) in the upcomming IDS-Version 9.40.UC8 und 10.00.UC4. Maybe this will also solve your problems ?

              Regards
              ulf

              • 4. Re: JBOSS Informix XA Data Source
                Ramakrishna Pratapa Newbie

                Schrouf,
                Thank you for the help. I checked the issue but it says it will time out and the Informix is not honoring the transaction isolation level.

                Any how we resolved the issue by changing the locking level from page to row on the tables that are concurrently updating.

                Once again thank you for the help.

                Thanks
                Ram