How to connect to ibm_db2 database using python package sqlalchemy with the DSN server instead of host/port

Question

I want to create sqlalchemy engine with the use of DSN

driver='db2+ibm_db'
dsn='BLUDB'
url = f'{driver}://{user}:{password}@{dsn}/{db}'
create_engine(url).connect()

Currently my code is with host and port and it is working fine, but we want to update it with dsn

from sqlalchemy import create_engine
from sqlalchemy.engine import Connection

driver='db2+ibm_db'
url = f'{driver}://{user}:{password}@{host}:{port}/{db}'
if params:
    url = f'{url}?{params}'
create_engine(url).connect()

While trying to connect it with DSN it is throwing below error

Traceback (most recent call last):
  File "C:\...\env\lib\site-packages\ibm_db_dbi.py", line 599, in connect
    conn = ibm_db.connect(dsn, '', '', conn_options)
SQLCODE=-1336M][CLI Driver] SQL1336N  The remote host "BLUDB" was not found.  SQLSTATE=08001

In the ODBC data source we are configuring under the system DSN tab, There we have added name as BLUDB and driver as IBM DB2 ODBC. — Binit Amin
– Binit Amin, Commented Mar 12, 2021 at 14:46
It doesn't look like the Db2 dialect supports what you want to do. — mustaccio
– mustaccio, Commented Mar 12, 2021 at 14:51

mao · Accepted Answer · 2021-03-13 08:57:35Z

This answer relates to versions: python ibm_db v3.0.3 , ibm_db_sa v0.3.6 as at March-2021.

The db2+ibm_db2 prefix for create_engine() runs connect in ibm_db_dbi.py which is hardcoded to expect an hostname and a port in addition to the credentials.

You can get a DSN connection by using pyodbc (as the DBAPI driver) and configure the DSN details externally. The dialect remains db2.

If using the clidriver then configure the DSN via the XML file db2dsdriver.cfg. If instead you use the Db2 fat client you can use the odbcad32 gui to define DSN, but the tiny footprint clidriver which comes by default with ibm_db module is non-GUI so configuration is via either XML file or legacy db2cli.ini.

The following works for me (using default clidriver) to give a DSN connection :

in the relevant virtual environment: pip install pyodbc
configure my DSN (for bludb) manually in my db2dsdriver.cfg
db2cli validate (to verify the XML syntax in db2dsdriver.cfg is correct )
db2cli validate -connect -dsn bludb -user XXX -passwd YYYY
db2cli registerdsn -add -alldsn (to register with MS ODBC driver manager)
in the python code use (after quoting) engine = create_engine('ibm_db_sa+pyodbc://'+uid+':'+pwd+'@bludb')
or you can also use (after quoting) engine = create_engine('db2+pyodbc://'+uid+':'+pwd+'@bludb')

Collectives™ on Stack Overflow

How to connect to ibm_db2 database using python package sqlalchemy with the DSN server instead of host/port

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related