DB2 | table shows incorrect columns
Has anybody encountered this? Using IDM 6 and 7.
I have configured the database table adapter to connect to a DB2 table. The DB2 version is 7.1, so we are forced to use Type 2 jdbc driver (and not type 4).
I then installed db2connect and configured a dsn and connected to that dsn in the database table adapter wizard. It all connects fine and I can see all tables views etc that are accessible to the user configured inside the dsn.
It so happens that there are 2 tables with the same name with different owners (creators). But the dropdown listing all tables do not show the table owners - only table names are shown. I can see my table listed twice, but regardless of which one I pick, the same columns are shown and it happens to belong to the wrong table - not the table I am interested in.
Is there some setting I can use somewhere (dbconnect or java) where I can force jdbc to pull a specific table (owned by a specific user)? Or is there any workaround at all for this?
Thanks
[1021 byte] By [
anokun7a] at [2007-11-27 8:55:05]
# 2
Hey - Thanks for the response.
Can you also tell me how you are connecting to DB2?
We use a db2connect client which has an alias to the remote database. We connect to this alias using the type2 driver: COM.ibm.db2.jdbc.app.DB2Driver
The other thing is that the Db2 database is hosted on a mainframe server (OS/390) and is v7.1 so cannot use type 4 drivers.
# 3
I'm connecting using com.ibm.db2.jcc.DB2Driver, from the jar files that ship with DB2 v8.1. I'm running DB2 on a Linux machine.
I have no experience w/ db2connect, so can't speak substantively about that.
It seems that we're seeing a limitation/assumption in the implementation - if I direct Sun IdM to use 'regular' tables (i.e. no extra qualifier needed), then all is well. Otherwise, the ambiguity messes it up.
I'm hoping that there's a fix/workaround for this.
# 5
Hi,
As chasedm mentioned, we needed to export and modify our DB2 resource object manually. But for DB2, we needed to set table name as databasename.tablename. We also edited resource fields/table columns on the Resource object file. We still cannot manage resource fields from the administrative interface but we're able to make all accesses through the resource and the resource is successfully connected.
# 6
That seems like a viable option, but my problem is that I am not even able to save the resource to modify through debug - I keep getting the error:
Unable to access <table name>:COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0204N "IAMDGEN.<table name>" is an undefined name. SQLSTATE=42704
# 7
Thanks.. - OK this is what I ended up doing. Since the user did not own ANY tables (or objects) in DB2, I created a database table resource for a local mysql table.
Then I went into debug and changed the type back to DB2 and put the correct table name with the owner prefix. Of course I had to change the name to something more appropriate. I removed the id and imported back into IDM. Now I had a new resource - I click on this and edited the schema to include the other columns. And it works fine so far.
best regards
