Both databases X and Y should have the same tables but SYSIBM.SYSTABLES shows thats not the case

Database X : 1102 tables TXXXXXX
Database Y : 1100 tables TXXXXXX

When trying to find out with NOT EXISTS, I always get 0 rows ?

SELECT NAME FROM SYSIBM.SYSTABLES WHERE DBNAME =
'X' AND NAME LIKE 'TXXX%' AND NOT EXISTS
(SELECT NAME FROM SYSIBM.SYSTABLES WHERE DBNAME = 'Y'
AND NAME LIKE 'TXXX%' );

What am I doing wrong ?

as soon as there is one table like "TXXX%' in database 'Y', your NOT EXISTS condition returns "FALSE" and you get no row.

Try a correlated subquery:


SELECT NAME FROM SYSIBM.SYSTABLES A WHERE DBNAME =
'X' AND NAME LIKE 'TXXX%' AND NOT EXISTS
(SELECT * FROM SYSIBM.SYSTABLES B WHERE DBNAME = 'Y'
AND A.NAME = B.NAME);


// edit:

As only the combiation of creator + name is unique, you might have to extend your query:

SELECT CREATOR , NAME FROM SYSIBM.SYSTABLES A WHERE DBNAME =
'X' AND NAME LIKE 'TXXX%' AND NOT EXISTS
(SELECT * FROM SYSIBM.SYSTABLES B WHERE DBNAME = 'Y'
AND A.NAME = B.NAME AND A.CREATOR = B.CREATOR) ;

Thanks but this seems to return all rows that comply to the first select.

Maybe the tables have different schema names (aka creator)? I'm not completely sure, but you cannot have two tables with the same fully qualified name in different databases of the same DB2 subsystem. At least, I haven't found anything. (And a "database" is just a qualifier for tablespaces.)

Maybe you want to make sure that the same tables exist in two different schemas?

I've got 2 databases which should have the same tables with the exception that the tables in one database use a prefix cd_% and the 2 db's have a different table creator ...


database X database Y
table abcdefg = table cd_abcdefg
table hijklmno = table cd_hijklmno

so by using substr for tables of database Y, should try to find a way to see which tables exist in database X but not in database Y and other way round by quering sysibm.systables .

I still don't understand how exactly the relationship of the schemas (creators) shall be because your description is not clear. A slight modification of umayer's query gives you the matching table name:

just a thought.

It is possible that the database contains a couple (internal created) aux tables for LOB or XML column?

For example,
CREATE TABLE TT(C1 XML, C2 CLOB)

db2 will create aux tables to hold XML(v9 only) and CLOB data. The table names are internal generated which may not be fitted in the queries.

I think this question refers to DB2 for z/OS. The reason I say that is in DB2 for z/OS you can have multiple databases in one subsystem (and both referenced in one SQL statement). A database in DB2 z/OS is a totally different meaning than a database in DB2 for LUW.

Its indeed db2 (ver 8) on z/OS .

There is a subsystem that contains 2 databases with the same tables .
The only difference are both database names (of course) and the table names which are prefixed with CD_ (the rest of the name is similar) in one database . (so table1 compared to CD_table1)
The purpose of the script is to run a compare (using sysibm.systables)
to find out if there are tables in one database missing in the other .

Thought of something similar to the 'minus' predicate in the oracle world .Found somewhere on a forum the 'except' predicate but that doesn't exist .

Select A.creator, A.name
From Sysibm.systables A
Where A.dbname = 'x' And
Substr(a.name, 1, 4) = 'txxx' And
Not Exists ( Select 1
From Sysibm.systables B
Where B.dbname = 'y' And
Substr(b.name, 1, 3) = 'cd_' And
Substr(b.name, 4, 125) = A.name);

Select A.creator, A.name
From Sysibm.systables A
Where A.dbname = 'y' And
Substr(a.name, 1, 3) = 'cd_' And
Substr(a.name, 4, 4) = 'txxx' And
Not Exists ( Select 1
From Sysibm.systables B
Where B.dbname = 'x' And
B.name = Substr(a.name,4,125));

Thanks a lot to all, it works as a correlated subquery . By the way, I tested the first reply from umayer with some little changes and it works also, his reply was also good , seems that I made some error .

Thanks !