Connecting to an Informix Database from Jupyter Notebook

Posted by

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!

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.