CODEWITHSUNDEEP
python-2.7db2pyodbcpython-3.7
Rajesh
Rajesh

Reputation: 1

Python2.7-DB2v11.1.4.4 connectivity fails

I am setting up a development environment with the following:

  1. CentOS 7
  2. Python 2.7
  3. IBM-DB2 EE Server v11.1.4.4
  4. ibm-db package

My earlier installation and set up went smooth with no real issues on ODBC connectivity with the local DB2 trial database version. With my new install, I keep getting the following message:

Exception:

[IBM][CLI Driver] SQL1531N The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file. Data source name specified in the connection string: "DATABASE". SQLCODE=-1531

I did try updating the python version to 3.7 but results the same. I had to reiterate here that my earlier install with the same configuration went through without any issues. I never updated neither the db2cli.ini file nor db2dsdriver file. I did try here and it fails. As much I could gather, I saw a message which read like "ibm-db does not sit well with all python versions properly".

    >>> import difflib
    >>> import subprocess
    >>> import os
    >>> import ibm_db
    >>> from shutil import copyfile
    >>> conn = ibm_db.connect("DATABASE","USERID","PASSWORD")
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>

Exception:

[IBM][CLI Driver] SQL1531N The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file. Data source name specified in the connection string: "DATABASE". SQLCODE=-1531

I expect the connection to go through fine without any issues.

Upvotes: 0

Views: 391

Answers (1)

kkuduk
kkuduk

Reputation: 601

The short answer is that the easiest way is to use a complete DSN string to establish the connection (including the hostname, port etc.), e.g:

In [1]: import ibm_db
In [2]: conn = ibm_db.connect("DATABASE=SAMPLE;HOSTNAME=localhost;PORT=60111;UID=db2v111;PWD=passw0rd;","","")

The long answer is that we should be able to use the alias from the catalog, as explained in ibm_db.connect API:

IBM_DBConnection ibm_db.connect(string database, string user, string password [, dict options [, constant replace_quoted_literal])

  • database For a cataloged connection to a database, this parameter represents the database alias in the DB2 client catalog. For an uncataloged connection to a database, database represents a complete connection string in the following format: DRIVER={IBM DB2 ODBC DRIVER};DATABASE=database;HOSTNAME=hostname;PORT=port; PROTOCOL=TCPIP;UID=username;PWD=password; where the parameters represent the following values:

    • hostname - The hostname or IP address of the database server.
    • port - The TCP/IP port on which the database is listening for requests.
    • username - The username with which you are connecting to the database.
    • password - The password with which you are connecting to the database.

  • user - The username with which you are connecting to the database. For uncataloged connections, you must pass an empty string.

  • password- The password with which you are connecting to the database. For uncataloged connections, you must pass an empty string.

The question is though which client catalog we will check...

It all depends whether IBM_DB_HOME was set when package was installed, as explained in README. If it was set, then Python driver will use the existing client instance and its database catalog (as well as db2cli.ini and db2dsdriver.cfg). If not, then a separate client will be fetched during the installation and deployed in Python's site-packages.

In order to check which one is the case you can run ldd against your ibm_db.so, e.g:

ldd /usr/lib/python2.7/site-packages/ibm_db-2.0.7-py2.7-linux-x86_64.egg/ibm_db.so | grep libdb2
    libdb2.so.1 => /usr/lib/python2.7/site-packages/ibm_db-2.0.7-py2.7-linux-x86_64.egg/clidriver/lib/libdb2.so.1 (0x00007fb6e137e000)

Based on the output I can say that in my environment the diver was linked against a driver in Python's site-packages, so it will use db2cli.ini from /usr/lib/python2.7/site-packages/ibm_db-2.0.7-py2.7-linux-x86_64.egg/clidriver/cfg. If I will populate it with a section:

[sample]
port=60111
hostname=localhost
database=sample

I will be able to connect just with the DSN alias:

In [4]: conn = ibm_db.connect("SAMPLE","db2v111","passw0rd")

If you want the driver to use the existing client instance, use the IBM_DB_HOME during installation.

Upvotes: 1

Related Questions