SEO

permalink · #1 (**permalink**) 04-29-2008, 08:26 PM

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

permalink · #2 (**permalink**) 04-29-2008, 08:26 PM

On Apr 29, 5:19 pm, WP <mindcoo...@gmail.com> 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.
>
> I just wanted to hear if I should proceed with this solution or if
> there's some better approach.
>
> - 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

permalink · #3 (**permalink**) 04-29-2008, 08:26 PM

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.

permalink · #4 (**permalink**) 04-29-2008, 08:26 PM

On 29 Apr, 17:37, "Dave Hughes" <d...@waveform.plus.com> wrote:
> WP wrote:
[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
> 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

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

permalink · #5 (**permalink**) 04-29-2008, 08:26 PM

WP wrote:

> On 29 Apr, 17:37, "Dave Hughes" <d...@waveform.plus.com> wrote:
> > WP wrote:
> [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 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
>
> 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.

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!
>
> [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.

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.

permalink · #6 (**permalink**) 05-02-2008, 05:04 AM

On 29 Apr, 21:55, "Dave Hughes" <d...@waveform.plus.com> wrote:
> WP wrote:
> > On 29 Apr, 17:37, "Dave Hughes" <d...@waveform.plus.com> wrote:
> > > WP wrote:
> > [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 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
>
> > 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.
>
> 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.

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

>
> > 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.
>
> 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 :-)

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Get "user" tables