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
– mao, Commented Jan 12, 2021 at 17:19
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
– ps0604, Commented Jan 12, 2021 at 17:23
conda version is 4.9.2, running jupyter as admin throws the same error — ps0604
– ps0604, Commented Jan 12, 2021 at 17:24
@mao see here, could it be that the python version 3.8.5 is not supported? It says <= 3.8 — ps0604
– ps0604, Commented Jan 12, 2021 at 17:42
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, Commented Jan 12, 2021 at 17:45

mao · Accepted Answer · 2021-01-13 16:50:38Z

1

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).

edited Jan 13, 2021 at 16:50

answered Jan 13, 2021 at 13:52

mao

12.4k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

ps0604 Over a year ago

I followed your instructions and I could generate the package with no issues, but I'm still getting the same error:

conn = ibm_db.connect("UID=DB2INST1;PWD=xxxxxx;DATABASE=bank0002;HOSTNAME=localhost", "", "")  SQLCODE=-1042on: [IBM][CLI Driver] SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

ps0604 Over a year ago

I tried this from a simple python program in Visual Studio Code, and from Jupyter, I get the same error. What if I install miniconda for python 3.7 instead of 3.8, will that make a difference?

mao Over a year ago

As I have proved, conda 4.9.22 built ibm_db 3.0.2 successfully connects to Db2 on Win10. Verify with python at the Anaconda command line (i.e. not from jupyter or VS-Code ). If you get the same symptom, follow the documented procedure for db2trc , dump and format its output and study it for details.

ps0604 Over a year ago

Same symptom running at the Anaconda command line, will continue with db2trc

ps0604 Over a year ago

I did the trace, one thing that I see repeatedly is SQLO_INSTALLATION_NAME_NOT_FOUND

|

Collectives™ on Stack Overflow

Cannot connect to Db2 from python

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related