Cannot connect to Db2 from python

Question

I installed ibm_db in python with pip install ibm_db and created a db2dsdriver.cfg file with the following content:

<configuration>
  <dsncollection>
    <dsn alias="BANK0002" name="BANK0002" host="localhost" port="50000"/>
  </dsncollection>
  <databases>
    <database name="BANK0002" host="BANK0002" port="50000"/>
  </databases>
</configuration>

Running this command line I can connect with the database and allows me to run a select statement with no issues:

db2cli execsql -user DB2INST1 -passwd xxxxx -dsn bank0002

The problem is that I cannot connect from Python:

conn = ibm_db.connect("UID=DB2INST1;PWD=xxxxxx;DATABASE=bank0002;
       HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;", "", "")

throws:

SQLCODE=-30082n: [IBM][CLI Driver] SQL30082N Security processing failed with reason "17" ("UNSUPPORTED FUNCTION"). SQLSTATE=08001

What is this problem and how to fix it?

python -V
Python 3.8.5


pip show ibm_db
Name: ibm-db
Version: 3.0.2
Summary: Python DBI driver for DB2 (LUW, zOS, i5) and IDS
Home-page: http://pypi.python.org/pypi/ibm_db/
Author: IBM Application Development Team
Author-email: [email protected]
License: Apache License 2.0
Location: c:\tools\miniconda3\envs\env38\lib\site-packages

The connection string should use UID=... instead of USER=..., and PWD=... instead of PASSWD=.... . Also what is the conda version? (There were reports in the past of issues involving ibm_db with conda, but most are resolved). Please specify if you tried with Admin mode (for install , as well as run, and if there is any different symptom). — mao
I fixed UID and PWD and now I get the same error I got before SQLCODE=-1042on: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 — ps0604
conda version is 4.9.2, running jupyter as admin throws the same error — ps0604
@mao see here, could it be that the python version 3.8.5 is not supported? It says <= 3.8 — ps0604
Last year I had to build my own ibm_db with Conda 4.8.5 to get it to work with Conda on Win10. I've not upgraded Conda since then. I suspect you may need to do the same. Your issue is Conda related, I believe. Easy to prove if you try on a workstation that does not have conda, but has python 3.8. — mao

mao mao · Accepted Answer · 2021-01-13T13:52:28

You are using the python build as delivered by miniconda / anaconda, and this may be the determining factor.

A workaround is to use conda to build ibm_db locally and to install the newly built package from local. Other workarounds may be possible.

The instructions for building conda packages and installing them are here and related pages.

For Win10 x64 20H2 with anaconda 2020.11 (4.9.2) the following commands let me use python ibm_db module to connect to Db2, at least from the anaconda prompt.

Install Anaconda 2020.11 for Microsoft Windows x64.
In a administrator-mode Anaconda3 prompt window run these commands (ensuring each command completes successfully before running the next one):
conda install conda-build git anaconda-clean m2-patch
conda update conda conda-build git m2-patch
cd /d %userprofile%
if directory ibm_db exists the rename it or remove it.
conda skeleton pypi ibm_db
conda-build ibm_db
conda install --use-local ibm_db

If you now run conda list, it's output should contain ibm_db module, and the python (3.8.5) will connect to Db2 , at least when used in the anaconda prompt window.

If you continue to get the same SQL1042C symptom, verify that the PATH has no other Db2-products (other than clidriver\bin) on it, and then add two additional directories to the PATH before launching python. These are the fully qualified pathnames to clidriver\bin\icc64 and clidriver\bin\amd64.vc12.crt (or whatever the amd64.vc.XX.crt is present under clidriver\bin). This is not normally needed, there is is a defect in clidriver for some specific configurations that makes it necessary, and IBM should deliver a fix at some future time (IT34327).

Cannot connect to Db2 from python

1 Answers