Error connecting from Python to Db2 through pyodbc

Question

I installed the Db2 ODBC driver in Windows 10 following these instructions. I extracted the driver folder in C:\tools\db2cli\ and run db2cli install -setup from the bin folder.

This seems to have worked fine, as the ODBC driver was registered in Windows:

But when I try to connect from Jupyter I get this error:

server = 'localhost' 
database = 'mydb' 
username = 'db2inst1' 
password = 'abc123' 
driver = 'IBM DB2 ODBC DRIVER - C_tools_db2cli'
conn = pyodbc.connect('DRIVER={' + driver +'};SERVER='
         + server + ';DATABASE=' + database + ';UID=' 
         + username + ';PWD=' + password + ';PROTOCOL=TCPIP;PORT=50000' )

Error Traceback (most recent call last) in 4 password = 'abc123' 5 driver = 'IBM DB2 ODBC DRIVER - C_tools_db2cli' ----> 6 conn = pyodbc.connect('DRIVER={' + driver +'};SERVER=' 7 + server + ';DATABASE=' + database + ';UID=' 8 + username + ';PWD=' + password + ';PROTOCOL=TCPIP;PORT=50000' )

Error: ('HY000', '[HY000] [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004\r\n (-1042) (SQLDriverConnect); [HY000] [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004\r\n (-1042)')

I unsuccessfully tried to find the error codes to find a solution. What should be looked at to fix this problem?

Note: I added C:\tools\db2cli\bin to the PATH but still get the same error.

What's the reason for using pyodbc, instead of the ibm_db interface ? (pip install ibm_db) and using ibm_db.connect() ? — mao
@mao, I have the same problem with ibm_db trying to connect with conn = ibm_db.connect("DATABASE=mydb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=abc123", "", "") I get SQLCODE=-1042on: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 — ps0604
Use HOSTNAME=localhost; not SERVER= in the connection string. It means the installation (of the CLI driver) is somehow misconfigured. Did you install the CLI driver as an Administrator? Leaving python aside temporarily, does the CLI driver operate by itself? Create and Configure the db2dsdriver.cfg file as per documentation, and test with the db2cli -connect -dsn XXX -user YYY -passwd ZZZ . This may give more details. — mao
If the db2cli connect -dsn ... succeeds, and python fails using that same db2cli, then edit your question to specify the modified connection string (mask the credentials), the text output of python -V and the text output of pip show ibm_db. You can see what is happening under the covers by using the tracing facilities of db2trc (refer to documentation) to capture trace, dump it, and format convert the binary output into readable text. — mao

mao mao · Accepted Answer · 2021-01-13T16:55:33

This issue was caused by a defect in clidriver for some specific configurations on Microsoft Windows.

You can workaround this by ensuring that the PATH does not have any other Db2-products on it (except for clidriver\bin), and then ensuring to add two additional directories to the PATH before launching python. These are the fully qualified pathnames to these directories:

clidriver\bin\icc64
clidriver\bin\amd64.vc12.crt

IBM should at some point deliver a fix for IT34327.

Error connecting from Python to Db2 through pyodbc

2 Answers