DB2 Z/OS : Find missing data in table

I have a list of tables and I want to find out the missing tables in db2 Z/OS.

I can think of using CTE with hardcoded values(UNION ALL) and then performing a LEFT OUTER JOIN on catalog table.

Not sure how to hardcode the values in CTE. Is there any other faster way to query the existence of tables as I have a huge list of tables that needs to be checked.

Thank You.

1 answer

  • answered 2018-11-08 08:17 Mark Barinstein

    Load your huge list into some intermediate table, let's say with (CREATOR VARCHAR(128), NAME VARCHAR(128)) fields. As for hardcoded values. Try this:

    select v.*
    from (
    select 'SYSIBM', 'SYSTABLES' from sysibm.sysdummy1
      union all
    select 'SYSIBM', 'SYSCOLUMNSS' from sysibm.sysdummy1
    -- union all
    --select ...
    ) v (creator, name)
    where not exists (
    select 1 
    from sysibm.systables t 
    where t.creator=v.creator and t.name=v.name
    );
    

    You can use some text processing utilities like sed to produce the strings like:

    select 'SYSIBM', 'SYSTABLES' from sysibm.sysdummy1 union all
    

    from those found in your input file:

    SYSIBM,SYSTABLES
    

    You can do some minimal changes to the results of such a processing to construct the final statement like above. But it's not for really huge number of tables, of course...