Connecting to AS400 with SQLAlchemy fails

[Alex Hall]

Hello list,
I sent this to the ibm_db list yesterday, but no one has responded
yet. Since it's as much ibm_db as SA, I thought I'd try here as well
in case any of you have used an AS400 before. I have ibm_db,
ibm_db_sa, the latest sqlalchemy, and Python 2.7 (latest) installed. I
can talk to SQLite with no trouble, it's talking to this 400 that
won't work. Anyway...

I'm finally ready to hook my app to the 400 instead of the local
SQLite database I've been using for testing. Here's my simple script:

import globals
import logging
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

#set up the sqlalchemy objects
dbEngine = create_engine('ibm_db_sa://username:pass...@mysite.com:8471/database')
Session = sessionmaker(bind = dbEngine) #note that's a capital s on Session
session = Session() #lowercase s
base = declarative_base()

def getAllTables():
 dbInspector = inspect(dbEngine)
 for table in dbInspector.get_table_names():
  print table

getAllTables()

When I run that, it waits thirty seconds or so, then tells me there
was an error. I'll paste the entire traceback below. Sorry in
advance--it's pretty long.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\admin\Dropbox\Autodist\jobs>c:\python27\python.exe DBInterface2.py
Traceback (most recent call last):
  File "DBInterface2.py", line 24, in <module>
    getAllTables()
  File "DBInterface2.py", line 18, in getAllTables
    dbInspector = inspect(dbEngine)
  File "c:\python27\lib\site-packages\sqlalchemy\inspection.py", line 63, in ins
pect
    ret = reg(subject)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 139
, in _insp
    return Inspector.from_engine(bind)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 135
, in from_engine
    return Inspector(bind)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 109
, in __init__
    bind.connect().close()
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2018, in
connect
    return self._connection_cls(self, **kwargs)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 72, in __
init__
    if connection is not None else engine.raw_connection()
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2104, in
raw_connection
    self.pool.unique_connection, _connection)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2078, in
_wrap_pool_connect
    e, dialect, self)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1405, in
_handle_dbapi_exception_noconnection
    exc_info
  File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line 200, in r
aise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2074, in
_wrap_pool_connect
    return fn()
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 318, in unique_c
onnection
    return _ConnectionFairy._checkout(self)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 713, in _checkou
t
    fairy = _ConnectionRecord.checkout(pool)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 480, in checkout

    rec = pool._do_get()
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1060, in _do_get

    self._dec_overflow()
  File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60,
in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1057, in _do_get

    return self._create_connection()
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 323, in _create_
connection
    return _ConnectionRecord(self)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 449, in __init__

    self.connection = self.__connect()
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 607, in __connec
t
    connection = self.__pool._invoke_creator(self)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 97,
 in connect
    return dialect.connect(*cargs, **cparams)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line 385, i
n connect
    return self.dbapi.connect(*cargs, **cparams)
  File "c:\python27\lib\site-packages\ibm_db-2.0.6-py2.7.egg\ibm_db_dbi.py", lin
e 588, in connect
    raise _get_exception(inst)
sqlalchemy.exc.OperationalError: (ibm_db_dbi.OperationalError) ibm_db_dbi::Opera
tionalError: [IBM][CLI Driver] SQL30081N  A communication error has been detecte
d. Communication protocol being used: "TCP/IP".  Communication API being used: "
SOCKETS".  Location where the error was detected: "[IP of the 400
server]".  Communicatio
n function detecting the error: "connect".  Protocol specific error code(s): "10
060", "*", "*".  SQLSTATE=08001\r SQLCODE=-30081

(I took out the actual IP). I'm not sure what most of that means, but
it looks like I can't connect to the 400 for some reason. Bad
credentials or something could be it, I suppose, but I'm using what my
work told me to use. I wondered if anyone here could spot the problem
before I go chasing different login details that I don't actually
need. Thanks in advance for any help.

[Jaimy Azle]

Connecting to AS400 from native ibm_db_dbi driver would need db2 connect which is a separated product from IBM. Use the ibm_db_sa pyodbc driver instead, or jdbc (jython) if you don't have db2 connect installed on your machine.

Salam,

-Jaimy

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Alex Hall]

I think I'm confused. Isn't Pyodbc an alternative to SQLAlchemy? If
not, how would the two work together? I just looked through the
'Getting Started' and 'API' docs for Pyodbc, and I don't see any
examples. I found some samples online of people using the two
together, but I don't quite follow how the process works. Thanks.

[Alex Hall]

I've done more research on this topic. There's a lot out there about
using MSSQL with SA, but next to nothing about using ibm_db_sa or
specifying drivers.

I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've
put db2odbc64.dll in my project folder, but don't know how to point SA
or pyodbc to it. I've tried several versions of
"?driver="db2odbc64.dll"" appended to my connection string, but I keep
getting an error: "data source not found and no default driver
specified". It doesn't even time out anymore, it just errors out
immediately. I've also tried "ibm_db_sa+pyodbc://" to start the
string, but that fails too.

This *must* be a simple thing, but I can't work out what to do, and
Google is failing me. If anyone has any ideas, I'd greatly appreciate
hearing them. Thanks, and sorry to keep bugging the list about this. I
just have no other options at the moment and I need to get this
working soon.

[Michal Petrucha]

On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote:
> I've done more research on this topic. There's a lot out there about
> using MSSQL with SA, but next to nothing about using ibm_db_sa or
> specifying drivers.
>
> I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've
> put db2odbc64.dll in my project folder, but don't know how to point SA
> or pyodbc to it. I've tried several versions of
> "?driver="db2odbc64.dll"" appended to my connection string, but I keep
> getting an error: "data source not found and no default driver
> specified". It doesn't even time out anymore, it just errors out
> immediately. I've also tried "ibm_db_sa+pyodbc://" to start the
> string, but that fails too.
>
> This *must* be a simple thing, but I can't work out what to do, and
> Google is failing me. If anyone has any ideas, I'd greatly appreciate
> hearing them. Thanks, and sorry to keep bugging the list about this. I
> just have no other options at the moment and I need to get this
> working soon.

Hi Alex,

Unfortunately, I can't offer you any specific help with IBM DB, but
judging by the number of replies, it seems nobody on this list can, so
I only have some stab-in-the-dark suggestions.

In my experience with enterprise software, *nothing* is ever a simple
thing, not even seemingly trivial operations, such as connecting to a
database.

You can try using either pyodbc, or the ibm_db driver – in both cases,
those are just the Python DBAPI drivers which take in textual SQL
statements, send them to the database in the low-level network
protocol, and present the results as dumb Python objects. SQLAlchemy
is a layer on top of them. That means, the first step would be to get
your Python runtime to open a raw pyodbc, or ibm_db connection to the
server, and be able to execute raw SQL statements there. Only after
you confirm this works you can move on to getting SQLAlchemy to work
with the DBAPI driver.


In my understanding, pyodbc is a wrapper around the library unixodbc.
I'm not sure how it's implemented on Windows – whether it's a port of
unixodbc, or it uses a different ODBC implementation there. Whatever
the case, though, on Linux with unixodbc, when I wanted to connect to
MS SQL, I had to register a low-level driver with the unixodbc
library. I had to edit a system-wide configuration file
(/etc/unixODBC/odbcinst.ini), and create a new driver definition in
there to make unixodbc recognize the FreeTDS driver I'm using as the
low-level protocol implementation.

I have no idea what low-level ODBC driver is required to connect to
IBM DB, I'm afraid you'll have to figure that out on your own. The
official IBM docs at
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4
seem to imply that IBM provides their own low-level ODBC driver which
you'll need to have in place in order to be able to connect to the
server using ODBC.

In any case, I would expect that the ODBC machinery would expect to
have the db2odbc64.dll registered somehow with a symbolic name in some
configuration file, registry, or whatever, and that would be the
string you're expected to pass as the driver name in the ODBC
connection string.

Actually, I think with ODBC, you're expected to define all database
servers in a system-wide configuration file or some such, give each
one of them a nickname (“DSN”), and just use that to connect to the
database.


The other option is to use the ibm_db Python DBAPI driver. I expect
you have already seen the official docs:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/c0054366.html
Have you tried following the set-up steps in that section there? Try
to first get it into a state where you can connect to the database
with ``ibm_db.connect()``, and successfully execute SQL statements
from the Python shell.

Once you get that to work, you can try to install ibm_db_sa, and try
to call
``sqlalchemy.create_engine('ibm_db_sa://user:pass...@host.name.com:50000/database')``

If that fails, you still have the option to turn to the official IBM
support channels – you're using an expensive enterprise database,
which should come with a support contract. The ibm_db drivers seem to
be officially supported by IBM, which would mean, if it doesn't work
for you, you should be able to request help from IBM to fix it.

Good luck!

Michal

[Alex Hall]

Thanks so much for your reply--this really helps! I asked the people
at work, and was told that my machine does, in fact, have some sort of
IBM manager installed. (Can you tell I'm new to this technology and
this job?) Using it, I was able to create a DSN to the test database
and, it seems, connect. I'm getting an error when I call

dbInspector = inspect(dbEngine)

but at least I'm getting that far. I'll ask about the error in a
separate thread, since more people are likely to have run across that
than seem to have experience with the 400 and IBM's wrapper.

[nahumcastro]

Hello all.

I have the same problem with db2 for as400, seems to be very different from db2 in windows, linux.

Here is what I have found:

this string dont apply for as400 as documented:

ibm_db_sa://user:pass@server:port/database

because when you connect to an as400 there is only one database with a bunch of schems.

It should be:

ibm_db_sa+pyodbc://user:password@ server:446/*local

*local are the equivalent to a database.

Now the problem is that you need a license to connect to the server or you will get:

 SQL1598N  Ha fallado un intento de conectar con el servidor de bases de datos debido a un problema de licencia.

The text is in spanish but says: buy a license to connect to the server.

> ``sqlalchemy.create_engine('ibm_db_sa://user:password@host.name.com:50000/database')``

[Mike Bayer]

hi there -

please direct these requests to the IBM list at:

https://groups.google.com/forum/#!forum/ibm_db

>> > ``sqlalchemy.create_engine('ibm_db_sa://user:pass...@host.name.com:50000/database')``

>> >
>> > If that fails, you still have the option to turn to the official IBM
>> > support channels – you're using an expensive enterprise database,
>> > which should come with a support contract. The ibm_db drivers seem to
>> > be officially supported by IBM, which would mean, if it doesn't work
>> > for you, you should be able to request help from IBM to fix it.
>> >
>> > Good luck!
>> >
>> > Michal
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> > Groups
>> > "sqlalchemy" group.
>> > To unsubscribe from this group and stop receiving emails from it, send
>> > an
>> > email to sqlalchemy+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>> >
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---