pyodbc connection to DB2

Question

I am currently trying to connect DB2 from a python program using pyodbc. As the driver was not already installed in the server I downloaded it from the IBM website, but I'm not sure how to connect using pyodbc .

The code which I am using is :

cnx = pyodbc.connect(
        'Driver={IBM DB2 ODBC Driver}; '
        'Hostname=hostname; '
        'Port=50100; '
        'Protocol=TCPIP; '
        'Database=db_name; '
        'CurrentSchema=schema; '
        'UID=user_id; '
        'PWD = passw;'
        )

Not sure how to connect this with the drivers and CLI I just downloaded and any hint will be very helpful.

This question is related :

DB2 connection through pyodbc and pandas.io.sql in Unix Box with non-root

Solution

If you want to use pyodbc, you will need to:

Install/configure the vendor ODBC driver.

In your case, you would need to install either the DB2 Data Server Driver for ODBC and CLI, the DB2 Data Server Runtime Client or the DB2 Data Server client and define the connection to the remote DB2 database in the DB2 client.

Install/configure an ODBC Driver Manager installed on your system (e.g., UnixODBC)

You must register the IBM DB2 client ODBC driver with the Driver Manager. (i.e. in /etc/odbcinst.ini).

For my system (which has the Data Server Client), the entry looks like:

[DB2]
Description = DB2 Driver
Driver      = /home/db2inst1/sqllib/lib/libdb2.so
FileUsage   = 1
DontDLClose = 1

Set up the DSN for your database.

You can do this either in /etc/odbc.ini (system DSNs) or $HOME/.odbc.ini (user DSNs).

My $HOME/.odbc.ini looks like:

[SAMPLE]
Driver = DB2

---

With all of this in place, you should be able to use pyodbc with a connect statement:

cnx = pyodbc.connect('DSN=SAMPLE; UID=user; PWD=password')

I am not sure if it's possible to use DSN-less connections with pyodbc and DB2.

Comments

[Alan Evangelista]

It is possible to use DSN-less connections with pyodbc and DB2, I have configured it. You just need to use a connection string such as "DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME=hostname;DATABASE=db_name;UID=user;PWD=pass‌​word;PROTOCOL=TCPIP;‌​PORT=port" .