Connect to db2 database with Python

I'm working on an app that needs to connect to an ibm db2 database. Using DBeaver I can successfully connect to the database (I provide him the db2cc.jar and db2cc4.jar files).

It looks to me as DBeaver is using my Window's credentials to login, because I didn't need to input any login or password to connect.

Now, I've been trying to connect to the same database using python 3.7 and pypi's latest version of the ibm_db package. I didn't install anything else.

import ibm_db

# ...
connection_string = "DATABASE=" + self.params['schema'] + ";" + \
                                         "HOSTNAME=" + self.params['host'] + ";" + \
                                         "PORT=" + self.params['port'] + ";" + \
                                         "PROTOCOL=TCPIP;" + \
                                         "SECURITYMECHANISM=4;" + \
                                         "UID=" + self.params['user'] + ";" + \
                                         "PWD=" + self.params['password'] + ";"


try:
    self.connection = ibm_db.connect(connection_string, "", "")
    # ...

Using my Windows credentials in the parameters, I get the following error message:

Connection error
Bad credentials
SQLCODE=-30082
08001

From what I've seen on stack overflow connecting to a db2 database is complicated...

Does someone know how to connect ? Using the windows credentials or otherwise...

Thanks !

Solution

Db2 works fine with Python, i.e. with cpython.

You need to be aware of some basics before you start such as:

what operating-system runs the target Db2-database (Z/OS, i series, Linux/Unix/Windows/cloud) and
what kind of client is being used (java, odbc/cli, .net etc), and
what kind of authentication/encrpytion is in place for the database (ssl, server based authentication/+/-/encryption etc.).
is the remote database rdbms Apache DERBY or Db2.

Find out these basics before you start. You have to speak with people who run the Db2-server. If your target database is on Db2 on cloud, then

Note: in your question you mention (SecurityMechanism=4) i.e com.ibm.db2.jcc.DB2BaseDataSource.USER_ONLY_SECURITY - this is not relevant for non-JAVA clients, it is relevant if the database manager is DERBY .

For python, the ibm_db package is not a java application, it is a python extension written in the 'c' programming language. In fact, the python ibm_db extension is a wrapper for an underlying CLI driver (which does the real work of communicating with the database).

DBeaver is a java application (hence it uses a driver specifically for java, for example it uses either db2jcc.jar or more typically db2jcc4.jar and a licence-file to connect to the remote database, for certain platforms).

You can only use your Microsof-Windows credentials for connecting to a Db2-database either when that Db2-database runs on a Microsoft-Windows server, or when the Db2-server is confugured to integrate with Microsoft Active-Directory, and those credentials also work on the hostname running the Db2-server or Active-Directory.

For any other combinations (of Db2-client operating system, and Db2-server operating system), the server administrator (or security people) must issue you a userid/password that works on the target Database-server environment.

The ibm_db package needs a Db2-client to be installed, and by default it will automatically install IBM's clidriver package unless you explicitly tell the installer not to do that installation.

A Db2-client can also be separatly installed before python , if you so wish, in which case you must tell Python installer to not install clidriver, by setting environment variable IBM_DB_HOME before installing ibm_db as described in the readme. Typically the reason you would re-use a preexisting Db2-client for python is that your existing Db2-client is already configured with ODBC data source names (DSNs) for your databases and any licenses that might be needed. In that case python ibm_db would be able to reuse without reconfiguration.

There are different kinds of Db2-client available from IBM depending both on which operating-system runs your Db2-server and how much functionality you need to have in your Db2-client. At least two other non-IBM companies supply ODBC drivers for Db2-clients.

If your remote Db2-server runs on Linux, Unix, Windows or Z/OS then you can use the "IBM Data Server Runtime Client" which you can either download from IBM's passport advantage website, or get from your internal IT folks.

If your Db2-server runs on i-Series (AS/400) you should get its drivers from your i-Series administrator (the package IBM i access is available with an ODBC option).

For both Z/OS targets or i-Series targets you will additionally need a license file (which costs money) and you should get that from your administrator, unless your company uses a gateway product called Db2-connect in which case you don't need a separate license file on your workstation.