Category 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

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!