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.
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.
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!
This scares me a little bit – trying to get some background knowledge before I begin the journey… I simply need to pull a saved report and turn into a pandas df.