How do I fix connection to db2 using SQLAlchemy in python?

Question

I'm having trouble connecting to my database BLUDB in IBM Db2 on Cloud using SQLAlchemy. Here is the code I've always used and it's always worked fine:

%sql ibm_db_sa://user:[email protected]:50000/BLUDB

But now I get this error:

(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Exception('[IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004\r SQLCODE=-1042') (Background on this error at: http://sqlalche.me/e/13/f405) Connection info needed in SQLAlchemy format, example: postgresql://username:password@hostname/dbname or an existing connection: dict_keys([])

These packages are loaded as always:

import ibm_db
import ibm_db_sa
import sqlalchemy
from sqlalchemy.engine import create_engine

I looked at the python db2 documentation on ibm and the sqlalchemy error message but couldn't get anywhere.

I am working in Jupyterlab locally. I've recently reinstalled Python and Jupyterlab. That's the only thing locally that's changed.

I am able to successfully run the notebooks in the cloud at kaggle and cognitive class. I am also able to connect and query sqlite3 via python without an issue using my local notebook.

All the ibm modules and version numbers are the same before and after installation. I used requirements.txt for reinstallation.

In db2diag.log here are the last two entries:

2020-11-05-14.06.47.081000-300 I13371F372 LEVEL: Warning PID : 17500 TID : 7808 PROC : python.exe INSTANCE: NODE : 000 HOSTNAME: DESKTOP-6FFFO2E EDUID : 7808 FUNCTION: DB2 UDB, bsu security, sqlexLogPluginMessage, probe:20 DATA #1 : String with size, 43 bytes loadAuthidMapper: GetModuleHandle rc = 126

2020-11-05-14.13.49.282000-300 I13745F373 LEVEL: Warning PID : 3060 TID : 12756 PROC : python.exe INSTANCE: NODE : 000 HOSTNAME: DESKTOP-6FFFO2E EDUID : 12756 FUNCTION: DB2 UDB, bsu security, sqlexLogPluginMessage, probe:20 DATA #1 : String with size, 43 bytes loadAuthidMapper: GetModuleHandle rc = 126

You write that it previously worked, you reinstalled Python and Jupyterlab, then it failed. Why did you reinstall Python? Did the python version change? After reinstalling python did you also reinstall all required packages ? — mao
If you re-installed, then most likely the package-versions changed (unless you have pip list output from both before and after. The SQL1042C can happen due to a broken configuration on the client side. There may be more details in the db2diag.log on the client workstation, study that and update the question with details. IF there are no details there, you can use db2trc to get a CLI trace and it (after formatting ) will show what is going on under the covers. Refer to the Db2 Knowledge centre online for details. — mao
The component giving SQL1042C is the Db2-driver, most likely the CLIDRIVER, which ibm_db installs by default always at "todays" version on day of pip install ibm_db. Suggest you configure db2dsdriver.cfg file per Db2-Knowledge-Centre instructions, then use db2cli validate -connect -dsn $YOURDSN -user $YOURUSER -passwd $YOURPASSWD. This eliminates python and jupyterlab temporarily and just gets the Db2-connection working at CLIDRIVER level. If that gives SQL1042C, then you can use db2trc to see what is happening under the covers. Follow Db2-Knowledge-Centre instructions. — mao

chughts chughts · Accepted Answer · 2020-11-06T11:35:19

I think the root of this will be down to the new version of Python and pip caching.

What version did you move from and what version are you now on. Is this a Python 2 to Python 3 change? When changing versions, normally you would need to clean pip install all components, but pip does use a cache. Even for components that may need to be compiled, and there is a good chance that Db2 components are being compiled.

So what you will need to do is to re-install the dependancies with

pip install --no-cache-dir

How do I fix connection to db2 using SQLAlchemy in python?

1 Answers