creation of table via sqlalchemy fails for ibm_db because numeric column is translated to a wrong decimal one.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. define a simple table like:
foo = Table('foo', meta,
    Column('somecol', NUMERIC(length=5, precision=2)),
    Column('othercol', NUMERIC(length=8, precision=0)),
)

2. try to foo.create() 

What is the expected output? What do you see instead?
I expect that the table is created.
I see this instead:
    raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError)
ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI
Driver][DB2/LINUX] SQL0604N  The length, precision, or scale attribute for
column, distinct type, structured type, array type, attribute of structured
type, routine, cast target type, type mapping, or global variable
"DECIMAL(2, 5)" is not valid.  SQLSTATE=42611 SQLCODE=-604 '\nCREATE TABLE
foo (\n\tsomecol DECIMAL(2, 5), \n\tothercol DECIMAL(31,0)\n)\n\n' {}

What version of the product are you using? On what operating system?
ibm_db 0.2.6
ibm_db_sa 0.1.1
python 2.5
sqlalchemy 0.4.3

Please provide any additional information below.
Looking at the creation statement I can see it is wrong.
CREATE TABLE foo (
    somecol DECIMAL(2, 5),
    othercol DECIMAL(31,0)
)

It should be
CREATE TABLE foo (
    somecol NUMERIC(5, 2),
    othercol NUMERIC(8,0)
)

Original issue reported on code.google.com by pyalot@gmail.com on 5 Mar 2008 at 11:05

[GoogleCodeExporter]

Original comment by apiti...@ca.ibm.com on 7 Mar 2008 at 6:21

• Changed state: Started

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Hi Florian,

While the generated SQL is obviously failing to create the table, I believe the
expectations are probably not quite correct:

If this is what's issued:
foo = Table('foo', meta,
    Column('somecol', NUMERIC(length=5, precision=2)),
    Column('othercol', NUMERIC(length=8, precision=0)),
)

Then this is what could be expected (first param is always precision, right? ...
while NUMERIC can be used as synonyms for DECIMAL):
CREATE TABLE foo (
    somecol NUMERIC(2, 5),
    othercol NUMERIC(0,8)
)

According to DB2 Info Center documentation (see CREATE TABLE):
"DECIMAL(precision-integer, scale-integer) or DEC(precision-integer, 
scale-integer)
    For a decimal number. The first integer is the precision of the number; that is,
the total number of digits; it may range from 1 to 31. The second integer is the
scale of the number; that is, the number of digits to the right of the decimal 
point;
it may range from 0 to the precision of the number.

    If precision and scale are not specified, the default values of 5,0 are used. The
words NUMERIC and NUM can be used as synonyms for DECIMAL and DEC."

Original comment by apiti...@ca.ibm.com on 7 Mar 2008 at 7:22

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Florian, if this is what's expected:

CREATE TABLE foo (
    somecol NUMERIC(5, 2),
    othercol NUMERIC(8,0)
)

This is what works:
>>> import ibm_db_sa.ibm_db_sa
>>> db2 =
sqlalchemy.create_engine('ibm_db_sa://dude:almighty@great.server.com:50000/pydev
')
>>> metadata = MetaData()
>>> foo = Table('foo', metadata,
...       Column('somecol', NUMERIC(precision=5, length=2)),
...       Column('othercol', NUMERIC(precision=8, length=0)),
... )
>>> metadata.bind = db2
>>> metadata.create_all()
>>> users_table = Table('users', metadata, autoload=True, autoload_with=db2)
>>> users_table
Table('users',
MetaData(Engine(ibm_db_sa://dude:almighty@great.server.com:50000/pydev)),
Column('user_id', IBM_DBInteger(), table=<users>, primary_key=True, 
nullable=False),
Column('user_name', IBM_DBString(length=None, convert_unicode=False,
assert_unicode=None), table=<users>, nullable=False), Column('email_address',
IBM_DBString(length=None, convert_unicode=False, assert_unicode=None),
table=<users>), Column('password', IBM_DBString(length=None, 
convert_unicode=False,
assert_unicode=None), table=<users>, nullable=False), schema=None)

And I think the confusion is w/r to DB2 precision being under length column

db2 => describe table foo

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale 
Nulls
------------------------------- --------- ------------------- ---------- ----- 
------
SOMECOL                         SYSIBM    DECIMAL                      5     2 
Yes
OTHERCOL                        SYSIBM    DECIMAL                      8     0 
Yes

Please let me know if you still believe there's an issue here. Thanks.

Original comment by apiti...@ca.ibm.com on 7 Mar 2008 at 7:38