Tag Archives: Informix

Enable transactions on Informix DB

We need to have the following variables set:

INFORMIXDIR=/opt/IBM/informix
INFORMIXSQLHOSTS=/opt/IBM/Informix/etc/sqlhosts.ol_informix1170
INFORMIXSERVER=ol_informix1170
  1. Now, we can enable the transactions, in first we need to put the database in “quiescent mode” (???):

    >/opt/IBM/informix/bin/onmode –uy
    

    Note: doing this disable access to the database.

  2. enable transactions:

    >/opt/IBM/informix/bin/ontape –s –L 0 –B d9exp
    
  3. Re-enable the connections:

    >/opt/IBM/informix/bin/onmode -m
    
  4. Helpful information: Jdbc configuration

    jdbc:oracle:thin:@server:port:db
    

    More information here

Unlock Informix table

First, get the sessid using the following sql:

select unique
dbsname db,
tabname table,
case
when type="S" then "shared lock"
when type="IS" then "intent shared lock"
when type="SIX" then "shared intent excl lock"
when type="XS" then "shared key value by RR"
when type="IX" then "intent excl lock"
when type="X" then "exclusive lock"
when type="XR" then "excl key value by RR"
when type="U" then "update lock"
when type="B" then "byte lock"
else
"unknown lock type"
end lock_type,
lpad(owner,5) ses_id,
lpad(waiter,5) wait_id
from sysmaster:syslocks
where tabname != 'sysdatabases';

And check the table concerned by the lock. Using its session id, you can now unlock it with:

>onmode -z <sess_id>

Thanks to this thread

Bitronix configuration

After a first attempt to configure the JBoss transaction manager including  JMS, I finally decided to try the Bitronix transaction manager.

Here is the detail of the configuration.

First of all, you need to add the bitronix jars to your project, using Maven, it’s done very easily (unlike JBossTS, which needs a lot of different dependencies):

<dependency>
    <groupId>org.codehaus.btm</groupId>
    <artifactId>btm</artifactId>
    <version>2.1.3</version>
</dependency>

Now, the configuration, using Spring, as usual (you can retrieve some elements I used with the Narayana configuration:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
                        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

    <!-- enable transaction demarcation with annotations -->
    <tx:annotation-driven/>
    <tx:jta-transaction-manager/> 

    <!--  Bitronix Transaction Manager embedded configuration -->
    <bean id="btmConfig" factory-method="getConfiguration" class="bitronix.tm.TransactionManagerServices">
        <property name="serverId" value="spring-btm" />
        <property name="DefaultTransactionTimeout" value="300" />
    </bean>

    <!-- create BTM transaction manager -->
    <bean id="bitronixTransactionManager" factory-method="getTransactionManager"
        class="bitronix.tm.TransactionManagerServices" depends-on="btmConfig" destroy-method="shutdown" />

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

<!-- Datasource config -->
    <bean id="dataSource" class="bitronix.tm.resource.jdbc.PoolingDataSource" init-method="init" destroy-method="close">
        <property name="className" value="com.informix.jdbcx.IfxXADataSource" />
        <property name="uniqueName" value="expElecCliCoredb" />
        <property name="maxPoolSize" value="5" />
        <property name="driverProperties">
            <props>
                <prop key="user">${user}</prop>
                <prop key="password">${pwd}</prop>
                <prop key="serverName">${serverName}</prop>
                <prop key="databaseName">${database}</prop>
                <prop key="portNumber">${port}</prop>
                <prop key="ifxIFXHOST">${host}</prop>
                <prop key="ifxIFX_LOCK_MODE_WAIT">5</prop>
                <prop key="ifxIFX_XASPEC">Y</prop>
                <prop key="ifxDB_LOCALE">en_us.819</prop>
            </props>
        </property>
    </bean>

    <!-- XA JMS Connection Factory, ensure that the jms server has been yet created (with depends-on or order definitions) 
    otherwise the factory is not available and you will receive an exception 
    (javax.jms.JMSException: error looking up wrapped XAConnectionFactory at /XAConnectionFactory)-->
    <bean id="xaJmsConnectionFactory" class="bitronix.tm.resource.jms.PoolingConnectionFactory"
        init-method="init" destroy-method="close">
        <property name="className" value="bitronix.tm.resource.jms.JndiXAConnectionFactory" />
                <property name="uniqueName" value="jmsConnectionFactory" />
        <property name="minPoolSize" value="10" />
        <property name="maxPoolSize" value="10" />
        <property name="driverProperties">
            <props>
                <prop key="name">/XAConnectionFactory</prop>
            </props>
        </property>
    </bean>

    <!--     The producer sends the messages to be processed on the queue -->
    <bean id="notificationsProducer" class="net.classnotfound.cli.core.service.component.NotificationsProducer">
        <property name="connectionFactory" ref="xaJmsConnectionFactory"/>
        <property name="notificationsQueue" ref="notificationsQueue"/>
    </bean>

    <bean id="notificationService" class="net.classnotfound.core.service.NotificationServiceImpl">
        <property name="authorMapper" ref="authorMapper"/>
        <property name="notificationsProducer" ref="notificationsProducer"/>
    </bean>
</beans>

Concerning this configuration, we can see that the way we configure the datasources is almost the same for database as for JMS. The tricky part comes from HornetQ which creates the JMS connection factory but we cannot access it as a standard Spring bean. It’s why we have to get it from JNDI, resulting in a different configuration using dedicated Bitronix component: bitronix.tm.resource.jms.JndiXAConnectionFactory.

This connection factory is now used by the JMS producer. When we send a JMS message, this call is proxied by Bironix to enlist this action in the transaction.

To start the transaction, we do it as usual using the well-known @Transactional annotation. Doing this, the transaction is automatically started, and when we send the JMS message, it is included in the transaction.

Here an example of its usage:

    public void sendNotification(final String message) throws Exception {
        notificationsQueueConnection = connectionFactory.createConnection();
        notificationsQueueSession = notificationsQueueConnection.createSession(true, 0);
        notificationsQueueProducer = notificationsQueueSession.createProducer(notificationsQueue);

        TextMessage textMessage = notificationsQueueSession.createTextMessage(message);
        notificationsQueueProducer.send(textMessage);
    }

It’s important to note that the session is created in the send method, because following the JMS specification, “Any call to the Connection.createSession method must take place within the transaction”. I tried to create the session in a @PostConstruct, there is no error, you can send your message but… Nothing happens 🙁

Done!

Improve server startup using Informix and MyBatis

Working on a project with an Informix database and using MyBatis as the persistence layer, I noted that the start-up of the server takes a long time (about 2 minutes). It is not really a problem if the performance of the application are good, as you don’t start your server all the day, but when you are in the implementation phase…
After step-by-step execution, I discovered that MyBatis gathers the metadata of the database to adapt its behavior, but this part was responsible of the delay (maybe Informix specific?).
I decided to replace this step, fortunately, using MyBatis with Spring, it’s not really a big deal 🙂
As the main purpose of this step is to identify the database provider, I defined a class which returns always the same information: Informix database.

  1. first, you need to define a new DatabaseIdProvider:
    public class IfxDatabaseIdProvider 
            implements org.apache.ibatis.mapping.DatabaseIdProvider {
      
      private static final Log log = LogFactory.getLog(IfxDatabaseIdProvider.class);
    
      private Properties properties;
      
      public String getDatabaseId(DataSource dataSource) {
        
        return "Informix Dynamic Server";
      }
    
      public void setProperties(Properties p) {
        this.properties = p;
      }
    
      public Properties getProperties() {
        return properties;
      }
    }
    
  2. now, you need to tell to MyBatis to use the new class, your Spring config file looks like:

        <bean id="ifxDatabaseIdProvider" 
            class="myPackage.IfxDatabaseIdProvider"/>
        
        <bean id="sqlSessionFactory" 
            class="org.mybatis.spring.SqlSessionFactoryBean">
          <property name="dataSource" ref="dataSource" />
          <property name="mapperLocations" value="classpath:mapper/*Mapper.xml"/>
          <property name="databaseIdProvider" ref="ifxDatabaseIdProvider"/>
        </bean>
    

Done!