Technical Articles

Getting Informix ODBC Working with unixODBC and pyodbc on Linux

Connecting to Informix® through unixODBC looks like it should be simple: define a DSN, point at the driver, connect. In practice, the Informix CSDK driver has its own ideas about configuration, and there are three separate issues to fix before a Python script will connect. Each one produces an error message that points you in the wrong direction.

This article walks through all three, in the order you're likely to hit them.

The setup

Linux host with unixODBC
IBM Informix Client SDK (CSDK) installed in /home/informix/csdk
Driver: /home/informix/csdk/lib/cli/iclis09b.so
DSNs defined in /etc/odbc.ini, driver registered in /etc/odbcinst.ini
Python virtualenv with pyodbc

/etc/odbcinst.ini:

[Informix]
Description = IBM Informix ODBC Driver
Driver      = /home/informix/csdk/lib/cli/iclis09b.so
Driver64    = /home/informix/csdk/lib/cli/iclis09b.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=03.51
FileUsage=0
SQLLevel=0
smProcessPerConnect=Y

/etc/odbc.ini:

[zeus_net]
Description=cdi on zeus_net
Driver=Informix
ServerName=zeus_net
Database=cdi
CLIENT_LOCALE=en_US.819
DB_LOCALE=en_US.819

A simple test harness, dbtest.py, referenced throughout the rest of this article:

#!/usr/bin/env python3
import pyodbc
conn = pyodbc.connect("DSN=zeus_net;UID=informix;PWD=<password>;")
print(conn)
cur = conn.cursor()
cur.execute("select first 5 tabname from systables")
print(cur.fetchall())

Never commit a real password into a script or a DSN like this. Pass it via an environment variable or a credentials file with restricted permissions, not a literal in source.

Problem 1: "Data source name not found" — even though it's there

$ isql -v zeus_net
[IM002][unixODBC][Informix][Informix ODBC Driver]Data source name not found and no default driver specified.

The obvious first reaction is to check odbc.ini for typos. But look closely at the message prefix: [unixODBC][Informix][Informix ODBC Driver]. The error is coming from the Informix driver, not the unixODBC driver manager.

An ODBC trace (Trace = Yes in the [ODBC] section of odbcinst.ini) confirms it: unixODBC found the DSN, resolved Driver=Informix, loaded iclis09b.so — and then the driver itself raised IM002 (native error -11041).

Why

The Informix CLI driver does not trust the driver manager for DSN attributes. It re-reads the DSN from its own copy of odbc.ini, which it locates via the ODBCINI environment variable. If that isn't set, it falls back to $HOME/.odbc.ini. A system-wide /etc/odbc.ini is invisible to it.

Fix

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc                 # directory holding odbcinst.ini
export INFORMIXDIR=/home/informix/csdk
export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/cli:$LD_LIBRARY_PATH

(Alternatively, per user: ln -s /etc/odbc.ini ~/.odbc.ini.)

Problem 2: "Sqlhosts file not found or cannot be opened"

With the DSN now visible, the error changes:

[S1000][unixODBC][Informix][Informix ODBC Driver][Informix]Sqlhosts file not found or cannot be opened.

Progress. The driver now needs to resolve ServerName=zeus_net to a host and port, and it does that through the Informix sqlhosts file — which a standalone CSDK install doesn't provide (you typically get only sqlhosts.std).

Fix

Find the server's connection details:

onstat -c | grep -E '^(DBSERVERNAME|DBSERVERALIASES)'
ss -ltnp | grep oninit

Create a sqlhosts file for the client:

cat > $INFORMIXDIR/etc/sqlhosts <<'EOF'
# dbservername  nettype    hostname  service/port
zeus_net        onsoctcp   zeus      9088
EOF
chmod 644 $INFORMIXDIR/etc/sqlhosts
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts

The first column must exactly match the server's DBSERVERNAME or one of its DBSERVERALIASES. Use the real port the listener is on.

At this point isql -v zeus_net connects.

Problem 3: pyodbc crashes with "stack smashing detected"

This assumes pyodbc already builds and imports against your Python interpreter. If pip install pyodbc fails to build at all — a common case against a very new Python release, where an older pinned pyodbc version has no compatible wheel — that's a separate problem from the crash below; see Using a Different Python Version on Linux for running the application under a second, compatible Python via a virtual environment without touching the system default.

isql works:

zeus:[csdk] isql zeus_net
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

So Python should too. It doesn't:

$ ./dbtest.py
*** stack smashing detected ***: terminated
Aborted (core dumped)

No Python traceback — this is a crash in native code.

Why

It's a Unicode width mismatch:

pyodbc uses the wide-character ODBC API (SQLDriverConnectW, SQLDescribeColW, …).
unixODBC uses UCS-2 (2 bytes per character). The ODBC trace even says so: UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'.
The Informix driver on Linux defaults to UCS-4 (4 bytes per character).

The driver writes twice as many bytes as the caller's buffer can hold, tramples the stack canary, and glibc aborts the process. isql never hits this because it uses the narrow (ANSI) API.

Fix

Tell the Informix driver to use UCS-2 by adding an [ODBC] section to the odbc.ini that $ODBCINI points to:

[ODBC]
UNICODE=UCS-2

Result:

$ ./dbtest.py
<pyodbc.Connection object at 0x7fb838d94ab0>

Final working configuration

/etc/odbc.ini:

[ODBC]
UNICODE=UCS-2

[zeus_net]
Description=cdi on zeus_net
Driver=Informix
ServerName=zeus_net
Database=cdi
CLIENT_LOCALE=en_US.819
DB_LOCALE=en_US.819

$INFORMIXDIR/etc/sqlhosts:

zeus_net   onsoctcp   zeus   9088

Environment (put in the user's profile, or the virtualenv's bin/activate):

export INFORMIXDIR=/home/informix/csdk
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/cli:$LD_LIBRARY_PATH

Troubleshooting cheat-sheet

Symptom	Real cause	Fix
IM002 "Data source name not found" from `[Informix ODBC Driver]`	Driver can't see `/etc/odbc.ini`	`export ODBCINI=/etc/odbc.ini`
"Sqlhosts file not found or cannot be opened"	No sqlhosts / `INFORMIXSQLHOSTS` unset or unreadable	Create sqlhosts, export `INFORMIXSQLHOSTS`
-908 / -930	Wrong host/port, firewall	Check listener: `nc -zv host port`
-25596	Server name not in `DBSERVERALIASES`	Match sqlhosts name to server config
-951	Login rejected	Check user/password/trust
`stack smashing detected` in pyodbc	UCS-4 vs UCS-2 mismatch	`[ODBC] UNICODE=UCS-2` in odbc.ini

Housekeeping

Turn tracing off once things work (Trace = No), or /tmp/odbc.trc grows on every connection.
Keep credentials out of /etc/odbc.ini. It must be world-readable for other users to use the DSNs; pass the user and password at connect time or use trusted connections instead.
Debugging native crashes: python -X faulthandler script.py or gdb -batch -ex run -ex bt --args python script.py will show which driver function blew up.

Key Takeaways

Read the error prefix. [Informix ODBC Driver] means the driver raised it, not unixODBC — even when the message sounds like a driver manager problem.
The Informix driver reads its own configuration via ODBCINI; it doesn't inherit what unixODBC resolved.
A working isql doesn't prove a Unicode client will work. isql uses the ANSI API; pyodbc uses the wide API.