Get "user" tables

[WP]

Hello, I need to communicate with a db2 database from a java program
and this java program needs to check which "user tables" there are. I
came up with the following query which I tried in Control Center:
select tabname, tabschema from syscat.tables where tabschema !=
'SYSCAT' and tabschema != 'SYSIBM' and tabschema != 'SYSIBMADM' and
tabschema != 'SYSSTAT' and tabschema != 'SYSTOOLS';
It seems to work, it returns just the tables that I have created
myself. But it is this the best way? The java program doesn't specify
a user upon connection because it's connecting through a local system
account I think. Therefore it cannot perform selection based on
username.

I just wanted to hear if I should proceed with this solution or if
there's some better approach.

- WP

 

[WP]

Hello, I need to communicate with a db2 database from a java program
and this java program needs to check which "user tables" there are. I
came up with the following query which I tried in Control Center:
select tabname, tabschema from syscat.tables where tabschema !=
'SYSCAT' and tabschema != 'SYSIBM' and tabschema != 'SYSIBMADM' and
tabschema != 'SYSSTAT' and tabschema != 'SYSTOOLS';
It seems to work, it returns just the tables that I have created
myself. But it is this the best way? The java program doesn't specify
a user upon connection because it's connecting through a local system
account I think. Therefore it cannot perform selection based on
username.

I just wanted to hear if I should proceed with this solution or if
there's some better approach.

- WP

I am so sorry, I sent this to the wrong newsgroup. It was meant for
the db2 group.

 

[Ronny Schuetz]

Hi,

Hello, I need to communicate with a db2 database from a java program
and this java program needs to check which "user tables" there are. I
[...]
myself. But it is this the best way? The java program doesn't specify
a user upon connection because it's connecting through a local system
account I think. Therefore it cannot perform selection based on
username.

You could check out the Database Metadata:

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html

retrievable via Connection#getMetaData(). Works fine for Oracle, no idea
about DB2.

Ronny

 

[Arne Vajhøj]

Hello, I need to communicate with a db2 database from a java program
and this java program needs to check which "user tables" there are. I
[...]
myself. But it is this the best way? The java program doesn't specify
a user upon connection because it's connecting through a local system
account I think. Therefore it cannot perform selection based on
username.

You could check out the Database Metadata:

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html

retrievable via Connection#getMetaData(). Works fine for Oracle, no idea
about DB2.

It is defined in the JDBC API - DB2 will support it.

Arne

 

[Arne Vajhøj]

Hello, I need to communicate with a db2 database from a java program
and this java program needs to check which "user tables" there are. I
came up with the following query which I tried in Control Center:
select tabname, tabschema from syscat.tables where tabschema !=
'SYSCAT' and tabschema != 'SYSIBM' and tabschema != 'SYSIBMADM' and
tabschema != 'SYSSTAT' and tabschema != 'SYSTOOLS';
It seems to work, it returns just the tables that I have created
myself. But it is this the best way? The java program doesn't specify
a user upon connection because it's connecting through a local system
account I think. Therefore it cannot perform selection based on
username.

I just wanted to hear if I should proceed with this solution or if
there's some better approach.

Either the above or the JDBC way (described in a previous post).

DB2 does not support the standard INFORMATION_SCHEMA views.

Arne