Trouble connecting to Db2 for IBM i using pyodbc

Question

im trying to make a connection to an as400 with db2 using pyodbc and the ibm db2 odbc driver.

import pyodbc

connection = pyodbc.connect(
    driver='{IBM DB2 ODBC DRIVER}',
    system='192.168.1.100',
    uid='user',
    pwd='pass')
c1 = connection.cursor()

#this is meaningless sql, i just want the connection
c1.execute('select * from libname.filename')
for row in c1:
    print (row)

Running this gives me this error

python pydata.py

Traceback (most recent call last):
  File "C:\Users\tca\Desktop\ScriptingSTuff\pydata.py", line 3, in <module>
    connection = pyodbc.connect(
pyodbc.OperationalError: ('08001', '[08001] [IBM][CLI Driver] SQL1013N  The database alias name or database name "" could not be found.  SQLSTATE=42705\r\n (-1013) (SQLDriverConnect)')

Any ideas?

This is all under win10

EDIT: Adding this "database='s10c38ft',"

import pyodbc

connection = pyodbc.connect(
    driver='{IBM DB2 ODBC DRIVER}',
    system='192.168.1.100,8471',
    database='s10c38ft',
    uid='user',
    pwd='pass')
c1 = connection.cursor()

c1.execute('select * from libname.filename')
for row in c1:
    print (row)

Makes it hang on a blinking cursor, I cant even CTRL+C to end it, I have to close cmd.

Check the documentation for the ODBC driver. Supplying the port number by comma-appending it to the server name/IP (e.g., 192.168.1.100,8471) is something that I've only ever seen for Microsoft's ODBC drivers for SQL Server. Most other ODBC drivers use a separate SERVER= and PORT= arguments. — Gord Thompson
– Gord Thompson, Commented Jun 11, 2021 at 18:42

John Y · Accepted Answer · 2021-06-11 20:39:18Z

8

The proper driver name should be IBM i Access ODBC Driver (but see notes below). Other than that, your first example was correct:

connection = pyodbc.connect(
    driver='{IBM i Access ODBC Driver}',
    system='192.168.1.100',
    uid='user',
    pwd='pass')

If that doesn't work, there are two main possibilities:

You are using an old ODBC driver. This would happen if you are using the old iSeries Access (in which case the driver name is iSeries Access ODBC Driver) or even older Client Access (driver name Client Access ODBC Driver (32-bit)). If you choose the appropriate name for your driver, it will work.
You are using an ODBC driver that is not for IBM i. The most commonly used member of the Db2 family is Db2 for LUW (Linux, Unix, Windows), but there are others. None of these will work for you.

You can find out the list of exact ODBC driver names you have installed by calling pyodbc.drivers(). If you don't have any of the ones I mentioned above by name, then you don't have the right driver. The ODBC driver you want is the one described here.

edited Jun 11, 2021 at 20:39

answered Jun 11, 2021 at 19:11

John Y

14.6k2 gold badges51 silver badges76 bronze badges

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

6 Comments

Anthony Over a year ago

When I try that I get this error. "pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')"

Gord Thompson Over a year ago

@Anthony - Check the list returned by pyodbc.drivers() to see what ODBC drivers are available to your Python app.

Anthony Over a year ago

>>> print(pyodbc.drivers()) ['SQL Server', 'IBM DB2 ODBC DRIVER - IBMDBCL1', 'IBM DB2 ODBC DRIVER', 'IBM DB2 ODBC DRIVER - C_PROGRA~1_IBM_CLIDRI~1', '

John Y Over a year ago

Where did you get that driver? I have never heard of it. Do you maybe have a driver for Db2 for LUW?

Anthony Over a year ago

Heres the link I got it from ibm.com/support/pages/…

|

Collectives™ on Stack Overflow

Trouble connecting to Db2 for IBM i using pyodbc

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related