JDBC: Connected but no tables shown

[anokun7]

Hi,

I am connecting to a DB2 v7.1 server from windows using JDBC type 2 (I
figured out that type 4 is only for 8 and upwards).

I used DB2 Connect to create a system DSN and seem to be able to
connect fine using db2connect and run queries etc.

The problem is when I try to use my java application to connect using
the same DSN. The way the db has been configured is that I am
connecting using a specific userid - this has been configured within
the System DSN. But the tables I need to read are owned (or created)
by another user. So I need to use the prefix of the owner/ creator to
access the tables. As of now no tables are shown.

I am using a product called IDM which basically connects to the DB2
Datasource (DSN) and once connected, it shows all tables available to
the user. I think what it does not know is that I might want to access
other tables which are not directly owned by this user (who is
configured in the DSN). Is there someway in the DSN or in the
connection string that I can tell jdbc to look at tables owned by
other users?

The parameters accepted by the jdbc client are: (and the values I
provided)

JDBC Driver : COM.ibm.db2.jdbc.app.DB2Driver
JDBC URL Template jdbc:db2:VISION
** I do not need to provide the following info if I give the URL **
Host
TCP Port
Database
User
Password

There are no errors when I connect, but no tables are shown.

Another thought is that in Oracle we use SYNONYMs, would that be a
viable option here.

Thanks
Anoop

[Otto Carl Marte]

When you connect to db2 your schema (by default) is set to your
userid. These "other users" probably created their tables,etc under
their own schema.
You can access these tables without qualifying the statement by
issuing the following commands on your connection.

set schema <schemaname>
set path SYSFUN,SYSPROC, <schemaname>

You can set this on the driver programmatically with the
setCurrentSchema(schemaname) and setCurrentFunctionPath(path) methods
on the datasource.

Or you can set the driver params on the URL.
i.e I think it would be something like this for your case:
jdbc:db2:VISION?
currentSchema=<schemaname>;currentFunctionPath=SYS FUN,SYSPROC,<schemaname>;

[anokun7]

On Jun 29, 3:21 am, Otto Carl Marte <[e-mail]:

When you connect to db2 your schema (by default) is set to your
userid. These "other users" probably created their tables,etc under
their own schema.
You can access these tables without qualifying the statement by
issuing the following commands on your connection.

set schema <schemaname>
set path SYSFUN,SYSPROC, <schemaname>

You can set this on the driver programmatically with the
setCurrentSchema(schemaname) and setCurrentFunctionPath(path) methods
on the datasource.

Or you can set the driver params on the URL.
i.e I think it would be something like this for your case:
jdbc:db2:VISION?
currentSchema=<schemaname>;currentFunctionPath=SYS FUN,SYSPROC,<schemaname>;

That looked promising, but with that url syntax, the connection just
wouldn't go through. here is the error stack trace I get:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0124E Invalid
argument value. SQLSTATE=HY009
at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(Unknown
Source)
at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(Unknown
Source)
at COM.ibm.db2.jdbc.app.DB2Connection.connect(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2Connection.<init>(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2Driver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:
512)
at java.sql.DriverManager.getConnection(DriverManager .java:
171)
at jdbctest.connect(jdbctest.java:297)
at jdbctest.main(jdbctest.java:79)
Error code is: -99999
SQLState is: HY009
Connection to jdbc:db2:VISION?
currentSchema=VIS;currentFunctionPath=SYSFUN,SYSPR OC,VIS; failed!

But thanks,
Anoop