Connecting to IBM Informix database from Linux

I've needed to connect to an IBM Informix database recently. Informix is not so popular database software and getting a working connection to it did not work right away by simply following the documentation(especially via ODBC). So lets write this down for future reference.

I've got working connections to the database via 3 different ways:

• Using JDBC
• Using "Informix Connect" (an Informix client library similar to Oracle OCI)
• Using ODBC

JDBC

JDBC connection worked flawlessly. It was just a matter of placing the Informix JDBC driver in the classpath and specifying the right connection string and login credentials. The connection string looks like:

jdbc:informix-sqli://10.10.10.10:1526/testdb:INFORMIXSERVER=ol_hostname

Informix Connect

I've got this working from a perl script. This needed perl DBI and libdbd-informix-perl installed. I guess the libdbd-informix-perl is not compiled in a flexible way since the dynamic library contained hard referenced paths:

root@work:/# ldd /usr/lib/perl5/auto/DBD/Informix/Informix.so
    linux-gate.so.1 =>  (0xb776f000)
    /opt/informix/lib/libifsql.so => not found
    /opt/informix/lib/libifasf.so (0xb7713000)
    /opt/informix/lib/libifgen.so => not found
    /opt/informix/lib/libifos.so => not found
    /opt/informix/lib/libifgls.so => not found
    libpthread.so.0 => /lib/i686/cmov/libpthread.so.0 (0xb76e4000)
    libc.so.6 => /lib/i686/cmov/libc.so.6 (0xb759e000)
    libm.so.6 => /lib/i686/cmov/libm.so.6 (0xb7577000)
    libdl.so.2 => /lib/i686/cmov/libdl.so.2 (0xb7573000)
    libcrypt.so.1 => /lib/i686/cmov/libcrypt.so.1 (0xb7541000)
    /lib/ld-linux.so.2 (0xb7770000)
    /opt/informix/lib/libifglx.so => not found

So if you install the Informix connect client in some other location(as I did) you might need to create the necessary symlinks. It might also be an option to get "DBD Informix" from CPAN and build it yourself.

The test perl script looked like:

#!/usr/bin/perl
use warnings;
use strict;

use DBI;

$ENV{'INFORMIXDIR'} = '/opt/IBM/informix';
$ENV{'INFORMIXSERVER'} = 'ol_hostname';


my $dbh = DBI->connect('dbi:Informix:DS_NAME', 'user', 'password');
my @list = $dbh->func('_tables');
print join("\n", @list);
print "\n";

You also need to describe the "DS_NAME" in /opt/IBM/informix/etc/sqlhosts file. It should look like:

ol_hostname        olsoctcp 10.10.10.10     1526

ODBC

ODBC was hardest to get right since it produced weird error messages and there were no useful recourses on the net which described the errors. In order to get it working you will need the unixodbc package. I've first tried using the graphical configuration tool ODBCConfig provided in unixodbc-bin but this failed miserably. I guess this was due to a missing library in "Informix Connect" with ODBC parameters description.

In order to get ODBC working you have to configure /opt/IBM/informix/etc/sqlhosts as shown above and then edit the files /opt/IBM/informix/etc/odbcinst.ini and /opt/IBM/informix/etc/odbc.ini . I've copied the ini file over the config files of the unixodbc package - /etc/odbcinst.ini and /etc/odbc.init and made the necessary adjustments.

odbcinst.ini:

[ODBC Drivers]
Informix=Installed

[Informix]
Driver=/opt/IBM/informix/lib/cli/iclit09b.so
Setup=/opt/IBM/informix/lib/cli/iclit09b.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=03.51
FileUsage=0
SQLLevel=1
smProcessPerConnect=Y

odbc.ini:

[ODBC Data Sources]
DSN_NAME=Informix

[DSN_NAME]
Description=Informix
Driver=/opt/informix/lib/cli/iclit09b.so
Database=DBNAME
LogonID=USERNAME
pwd=PASSWORD
Servername=ol_HOSTNAME
CursorBehavior=0
CLIENT_LOCALE=en_US.8859-1
DB_LOCALE=en_US.819
TRANSLATIONDLL=/opt/IBM/informix/lib/esql/igo4a304.so
;
; UNICODE connection Section
;

[ODBC]
;uncomment the below line for UNICODE connection
UNICODE=UCS-4
;
; Trace file Section
;
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/opt/IBM/informix
TRACEDLL=idmrs09a.so

At first the config files were not actually picked up. This was not obvious at all from the error messages, so I had to do a good amount of strace-ing until I was able to figure it out. So the solution was to have:

export ODBCINI=/etc/odbc.ini

May be you will not have this problem if you place the settings in ~/.odbc.ini as this was searched too.

The next problem I had was that I was trying to get the CLIENT_LOCALE to be as my terminal to en_US.UTF-8. The error message was still ridiculous:

kernel@work:~$ isql DS_NAME
[ISQL]ERROR: Could not SQLConnect

I had to use strace once again, and saw that the referred resource files were not found. The solution was to change the client locale to something supported:

CLIENT_LOCALE=en_US.8859-1

Then I was able to use isql to connect to the Informix DB host and issue queries.