WP wrote:
> 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.
Okay, you're using an "implicit" connection which means the Java app is
connecting as the user it's running under. If you wanted to find out
which tables a specific user has created you could do something like:
SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE OWNER = 'USERNAME'
(If you're using DB2 v8 or below, user DEFINER instead of OWNER). To
find out which tables the currently connected user has created:
SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE OWNER = USER
The following *might* work for finding all user-defined tables (I'm not
sure, there might be some exceptions):
SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE OWNER <> 'SYSIBM'
BTW, don't use != for inequality - it's deprecated. <> is standard SQL.
Cheers,
Dave.
|
