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

score 5 · Answer 1 · answered Aug 06 '13 at 20:36

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.

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=password;PROTOCOL=TCPIP;PORT=port" . — Alan Evangelista, Mar 14 '14 at 16:59

score 0 · Answer 2 · answered Oct 01 '15 at 16:55

0

This connection string for pyodbc, work for me:

conexion_str = 'SYSTEM=%s;db2:DSN=%s;UID=%s;PWD=%s;DRIVER=%s;' % (self._SYSTEM, self._DSN, self._UID, self._PWD, self._DRIVER)
self._cnn = pyodbc.connect(conexion_str)

answered Oct 01 '15 at 16:55

Giovani Hgo

29
4

ravinayag · Answer 3 · 2021-05-24T11:17:17.147

I had a similar situation today with Conda environment. This post helps me to move forward with driver configurations. Initially had query why there are two files.. but later i understood by spending some time. hence i posting if anyone reads in the future.

i.e. Pyodbc+db2 on docker container "daskdev/dask" which comes with conda python env and manually install db2, pyodbc drivers.

conda install pyodbc -y
conda install -c conda-forge ibm_db -y

FYI :

file /etc/odbcinst.ini used for Driver configurations.

file /etc/odbc.ini used for database configurations.

By using pyodbc, you can call your database connectivity which internally connects the driver.

pyodbc connection to DB2

3 Answers3

FYI :