1

I'm trying to connect to a database on an IBM machine, and I can connect just fine via the IBM desktop client "IBM Data Studio." However, when I try to connect with pyodbc it fails to connect. I've received a series of errors but it seems the main response is along the lines of the following 

pyodbc.OperationalError: ('08001', u'[08001] [Microsoft][ODBC SQL Server Driver]
[TCP/IP Sockets]SQL Server does not exist or access denied. (17) 
(SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (
Connect()). (10061); [08001] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

My code follows:

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=*****; PORT=50000;DATABASE=****;UID=***;PWD=***')

# Create a cursor from the connection
cursor = cnxn.cursor()

cnxn.close()

I am working on a Windows machine.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Bennet Leff
  • 376
  • 1
  • 4
  • 18
  • Is the machine actually running Microsoft SQL Server? – Gord Thompson Jun 07 '18 at 16:33
  • Also is SQL server running on default port 1433? – Ian-Fogelman Jun 07 '18 at 16:33
  • The machine is an IBM server running DB2. I suppose that would mean it's not running Microsoft SQL server? I would imagine the server is running on the default port since the DBA was not sure. How can I check what port it is running on? – Bennet Leff Jun 07 '18 at 16:42
  • I notice this SO answer: https://stackoverflow.com/questions/6044326/how-to-connect-python-to-db2/ – Bill Bell Jun 07 '18 at 16:53
  • @BillBell Thanks, however I've tried that thread already to no avail. – Bennet Leff Jun 07 '18 at 16:57
  • (1) I don't have or use that IBM s/w. Can you use https://www.ibm.com/support/knowledgecenter/en/SSH2TE_1.1.0/com.ibm.7700.r2.datatools.doc/doc2/virt_app_patterns/cloud-tools/tds_dsconnect_db.html to find out about the connection? (2) What led you to use the Python `pyodbc` library rather than the library recommended in the IBM doc? – Bill Bell Jun 07 '18 at 17:40
  • I actually had nearly identical errors with both packages. I can follow up on the IBM support but their documentation has proven to be an inch deep and a mile wide. – Bennet Leff Jun 07 '18 at 18:36
  • Have you tried using the IBM-provided libraries? https://pypi.org/project/ibm_db/ – Stavr00 Jun 07 '18 at 19:48
  • Yes, I've tried the IBM libraries and get roughly the same errors. They all seem to be connection issues, however, I can connect with the same credentials just fine via the IBM Data Studio client. – Bennet Leff Jun 07 '18 at 19:50

3 Answers3

2

You cannot use an ODBC Driver for Microsoft SQL Server, to connect to an instance of IBM DB2 (nor to any DBMS other than Microsoft SQL Server).

You need an ODBC Driver for IBM DB2 for this connection, such as those from my employer.

You could also use an ODBC-to-JDBC Bridge Driver, in combination with a JDBC Driver for IBM DB2, such as JTOpen (open source, from IBM).

TallTed
  • 9,069
  • 2
  • 22
  • 37
2

Just because a jdbc connection from Data-Studio is working, it does not mean that your pyodbc will connect to Db2. pyodbc does not use jdbc, instead it will use the CLI/ODBC interface to Db2 which gets implemented by a suitable driver.

To use Db2 from pyodbc on Windows, ensure you first have either a Db2-client installed on Windows, or a Db2-server installed installed on Windows.

There are many kinds of Db2-clients. Much depends on the operating-system that runs your Db2-server (Z/OS, i-Series, Linux, Unix, Windows), and what kind of activities you want to perform on the client (developing, administering/monitoring, querying, or all three).

For some target platforms there are non-IBM drivers, but I won't discuss those.

In python, you can choose to connect to a database either with a DSN (Data Source Name) (usually this involves a shorter connection string) or without a DSN (longer connection string containing all the details).

When learning, it may be easier to get Microsoft Windows to do most of the initial work with odbcad32. This is most easy if the Db2-client is already configured to access one or more Db2-databases . The prereq is that the driver supports CLI/ODBC.

To define a DSN (either a system-DSN or a user-DSN) use the Microsoft odbcad32 tool to point to your Db2 database and verify connectivity.

If the Db2-server runs on Z/OS or i-Series, special licensing requirements may apply depending on whether you are directly connecting to the target Db2-server or whether you are using a Db2-connect gateway.

Take a note of the exact DRIVER string inside odbcad32 for the Db2-database, including case and spaces because you will need that in your python code.

For example, that driver name might look like 'IBM DB2ODBC DRIVER - DB2COPY1' if you have a local Db2-server installed on Windows (such as the free Db2-Express-C).

Verify that the connection to the Db2-database is successful inside odbcad32. That is crucial.

When odbcad32 succeeds to connect then pyodbc will succeed to connect usually.

In your python code, your connection-string can either use the DSN or explicitly quote the DRIVER/SERVER/PORT/DATABASE/UID/PWD, along with any other required settings on the connection string.

Remember also that you don't have to use pyodbc. There are other options for python to interact with Db2-Servers. Make an informed choice and do your research.

You can also use the IBM supplied module "ibm_db" or the DBI interface module "ibm_db_api", or if you are using an object relational mapper you can use the SQLAlchemy adapter (ibm_db_sa), or you can use django framework.

Read all about that in the Db2 documentation here. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.python.doc/doc/c0054366.html

mao
  • 11,321
  • 2
  • 13
  • 29
  • I've followed this but when I use port 8471, it hangs. If I use port 50000, it issues an error. It seems that 8471 is the right port because it's a default according to some IBM docs but obviously the program (odbcad32) hanging means something isn't working. – Bennet Leff Jun 08 '18 at 14:24
1

I have solved the problem! The issue was that I was missing the "iSeries Access ODBC Driver". I don't think it's available online, although I could be wrong, I had to have our database guy help install it. Now I can connect through both the ibm_db and pyodbc libraries, after setting up my DSN under "odbcad32.exe." This took a while to solve but mostly because of the lack of informative documentation from IBM. Hopefully, this helps anyone in the same situation.

Bennet Leff
  • 376
  • 1
  • 4
  • 18