SQLAlchemy Foreign Key Issues with Db2

[Brian T]

I'm running SQLAlchemy 1.2.12.  When trying to autoload a DB2 table, it gives me a "no such table" error for a table referenced in a foreign key, even though that table exists.  I'm running the most recent version of the IBM SqlAlchemy adapter.

    import sqlalchemy
   
    cnxstr = 'ibm_db_sa://xyzzy'
    db2 = sqlalchemy.create_engine(

cnxstr)
    meta = sqlalchemy.MetaData(db2)   
    tbl = sqlalchemy.Table('child_table', meta, schema='plugh', autoload_with=db2)
   
    NoSuchTableError: parent_table

Not that this code works fine -- SQLAlchemy is able to find the parent table in other contexts.

    tbl = sqlalchemy.Table('parent_table', meta, schema='plugh', autoload_with=db2)

I'm also able to construct the tables by hand using the inspector.  It's only when I use Autoload that I run into this problem.  Is there a way to fix the autoload?

[Abhinav Radke]

Hi,

Sorry for late reply, I am looking into this issue. I will update you soon.

Thanks,

Abhinav

[Abhinav Radke]

Hi,

I executed below script but it is working for me. 

db2 = sqlalchemy.create_engine('ibm_db_sa://db2admin:db2a...@db2host.com:50000/mydb')

meta = sqlalchemy.MetaData(db2)    

tbl = sqlalchemy.Table('emp',  meta, schema='user1', autoload_with=db2,autoload=True)

print([c.name for c in tbl.columns])

can you share more steps to reproduce. I am unable to repro it.

Can you share your pip list.

[Brian T]

OK!

I don't have the ability to create my own DB2 tables to provide an easily reproducible example, but here's what I have.  Note that everything is upper case. --

We're running DB2 v10.5.0.7, fixpack 7 on AIX.

Parent Table --

SET SCHEMA = 'PLUGH';

CREATE TABLE "PLUGH"."PARENT_TABLE" (
  "QUOTE_ID"    DECIMAL(14,0)    NOT NULL,
  "SRC_SYS_CD"    VARCHAR(160)
)
  IN "FDWTS424_16K"
  INDEX IN "FDWIX424_16K"
  DISTRIBUTE BY HASH ( "QUOTE_ID" )
  COMPRESS YES
  WITH RESTRICT ON DROP;

ALTER TABLE "PLUGH"."PARENT_TABLE"
  DATA CAPTURE NONE
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

ALTER TABLE "PLUGH"."PARENT_TABLE"
  ADD PRIMARY KEY
    ("QUOTE_ID");

SET SCHEMA = 'SYSIBM';

GRANT CONTROL ON TABLE "PLUGH"."PARENT_TABLE" TO USER "BCUAIX";

SET SCHEMA = 'PLUGH';

Child table --

SET SCHEMA = 'PLUGH';

CREATE TABLE "PLUGH"."CHILD_TABLE" (
  "QUOTE_ID"    DECIMAL(14,0)    NOT NULL,
  "DATA_CNTR_CD"    VARCHAR(40)
)
  IN "FDWTS424_16K"
  INDEX IN "FDWIX424_16K"
  DISTRIBUTE BY HASH ( "QUOTE_ID" )
  COMPRESS YES
  WITH RESTRICT ON DROP;

ALTER TABLE "PLUGH"."CHILD_TABLE"
  DATA CAPTURE NONE
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

ALTER TABLE "PLUGH"."CHILD_TABLE"
  ADD PRIMARY KEY
    ("QUOTE_ID");

SET SCHEMA = 'SYSIBM';

GRANT CONTROL ON TABLE "PLUGH"."CHILD_TABLE" TO USER "BCUAIX";

GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
    INDEX, REFERENCES ON TABLE "PLUGH"."CHILD_TABLE" TO USER "BCUAIX" WITH GRANT OPTION;

SET SCHEMA = 'PLUGH';

ALTER TABLE "PLUGH"."CHILD_TABLE"
  ADD FOREIGN KEY
    ("QUOTE_ID")
  REFERENCES "PLUGH"."PARENT_TABLE"
    ("QUOTE_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT ENFORCED
    ENABLE QUERY OPTIMIZATION;

On Monday, October 22, 2018 at 6:21:12 AM UTC-7, Abhinav Radke wrote:

Hi,

I executed below script but it is working for me. 

db2 = sqlalchemy.create_engine('ibm_db_sa://db2admin:db2admin@db2host.com:50000/mydb')

[Brian Trautman]

Have you had a chance to take a look at this?

db2 = sqlalchemy.create_engine('ibm_db_sa://db2admin:db2a...@db2host.com:50000/mydb')

meta = sqlalchemy.MetaData(db2)    

tbl = sqlalchemy.Table('emp',  meta, schema='user1', autoload_with=db2,autoload=True)

print([c.name for c in tbl.columns])

can you share more steps to reproduce. I am unable to repro it.

Can you share your pip list.

On Thursday, 18 October 2018 15:32:53 UTC+5:30, Abhinav Radke wrote:

Hi,

Sorry for late reply, I am looking into this issue. I will update you soon.

Thanks,

Abhinav

On Monday, 15 October 2018 08:55:56 UTC+5:30, Brian T wrote:

I'm running SQLAlchemy 1.2.12.  When trying to autoload a DB2 table, it gives me a "no such table" error for a table referenced in a foreign key, even though that table exists.  I'm running the most recent version of the IBM SqlAlchemy adapter.

    import sqlalchemy
   
    cnxstr = 'ibm_db_sa://xyzzy'
    db2 = sqlalchemy.create_engine(

cnxstr)
    meta = sqlalchemy.MetaData(db2)   
    tbl = sqlalchemy.Table('child_table', meta, schema='plugh', autoload_with=db2)
   
    NoSuchTableError: parent_table

Not that this code works fine -- SQLAlchemy is able to find the parent table in other contexts.

    tbl = sqlalchemy.Table('parent_table', meta, schema='plugh', autoload_with=db2)

I'm also able to construct the tables by hand using the inspector.  It's only when I use Autoload that I run into this problem.  Is there a way to fix the autoload?

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

[Abhinav Radke]

Hi,

I am looking into it but still not able to reproduce it.

Can you capture db2trc and git it to us to analyse it.

to capture db2trc please follow below commands.

db2trc on -f trc.dmp 
`run your application` (from your client)
db2trc off
db2trc flw trc.dmp trc.flw
db2trc fmt trc.dmp trc.fmt

db2trc fmt -cli trc.dmp trc.cli
db2trc fmt -c trc.dmp trc.fmtc

Thanks,

Abhinav

db2 = sqlalchemy.create_engine('ibm_db_sa://db2admin:db2admin@db2host.com:50000/mydb')