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

[Lennart]

On Apr 29, 5:19 pm, WP <mindcoo...@gma il.comwrote:

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

[Dave Hughes]

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.

[WP]

On 29 Apr, 17:37, "Dave Hughes" <d...@waveform. plus.comwrote:

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

[Dave Hughes]

WP wrote:

On 29 Apr, 17:37, "Dave Hughes" <d...@waveform. plus.comwrote:

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.

[WP]

On 29 Apr, 21:55, "Dave Hughes" <d...@waveform. plus.comwrote:

WP wrote:

On 29 Apr, 17:37, "Dave Hughes" <d...@waveform. plus.comwrote:

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