Connecting to an Informix Database from Jupyter Notebook

There is almost nothing in this blog post about Db2. This is unusual for me. But I had a bear of a time figuring this out, so I need to share.

I have a lot of Db2 performance data that is stored in an Informix databse. This means that if I want to apply things I’m learning with Jupyter Notebook and data science to this data, I had to learn the basics of how to connect to an Informix database.

My background is with Db2. Informix, despite being written by the same larger company is pretty dang different. There are additional things one has to know for a connection. I don’t pretend to know why, and despite knowing several top Informix experts, I’m not going to ask. The part I need to know and figure out is how to use an Informix database as a user. For once, I’m not the Engineer or even the Administrator.

The Jupyter Notebook that I use for this blog entry is available on my GitHub repo.

The Journey

I went into this thinking this would be easy. After all, I’m used to connecting to another IBM RDBMS all the time, right? No, that didn’t help much.

You see, the standard IBM_DB driver seems to say it will support Informix as well as Db2, but it turns out that (it looks like) they never got around to implementing that functionality. This leads to the fact that there is no support for Informix in my pet tool, the SQL Magic.

What this means is even once I managed to get connected, everything is done differently, and with a lot more difficulty. But Once you have the basic code working, you can use it over and over again to pull the data into a data frame, and from there nothing’s all that different.

The Connection

Without the IBM_DB driver support, I had to find a library that would help me connect. After trying a couple of others, I found ifxpy. It can be installed and imported using:

!pip3 install ifxpy
import IfxPy

For a Db2 database, I need four pieces of information to connect:

• Server Name or IP Address
• Port Number for the Right Db2 Instance
• User Name with Privileges
• Password for the User

As far as I can tell, for an Informix connection, I need two additional pieces of information on top of the above:

• Informix Server Name (this is different than the hostname)
• Database Locale

Once I properly had these, I was able to connect using this syntax:

# Define filename for passwords
filename = 'dba_variables.py'
# source the file
%run $filename

ConStr = "SERVER="+ifxserver+";DATABASE="+DB+";HOST="+Host+";SERVICE="+Port+";UID="+User+";PWD="+PW+";DB_LOCALE=en_US.utf8;"

try:
    conn = IfxPy.connect( ConStr, "", "")
except Exception as e:
    print ('ERROR: Connect failed')
    print ( e )
    quit()

I suspect I can do better than this, because this returns absolutely nothing on a successful connection. It does at least return the error when a connection is not successful.

The variables I use in that connection string are ones I store in a separate file. That file looks something like this:

Host='host.example.com'
DB='sample'
Port='50001'
User='ecrooks'
PW='redactedPW'
ifxserver='sample_reporting_tcp'

Running a Statement and Getting Output in a DataFrame

I don’t entire get the data mappings here, but in order to get things into a data frame, I had to use something like this:

# Select records
sql = """
select timestamp::DATETIME YEAR TO HOUR AS hour
    , 100 - avg(cpu_id) as cpu_busy
from os_vmstat
where mail_id='cli_dm_db1_prod1'
group by 1
order by 1
"""

stmt = IfxPy.exec_immediate(conn, sql)
assoc = IfxPy.fetch_assoc(stmt)

ls=[]
while assoc != False:
    ls.append(assoc)
    assoc = IfxPy.fetch_assoc(stmt)

This methodology worked reliably for me. I guess I just didn’t realize all the things the SQL magic was actually doing for me.

Hopefully if someone else needs to get data from Informix into Jupyter Notebook or Python in general, this can help!

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home