Connecting to IBM AS400 server for database operations hangs
Solution 1
The README for ibm_db_sa
only lists DB2 for Linux/Unix/Windows in the "Supported Database" section. So it most likely doesn't work for DB2 for i, at least not right out of the box.
Since you've stated you have IBM System i Access for Windows, I strongly recommend just using one of the drivers that comes with it (ODBC, OLEDB, or ADO.NET, as @Charles mentioned).
Personally, I always use ODBC, with either pyodbc
or pypyodbc
. Either one works fine. A simple example:
import pyodbc
connection = pyodbc.connect(
driver='{iSeries Access ODBC Driver}',
system='11.22.33.44',
uid='username',
pwd='password')
c1 = connection.cursor()
c1.execute('select * from qsys2.sysschemas')
for row in c1:
print row
Now, one of SQLAlchemy's connection methods is pyodbc
, so I would think that if you can establish a connection using pyodbc
directly, you can somehow configure SQLAlchemy to do the same. But I'm not an SQLAlchemy user myself, so I don't have example code for that.
UPDATE
I managed to get SQLAlchemy to connect to our IBM i and execute straight SQL queries. In other words, to get it to about the same functionality as simply using PyODBC directly. I haven't tested any other SQLAlchemy features. What I did to set up the connection on my Windows 7 machine:
-
Install
ibm_db_sa
as an SQLAlchemy dialect
You may be able to usepip
for this, but I did it the low-tech way:- Download
ibm_db_sa
from PyPI.
As of this writing, the latest version is 0.3.2, uploaded on 2014-10-20. It's conceivable that later versions will either be fixed or broken in different ways (so in the future, the modifications I'm about to describe might be unnecessary, or they might not work). - Unpack the archive (
ibm_db_sa-0.3.2.tar.gz
) and copy the enclosedibm_db_sa
directory into thesqlalchemy\dialects
directory.
- Download
-
Modify
sqlalchemy\dialects\ibm_db_sa\pyodbc.py
- Add the
initialize()
method to theAS400Dialect_pyodbc
class
The point of this is to override the method of the same name inDB2Dialect
, whichAS400Dialect_pyodbc
inherits from. The problem is thatDB2Dialect.initialize()
tries to set attributesdbms_ver
anddbms_name
, neither of which is available or relevant when connecting to IBM i using PyODBC (as far as I can tell). - Add the module-level name
dialect
and set it to theAS400Dialect_pyodbc
class
- Add the
Code for the above modifications should go at the end of the file, and look like this:
def initialize(self, connection):
super(DB2Dialect, self).initialize(connection)
dialect = AS400Dialect_pyodbc
Note the indentation! Remember, the initialize()
method needs to belong to the AS400Dialect_pyodbc
class, and dialect
needs to be global to the module.
Finally, you need to give the engine creator the right URL:
'ibm_db_sa+pyodbc://username:password@host/*local'
(Obviously, substitute valid values for username
, password
, and host
.)
That's it. At this point, you should be able to create the engine, connect to the i, and execute plain SQL through SQLAlchemy. I would think a lot of the ORM stuff should also work at this point, but I have not verified this.
Solution 2
The way to find out what port is needed is to look at the service table entries on the IBM i.
Your IBM i guy can use the iNav GUI or the green screen Work with Service Table Entry (WRKSRVTBLE) command
Should get a screen like so:
Service Port Protocol
as-admin-http 2001 tcp
as-admin-http 2001 udp
as-admin-https 2010 tcp
as-admin-https 2010 udp
as-central 8470 tcp
as-central-s 9470 tcp
as-database 8471 tcp
as-database-s 9471 tcp
drda 446 tcp
drda 446 udp
The default port for the DB is indeed 8471. Though drda is used for "distributed db" operations.
Based upon this thread, to use ibm_db to connect to DB2 on an IBM i, you need the IBM Connect product; which is a commercial package that has to be paid for.
This thread suggests using ODBC via the pyodbc module. It also suggests that JDBC via the JT400 toolkit may also work.
Solution 3
Here is an example to work with as400, sqlalchemy and pandas. This exammple take a bunch of csv files and insert with pandas/sqlalchemy. Only works for windows, on linux the i series odbc driver segfaults (Centos 7 and Debian 9 x68_64)
Client is Windows 10.
My as400 version is 7.3
Python is 2.7.14
installed with pip: pandas, pyodbc, imb_db_sa, sqlalchemy
You need to install i access for windows from ftp://public.dhe.ibm.com/as400/products/clientaccess/win32/v7r1m0/servicepack/si66062/
Aditionally the modifications by @JohnY on pyodbc.py C:\Python27\Lib\site-packages\sqlalchemy\dialects\ibm_db_sa\pyodbc.py Change line 99 to
pyodbc_driver_name = "IBM i Access ODBC Driver"
The odbc driver changed it's name.
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import glob
csvfiles=(glob.glob("c:/Users/nahum/Documents/OUT/*.csv"))
df_csvfiles = pd.DataFrame(csvfiles)
for index, row in df_csvfiles.iterrows():
datastore2=pd.read_csv(str(row[0]), delimiter=',', header=[0],skipfooter=3)
engine = create_engine('ibm_db_sa+pyodbc://DB2_USER:PASSWORD@IP_SERVER/*local')
datastore2.to_sql('table', engine, schema='SCHEMA', chunksize=1000, if_exists='append', index=False)
Hope it helps.
Related videos on Youtube
AH16
I got my computer science degree in 2011 and am most familiar with Python and Swift, though I know several other languages to some degree. I'm an IT guy at a local company, and like to hack on this and that when not at work. I'm also an editorial member of www.applevis.com in my spare time, and enjoy playing music and reading.
Updated on September 15, 2022Comments
-
AH16 over 1 year
I'm trying to talk to an AS400 in Python. The goal is to use SQLAlchemy, but when I couldn't get that to work I stepped back to a more basic script using just ibm_db instead of ibm_db_sa.
import ibm_db dbConnection = ibm_db.pconnect("DATABASE=myLibrary;HOSTNAME=1.2.3.4;PORT=8471;PROTOCOL=TCPIP;UID=username;PWD=password", "", "") #this line is where it hangs print ibm_db.conn_errormsg()
The problem seems to be the port. If I use the 50000 I see in all the examples, I get an error. If I use 446, I get an error. The baffling part is this: if I use 8471, which IBM says to do, I get no error, no timeout, no response whatsoever. I've left the script running for over twenty minutes, and it just sits there, doing nothing. It's active, because I can't use the command prompt at all, but it never gives me any feedback of any kind.
This same 400 is used by the company I work for every day, for logging, emailing, and (a great deal of) database usage, so I know it works. The software we use, which talks to the database behind the scenes, runs just fine on my machine. That tells me my driver is good, the network settings are right, and so on. I can even telnet into the 400 from here.
I'm on the SQLAlchemy and ibm_db email lists, and have been communicating with them for days about this problem. I've also googled it so much I'm starting to run out of un-visited links in my search results. No one seems to have the problem of the connection hanging indefinitely. If there's anything I can try in Python, I'll try it. I don't deal with the 400 directly, but I can ask the guy who does to check/configure whatever I need to. As I said though, several workstations can talk to the 400's database with no problems, and queries run against the library I want to access work fine, if run from the 400 itself. If anyone has any suggestions, I'd greatly appreciate hearing them. Thanks!
-
AH16 about 8 yearsWell, that seems to work! I can run queries and, obvciously, connect! There's so much out there about using ibm_db for this stuff that I'm surprised straight pyodbc did the job in a minute, whereas ibm_db/ibm_db_sa have failed for days. Now I just have to work out how to hook this back into sqlalchemy. Thanks for the help. Thanks to @Charles as well, for all your answers over the last couple days.
-
Jimmy over 7 yearsI'm having the same issue but only have access to a linux server. I'm assuming pyodbc works only on windows systems. Do you know a solution for linux?
-
John Y over 7 years@Jimmy - maybe you will find something useful here.
-
Jimmy over 7 years@JohnY This definitely helps!
-
TheDude about 2 yearsDo you have any experience writing back data to the i database? I have tried many combinations of libraries, dialects, drivers and so on by now but either I'm failing to establish the connection or having trouble writing stuff back to the db. With you solution above I can establish the connection but it seems that I cannot create a new table. Surprisingly, I don't get any error while executing the SQL statement.
-
John Y about 2 years@TheDude - my experience working with the IBM i database using Python is entirely with PyODBC directly. So I don't know if SQLAlchemy is swallowing errors, or if the database isn't reporting any. I will say that in my experience, if an SQL statement doesn't generate an error yet doesn't appear to have worked, it is usually an issue with commitment control (i.e. you tried to make a change but it was never committed). Another fairly common issue is insufficient authority on the i to make the change.