Get "user" tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WP - 29 Apr 2008 16:19 GMT

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

Reply to this Message

Lennart - 29 Apr 2008 16:36 GMT

> 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
[quoted text clipped - 12 lines]
>
> - WP

You could simplify the query to ... where tabschema not like 'SYS%'.
However, I'm somewhat surprised that you don't know which schema's
there are, is there some specific reason for that?

/Lennart

Reply to this Message

Dave Hughes - 29 Apr 2008 16:37 GMT

> 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
[quoted text clipped - 7 lines]
> 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.

Reply to this Message

WP - 29 Apr 2008 16:49 GMT

[snip]

> 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
[quoted text clipped - 6 lines]
>
> SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE OWNER = USER

SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE OWNER = USER; works
just fine and I think I'm going to use that. Right now, the java
program and the db2 database can be assumed to be running on the same
machine but I'm thinking maybe I should reconfigure things so the java
program has to specify username when connecting (and password maybe).
Hmm.

Thanks for the quick replies!

[snip]

> BTW, don't use != for inequality - it's deprecated. <> is standard SQL.

Ah, thanks, I will remember that. First I tried "IS NOT" but that
didn't work, heh.

By the way, I'm using verion 9.5.

> Cheers,
>
> Dave.

- WP

Reply to this Message

Dave Hughes - 29 Apr 2008 20:55 GMT

> [snip]
> >
[quoted text clipped - 17 lines]
> program has to specify username when connecting (and password maybe).
> Hmm.

Depends - if you can be sure the Java app will always be on the same
machine as the database I wouldn't bother adding username + password.
If there's a possibility the two will be separated at some point it'd
be worth it though.

> Thanks for the quick replies!
>
[quoted text clipped - 5 lines]
> Ah, thanks, I will remember that. First I tried "IS NOT" but that
> didn't work, heh.

Ah - IS and IS NOT are only used for testing for NULL ("col IS NULL",
"col IS NOT NULL"). "col = NULL" and "col <> NULL" aren't useful due to
the tri-state logic that SQL employs ... "col = NULL" always evaluates
to NULL, not TRUE or FALSE (and a NULL result is equivalent to FALSE in
things like WHERE clauses).

BTW - did you pop into the #db2 channel on Freenode asking about this?
Someone asked a very similar question (involved "IS NOT"),
unfortunately it was an hour before I noticed and they'd gone by then
(it's a pretty quiet channel - usually takes a while for people to
notice anyone's said something!). Anyway, if it was you, sorry I missed
it :-)

Cheers,

Dave.

Reply to this Message

WP - 30 Apr 2008 09:05 GMT

> > [snip]
>
[quoted text clipped - 22 lines]
> If there's a possibility the two will be separated at some point it'd
> be worth it though.

OK, I think I will leave it as it is for the meantime then.

> > Thanks for the quick replies!
>
[quoted text clipped - 18 lines]
> notice anyone's said something!). Anyway, if it was you, sorry I missed
> it :-)

Hehe, yes, that was indeed me. Then I was having slight trouble with
the actual SELECT-statement but I got it sorted (I'm rusty at SQL and
haven't worked with DB2 before). Seems to be a small but good channel
(when active). :)

> Cheers,
>
> Dave.

- WP

Reply to this Message