连接到IBM AS400服务器以进行数据库操作将挂起 [英] Connecting to IBM AS400 server for database operations hangs

问题描述

我想和Python中的AS400交谈。目标是使用SQLAlchemy，但是当我无法得到工作，我回到一个更基本的脚本使用ibm_db而不是ibm_db_sa。

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()

似乎是港口。如果我使用50000我看到所有的例子，我得到一个错误。如果我使用446，我得到一个错误。令人困惑的部分是：如果我使用8471，它 IBM说要做，我得到没有错误，没有超时，没有任何响应。我已经让脚本运行了二十多分钟，它只是坐在那里，什么也不做。它是活跃的，因为我不能使用命令提示符，但它从来没有给我任何反馈任何种类。

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.

这同样400被公司使用工作每天，日志，电子邮件和（大量）数据库使用，所以我知道它的工作原理。我们使用的软件，在后台与数据库谈话，在我的机器上运行正常。这告诉我我的驱动程序是好的，网络设置是正确的，等等。我甚至可以从这里telnet到400.

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.

我在SQLAlchemy和ibm_db电子邮件列表，并已经与他们沟通了几天关于这个问题。我也搜索了这么多，我开始耗尽未访问的链接在我的搜索结果。没有人似乎有连接挂无限期的问题。如果有什么我可以尝试在Python中，我会尝试。我不直接处理400，但我可以问的人，检查/配置任何我需要的。正如我所说，几个工作站可以谈到400的数据库没有问题，查询运行反对库我想访问工作正常，如果从400本身运行。如果任何人有任何建议，我非常感谢听到他们。谢谢！

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!

推荐答案

README ibm_db_sa 仅列出了支持的数据库部分中的DB2 for Linux / Unix / Windows。因此，它很可能不适用于DB2 for i，至少不是现成的。

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.

因为你已经声明你有IBM System i Access for Windows ，我强烈建议只使用其中的一个驱动程序（ODBC，OLEDB或ADO.NET，如@Charles所述）。

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).

个人而言，我总是使用ODBC，使用 pyodbc 或 pypyodbc 。任一个工作正常。一个简单的例子：

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

现在，SQLAlchemy的连接方法之一是 pyodbc ，所以我认为如果你可以建立连接使用 pyodbc 直接，你可以以某种方式配置SQLAlchemy做同样的。但我不是自己的SQLAlchemy用户，所以我没有示例代码。

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

我设法使SQLAlchemy连接到我们的IBM i并执行直接的SQL查询。换句话说，要使其具有与简单地直接使用PyODBC相同的功能。 我在Windows 7计算机上设置连接的操作：

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:

• 将 ibm_db_sa 安装为SQLAlchemy方言
  
  您可以使用 pip 为此，但我做了低科技的方式：

• Install ibm_db_sa as an SQLAlchemy dialect
  You may be able to use pip for this, but I did it the low-tech way:

1. 下载 ibm_db_sa 。
   
   在撰写本文时， 0.3.2，uploaded 2014-10-20。可以想象，以后的版本将以不同的方式修复或破坏（因此，将来，我要描述的修改可能是不必要的，或者它们可能不起作用）。


2. 解压缩归档文件（ ibm_db_sa-0.3.2.tar.gz ）并将随附的 ibm_db_sa 目录复制到 sqlalchemy\dialects 目录。

1. 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).
2. Unpack the archive (ibm_db_sa-0.3.2.tar.gz) and copy the enclosed ibm_db_sa directory into the sqlalchemy\dialects directory.

sqlalchemy\dialects\ibm_db_sa\pyodbc.py

• 添加 initialize（）方法到 AS400Dialect_pyodbc 类

这是为了覆盖同名的方法在 DB2Dialect 中，其中 AS400Dialect_pyodbc 继承。问题是 DB2Dialect.initialize（）尝试设置属性 dbms_ver 和 dbms_name ，在使用PyODBC连接到IBM i时都不可用或不相关。


• 添加模块级名称方言并将其设置为 AS400Dialect_pyodbc 类

• Add the initialize() method to the AS400Dialect_pyodbc class
  The point of this is to override the method of the same name in DB2Dialect, which AS400Dialect_pyodbc inherits from. The problem is that DB2Dialect.initialize() tries to set attributes dbms_ver and dbms_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 the AS400Dialect_pyodbc class

上述修改的代码应放在文件末尾，如下所示：

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

注意缩进！记住， initialize（）方法需要属于 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'

（显然，用 username c $ c> password 和 host 。）

(Obviously, substitute valid values for username, password, and host.)

在这一点上，您应该能够创建引擎，连接到i，并通过SQLAlchemy执行纯SQL。我认为很多ORM的东西也应该在这一点上工作，但我还没有验证这一点。

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.

这篇关于连接到IBM AS400服务器以进行数据库操作将挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！