Connecting to IBM AS400 server for database operations hangs

how to connect to ibm db2 database using python
ibm db2 python connection
pyodbc
db2 python scripts
ibm_db example
ibm_db python documentation
no module named 'ibm_db

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!

Connecting to IBM AS400 server for database operations hangs , 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  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

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.

Unable to connect to a database, connection to a database just hangs., An operating system level process may hold a lock on the database lock file. That is, a database might be locked. Resolving The Problem. To see  a. Verify that if the IBM server is up and running following the troubleshoot steps in this article. b. Check the port status by using “netstat” in your IBM server and verify the port is in state Listen. Secondly, choose each driver in the following screenshot when connecting to IBM database and check if it is successful.

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.

si31390 - ca400exp 5250 session hang when the mixed data , The iSeries Access for Windows OLE DB provider is not setting the query options be extended to display the 'Server mode connecting job:' within the current job with the reattach operation by pressing the Continue button, or cancel the  Versions Affected: Version 6 and Version 7 IBM i Access for Windows Operations Console connecting to IBM i V6.1.0 or later. When configuring a connection for a new server or server with no service tool LAN adapter configuration data, the connection sits at status of "Connecting console".

If you don't need Pandas/SQLAlchemy, just use pyodbc as suggested in John Y's answer. Otherwise, you can try doing what worked for me, below. It's taken from my answer to my own, similar question, which you can check out for more detail on what doesn't work (I tried and failed in so many ways before getting it working).

  1. I created a blank file in my project to appease this message that I was receiving:

    Unable to open 'hashtable_class_helper.pxi': File not found (file:///c:/git/dashboards/pandas/_libs/hashtable_class_helper.pxi).

    (My project folder is C:/Git/dashboards, so I created the rest of the path.)

  2. With that file present, the code below now works for me. For the record, it seems to work regardless of whether the ibm_db_sa module is modified as suggested in John Y's answer, so I would recommend leaving that module alone. Note that although they aren't imported by directly, you need these modules installed: pyodbc, ibm_db_sa, and possibly future (if using Python 2...I forget if it's necessary). If you are using Python 3, I you'll need urllib.parse instead of urllib. I also have i Access 7.1 drivers installed on my computer, which probably came into play.

import urllib
import pandas as pd
from sqlalchemy import create_engine

CONNECTION_STRING = (
    "driver={iSeries Access ODBC Driver};"
    "system=ip_address;"
    "database=database_name;"
    "uid=username;"
    "pwd=password;"
)

SQL= "SELECT..."

quoted = urllib.quote_plus(CONNECTION_STRING)
engine = create_engine('ibm_db_sa+pyodbc:///?odbc_connect={}'.format(quoted))

df = pd.read_sql_query(
    SQL,
    engine,
    index_col='some column'
)
print df

Troubleshooting DB2 connection delays, Unfortunately troubleshooting a slow query is beyond the scope of this technote. To check if this is the case, review the Database directory If connecting to a DB2 server on the AIX platform, the cause of the connection Check your operating system authentication configuration since DB2 calls OS  I'm trying to connect to an AS400 as well (or to the DB2 for i database). I downloaded the IBM Data Server Driver Package (DS Driver) and search on the IBM website the right drive but I could manage to find anything and I'm still getting this error: Does anyone else have the same problem? Many thanks, Ben

Unable to Start or Connect to TCP/IP Server, It is a very rare scenario to be unable to connect to a specific TCP/IP server such as Telnet I will assume that TCP/IP on the IBM Power Systems is up and running and that Step 1: On the operating system command line, type the following: This clears any problems there might be with the server jobs. After I connect to DS 4.0, my source database will be AS400 DB2 7.1. We will be loading data from AS400 DB2 7.1 to SAP HANA. I was checking DS the other day and when I tried to create a Data Store for DB2 there is only 2 options which are DB2 UDB 8.x and DB2 UDB 9.x.

LAN Console Connection for New Server Hangs at Connecting, 7 IBM i Access for Windows Operations Console that can result in a lan console connection for a new server hanging with status connecting. If you try to connect to an IBM AS400 iSeries database using the IBM DB2 driver included with Ignition, the connection will either report as "Faulted", or be stuck in the "Connecting" status.

db2 hangs with no messages, What could be a reason when db2 hang in the way that I cannot call "db2 connect​". The console least your platform , Operating System and DB2 level information. -- If the connections are hanging *before* the database crash, then it's not accessed from IBM WebSphere App Server that serves the CMP EJBs. There is  hi, First Download the IBM Client Access to your server. Below is the script to create the Linked server: exec sp_addlinkedserver @server=N’NAME_LINKED_SERVER’, @srvproduct=N’IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider’, @provider=N’IBMDA400′, @datasrc=N’X.X.X.X’, — IP do servidor @catalog=’CATALOG_SERVIDOR’, @provstr=’Default Collection=NOME_LIBRARY’

Comments
  • If you have made any progress on this, it would be great if you shared it here. I was poking around the code for ibm_db_sa to see if anything jumped out at me, and something did: The pyodbc.py module within ibm_db_sa has a class called AS400Dialect_pyodbc, and it has the right value for pyodbc_driver_name. So clearly at least some attempt was made to get SQLAlchemy working with DB2 for i.
  • I haven't. Well, my app is moving along, but for now I'm relying only on pyodbc. I tried to use SA by connecting to pyodbc, then passing the function that does the connecting to create_engine, but that didn't work.
  • How much does not having the extra functionality of SQLAlchemy hurt you? And how well do you (a) know Python and (b) know SQLAlchemy? The reason I ask is because through a bit more poking and prodding, I have managed to get create_engine() to successfully create an engine, and use that engine to do naked SQL queries. In other words, I can make SQLAlchemy serve as an extremely convoluted wrapper for PyODBC, with no value added. I'm happy to share my findings, but I have never used ORM, so there may well be further issues lurking about before you can fully leverage SQLAlchemy.
  • So where your knowledge comes in is, if there is any more experimentation and fixing that needs to be done, you may have to roll up your sleeves and get your hands a bit dirty.
  • I feel comfortable in Python, but I'm new to SQLAlchemy. The main reason for me wanting to use SA is the convenience of ORM, so if all it can handle is straight SQL, I guess I may as well stick to pyodbc. Thanks for following up, though.
  • Well, 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.
  • I'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?
  • @Jimmy - maybe you will find something useful here.
  • @JohnY This definitely helps!
  • Thanks for the quick reply. I'll ask them to double check the ports, but I'm told that no one has modified any of those. If I try 446 instead of 8471, I get something like "execution of the SQL statement failed because of an error in the communication data stream that will affect the successful execution of subsequent commands and SQL statements." SQLCode=-30020-0206, SQLState=58009
  • I forgot to say that I have all necessary IBM drivers and products installed (our company has access to those). In fact, other software we use that has to talk to the 400 runs on my machine without problems, so I'm positive that I have all necessary software. Also, I've just gotten confirmation that our ports are set up just like the standard ones (so 446 and 8471 are the two possible ones). Yet, 8471 is the one that hangs and never errors or times out, while 446 errors out within two seconds.
  • To add a bit to Charles' answer, almost all the examples on the net are for DB2 LUW or DB2 for mainframe. You really need exampled for DB2 for IBM i. Also, it's not an AS400, and that's really important when looking for help - if someone posted something as AS/400 it's either ancient, or THEY'RE ancient, and neither is likely to be helpful. Try IBM i or even iSeries.
  • My boss, and the other people who work with the system, all call it an AS400, so I figured that was the actual name. Thanks for the clarification on that. Also, I was told on a mailing list to only use 446, not 8471. When I do, I get SQLCode=-30020"0206")"". SQLState=58009. Hey, at least the attempt isn't hanging anymore! I'm sure my username and password are right, as is the host IP address and protocol setting (TCP/IP). I don't know... Do I need to put "http://" in front of the IP address?
  • In my (admittedly limited) understanding, Python needs DB2 Connect (or other similar driver) when using ibm_db. That's simply because it provides a pretty standard DRDA connection, e.g., via port 446. If you must use DRDA, options are limited. But the iAccess ODBC driver that works via pyodbc doesn't make the same connection. It connects to the database "host server" over port 8471 and has separate licensing requirements. I use pyodbc to avoid DB2 Connect, like @JohnY in his answer..