Using SQLAlchemy to connect from Windows to AIX / DB2

[Werner]

Hi,

I'm trying to connect from a Windows machine to and AIX 5.3 running
DB2 9.1. The error I get when I supply the wrong password is in the
db2cli.ini file is:

raise exceptions.DBAPIError.instance(None, None, e)
sqlalchemy.exceptions.OperationalError: (OperationalError)
ibm_db_dbi::Operati
alError: [IBM][CLI Driver] SQL30082N Security processing failed with
reason "
" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001\r
SQLCODE=-30082 None None

This is as expected. If I supply the correct password, I get

raise exceptions.DBAPIError.instance(None, None, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError)
ibm_db_dbi::DatabaseError:
[IBM][CLI Driver] CLI0133E Option type out of range. SQLSTATE=HY092
SQLCODE=-99999 None None

What am I doing wrong? Any help would be appreciated.

Thanks,

Werner

[prave...@in.ibm.com]

Hi Werner,

What is your DB2 client level? At minimum you will require DB2 V8 FP15
or DB2 V9 FP2. Please check your version. If the version is as per
requirement then please check if your environment variables are set
properly (see below for instructions).

The latest IBM Data Server Driver, the only dependency for the ibm_db
python driver and subsequently SQLAlchemy adapter, is available for
download at
http://www.ibm.com/support/docview.wss?rs=71&uid=swg21288110

Also, be sure that you have configured CLI properly. Please follow the
steps below-

* Set the DB2 environment variable DB2_CLI_DRIVER_INSTALL_PATH to
the directory where the driver is installed. Example-

set DB2_CLI_DRIVER_INSTALL_PATH=<Path where CLI is installed>

Usually the path would be C:\Program Files\IBM\IBM
DATA SERVER DRIVER

* Set LD_LIBRARY_PATH to lib directory where driver is installed.
Example-

set LD_LIBRARY_PATH=<CLI driver path>/lib

* Add the lib and bin directory to the PATH by issueing following
command.

set PATH=<clidriver_path>\bin;<clidriver_path>\lib;%PATH%



Thanks

Praveen

[Werner]

Hi Praveen,

Thanks for the prompt response.

I've switched from v9.1 FP1 to v9.1 FP2 (as you suggested), and now
this problem has been resolved. I can connected to AIX/DB2 and can
execute queries, no problem.

However, I've think I've now run into the same problem with SQLAlchemy
& metadata that has previously been discussed under heading "metadata
not loading any table info", since the following type of query for a
particular table (say <table-name>) works fine

result = conn.execute("select * from <table-name>")
for row in result:
print row
result.close()

but

<table-name> = Table('<table-name>', metadata, autoload=True)

raises the following error.

raise exceptions.NoSuchTableError(table.name)
sqlalchemy.exceptions.NoSuchTableError: <table-name>

Thanks,

Werner

[abhigyan...@in.ibm.com]

Are you using some different schema than the default schema? If I
remember correctly, that problem was because of different schema
name.

[Werner]

If I use the wrong schema name then the "select * from <table-name>"
fails with error

sqlalchemy.exceptions.ProgrammingError: (ProgrammingError)
ibm_db_dbi::Programmi
ngError: [IBM][CLI Driver][DB2/AIX64] SQL0204N "<schema-
name>.<table-name>" is a
n undefined name. SQLSTATE=42704\r SQLCODE=-204 'select * from
<table-name>'
{}

With the correct schema name the "select * from <table-name>" works
just fine, but

<table-name> = Table('<table-name>', metadata, autoload=True)

always fails either way. Have a look at the post in question on
metadata loading. At the end of it you've said

> abhigyan_agra...@in.ibm.com
> From: abhigyan_agra...@in.ibm.com
> Date: Tue, 1 Apr 2008 04:43:53 -0700 (PDT)
> Local: Tues, Apr 1 2008 1:43 pm
> Subject: Re: metadata not loading any table info
> Hi Narayan,
> Looks like when we give schema=<value> in Table(), then it
> adjusts the table name as SchemaName.TableName; same as when we try
> SchemaName.TableName manually. But in our code we are expecting these
> two to be different. So, we need to change our logic here. I will open
> an internal defect for it and work on it.

Has this problem been resolved?

Thanks,

Werner

[abhigyan...@in.ibm.com]

That case was a little different. Here, you are not specifying schema.
I have tried the following code:

import sqlalchemy
from sqlalchemy import *

metadata = MetaData()

db = sqlalchemy.create_engine('ibm_db_sa://user:password@ip:60000/
db')
metadata.bind = db
table = Table('test', metadata, autoload=True)

This works fine without any errors.

[Werner]

Hi,

A schema is being used. My schema name and database name is the same.
Here is a
compelte example that I just worked through.

First I created the SAMPLE database on AIX/DB2.

-- Login as db2inst1 and create the SAMPLE database

-bash-3.00$ db2sampl

Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "DB2INST1"...
Creating tables with XML columns and XML data in schema
"DB2INST1"...

'db2sampl' processing complete.

-bash-3.00$

--- From Windows, connect to the SAMPLE database with schema DB2INST1

from sqlalchemy import *

metadata = MetaData()

db = create_engine('ibm_db_sa://db2inst1:password@host:50001/
SAMPLE;CurrentSchema=DB2INST1')
metadata.bind = db

conn = db.connect()
result = conn.execute("select * from EMPLOYEE")

for row in result:
print row
result.close()

employee = Table('EMPLOYEE', metadata, autoload=True)
print ""
print employee.columns

--- This all worked fine. I could see all the data in the EMPLOYEE
table
--- as well as all the columns of the EMPLOYEE table.
--- Next, I'm trying to use my own database called MYDATA for
arguments sake.
--- It assocaited schema is called MYDATA as well.

--- From Windows, connect to MYDATA database with schema MYDATA

from sqlalchemy import *

metadata = MetaData()

db = create_engine('ibm_db_sa://db2inst1:password@host:50001/
MYDATA;CurrentSchema=MYDATA')
metadata.bind = db

conn = db.connect()
result = conn.execute("select * from MY_TABLE")

for row in result:
print row
result.close()

table = Table('MY_TABLE', metadata, autoload=True)
print ""
print table.columns

--- The data in MY_TABLE is displayed by the select statement, but the
Table('') mapping fails.

I'm stuck. Hope there's some kind of workaround.

Thanks,

Werner

[abhigyan...@in.ibm.com]

Hi Werner,
A quick workaround would be to change ibm_db_sa.py file and
the sample code a little.

Change your ibm_db_sa.py file (Usually residing on C:\Python25\Lib
\site-packages\ibm_db_sa-0.1.5-py2.5.egg\ibm_db_sa) and add following
lines after line 474 in reflecttable() function:

if table.schema is not None:
ibm_dbi_conn.set_current_schema(table.schema)
schema_name = table.schema

And change line 480 (line no. before adding above code) to:

columns = ibm_dbi_conn.columns( schema_name, table.name,
include_columns)

Now, you should be able to get metadata information by specifying
schema in your sample code in Table() like:
table = Table('table', metadata, autoload=True,schema='schema')
print table.columns


Let me know how this goes.

Regards,
Abhigyan

> > > Werner- Hide quoted text -
>
> - Show quoted text -

[Werner]

Hi,

That helped a great deal, I can now define the table and print its
columns. However, it now fails further down in my code when I try to
invoke the sqlalchemy.orm.mapper function.

table_name = Table(...)
print ""
print table_name.columns

class TableName(object):
pass

sqlalchemy.orm.mapper(
TableName,
table_name)

Traceback (most recent call last):
File "sqa_test02.py", line 99, in <module>
table_name)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\
__init__.py", line 566, in mapper
return Mapper(class_, local_table, *args, **params)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\
mapper.py", line 182, in __init__
self.__compile_pks()
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.7-py2.5.egg
\sqlalchemy\orm\
mapper.py", line 516, in __compile_pks
raise exceptions.ArgumentError("Mapper %s could not assemble any
primary key
columns for mapped table '%s'" % (self,
self.mapped_table.description))
sqlalchemy.exceptions.ArgumentError: Mapper Mapper|TableName|
table_name could not assemble any primary key columns for mapped table
'table_name'

The same database definition works fine on MS SQL Server. I'll do some
more digging to try and resolve this issue.

Is a more schema friendly release of ibm_db_sa planned any time soon?

Thanks,

Werner

[abhigyan...@in.ibm.com]

Good to know that your problem (atleast one of them) is resolved. :)
We rely heavily on people like you to find bugs and give valuable
suggestions to improve our offerings. I agree that we didn't delve
much on schema. Hopefully within a month, I will be able to make a
schema friendly release. I will inform you about the same.

Thanks,
Abhigyan

[rcredburn]

I'm encountering the same issues as Werner. A version that works
correctly with SA ORM and non default schemas should be high
priority. All DB2 databases I interact with use schemas extensively.
Thanks Abhigyan.

Robert (rredburn(at)us.ibm.com)

[abhigyan...@in.ibm.com]

hi Werner,
The problem is occuring because we changed just the column
variable in reflecttable(). Please make following changes to reflect
primary keys, indexes and foreign keys:
Line 506 of ibm_db_sa.py should read
pkeys = ibm_dbi_conn.primary_keys( True, schema_name, table.name)
and Line 518 should read
indexes = ibm_dbi_conn.indexes( True, schema_name, table.name)
and Line 533 should read
fkeys = ibm_dbi_conn.foreign_keys( True, schema_name, table.name)

Basically we need to use table.name instead of str(table).

Hopefully, after these changes, you will be able to proceed without
any further trouble. Please keep me informed on this.
As I said earlier, I will make a realease as soon as possible.

I am surprised that SQLAlchemy test suite didn't catch this error.

Thanks,
Abhigyan

On Aug 4, 5:19 pm, Werner <werner.fou...@gmail.com> wrote:

[abhigyan...@in.ibm.com]

Hi Robert,
Definitely it is in our priority list.