Results 1 to 11 of 11

Thread: Not Exists

  1. #1
    Join Date
    Jul 2007
    Posts
    34

    Unanswered: Not Exists

    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 ?

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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) ;
    Last edited by umayer; 10-23-07 at 07:33.

  3. #3
    Join Date
    Jul 2007
    Posts
    34
    Thanks but this seems to return all rows that comply to the first select.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jul 2007
    Posts
    34
    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 .

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    SELECT CREATOR, NAME
    FROM   SYSIBM.SYSTABLES A
    WHERE  DBNAME = 'X' AND
           NAME LIKE 'TXXX%' AND
           NOT EXISTS ( SELECT 1
                        FROM   SYSIBM.SYSTABLES B
                        WHERE  DBNAME = 'Y' AND
                               A.NAME = 'CD_' || B.NAME AND
                               A.CREATOR = B.CREATOR )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2003
    Posts
    113
    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.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jul 2007
    Posts
    34
    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 .

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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));
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jul 2007
    Posts
    34
    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 !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •