zOs/SQL/PLANTS

WITH pt AS
( select *
    from sysibm.sysTables o
    where type = 'T' and name in ('COST_TABLE'
                          ,'DSN_COLDIST_TABLE'
                          ,'DSN_DETCOST_TABLE'
                          ,'DSN_FILTER_TABLE'
                          ,'DSN_FUNCTION_TABLE'
                          ,'DSN_KEYTGTDIST_TABLE'
                          ,'DSN_PGRANGE_TABLE'
                          ,'DSN_PGROUP_TABLE'
                          ,'DSN_PREDICAT_TABLE'
                          ,'DSN_PTASK_TABLE'
                          ,'DSN_QUERY_AUX'
                          ,'DSN_QUERY_TABLE'
                          ,'DSN_SORTKEY_TABLE'
                          ,'DSN_SORT_TABLE'
                          ,'DSN_STATEMENT_CACHE_AUX'
                          ,'DSN_STATEMENT_CACHE_TABLE'
                          ,'DSN_STATEMNT_TABLE'
                          ,'DSN_STRUCT_TABLE'
                          ,'DSN_VIEWREF_TABLE'
                          ,'DSN_VIRTUAL_INDEXES'
                          ,'EEEAUTH'
                          ,'EEEDBRM'
                          ,'EEEHINT'
                          ,'EEEPATH'
                          ,'EEEPLAN'
                          ,'EEEWORK'
                          ,'OBJECT_DATA'
                          ,'OBJECT_DIRECTORY'
                          ,'F2PLAN_TABLE'
                          ,'PLAN_TABLE'
                          ,'PREDICATE_TABLE'
                          ,'STRUCTURE_TABLE'
                          )
)
, ts as
( select creator cr, dbName db, tsName ts, count(*) cnt
    from pt
    group by creator, dbName, tsName
)
select ts.*
    ,  (select strip(o.creator) || '.' || strip(o.name)
            from sysibm.sysTables o
            where o.dbName = ts.db and o.tsName = ts.ts
                and o.type = 'T'
                and not exists (select 1
                     from pt
                     where pt.creator = o.creator and pt.name = o.name)
       )
    from ts where cr      = 'A540769'
;;;
select * -- bCreator, bName, bType, 1
    from sysibm.sysViewDep
    where bType = 'V'
          and dcreator = 'A540769' and dName like 'PLAN%'
;;;
WITH S AS
(
select (select strip(o.creator) || '.' || strip(o.name)
            from sysibm.sysTables o
            where o.dbName = t.dbName and o.tsName = t.tsName
                and o.type = 'T'
                and not (o.creator = t.creator
            and o.name in ('COST_TABLE'
                          ,'DSN_COLDIST_TABLE'
                          ,'DSN_DETCOST_TABLE'
                          ,'DSN_FILTER_TABLE'
                          ,'DSN_FUNCTION_TABLE'
                          ,'DSN_KEYTGTDIST_TABLE'
                          ,'DSN_PGRANGE_TABLE'
                          ,'DSN_PGROUP_TABLE'
                          ,'DSN_PREDICAT_TABLE'
                          ,'DSN_PTASK_TABLE'
                          ,'DSN_QUERY_AUX'
                          ,'DSN_QUERY_TABLE'
                          ,'DSN_SORTKEY_TABLE'
                          ,'DSN_SORT_TABLE'
                          ,'DSN_STATEMENT_CACHE_AUX'
                          ,'DSN_STATEMENT_CACHE_TABLE'
                          ,'DSN_STATEMNT_TABLE'
                          ,'DSN_STRUCT_TABLE'
                          ,'DSN_VIEWREF_TABLE'
                          ,'DSN_VIRTUAL_INDEXES'
                          ,'EEEAUTH'
                          ,'EEEDBRM'
                          ,'EEEHINT'
                          ,'EEEPATH'
                          ,'EEEPLAN'
                          ,'EEEWORK'
                          ,'OBJECT_DATA'
                          ,'OBJECT_DIRECTORY'
                          ,'F2PLAN_TABLE'
                          ,'PLAN_TABLE'
                          ,'PREDICATE_TABLE'
                          ,'STRUCTURE_TABLE'
                       )  )
            fetch first 1 row only
       ) otTb, t.*
    from sysibm.sysTables t
    where creator like '%' and type = 'T'
    and name in ('COST_TABLE'
                ,'DSN_COLDIST_TABLE'
                ,'DSN_DETCOST_TABLE'
                ,'DSN_FILTER_TABLE'
                ,'DSN_FUNCTION_TABLE'
                ,'DSN_KEYTGTDIST_TABLE'
                ,'DSN_PGRANGE_TABLE'
                ,'DSN_PGROUP_TABLE'
                ,'DSN_PREDICAT_TABLE'
                ,'DSN_PTASK_TABLE'
                ,'DSN_QUERY_AUX'
                ,'DSN_QUERY_TABLE'
                ,'DSN_SORTKEY_TABLE'
                ,'DSN_SORT_TABLE'
                ,'DSN_STATEMENT_CACHE_AUX'
                ,'DSN_STATEMENT_CACHE_TABLE'
                ,'DSN_STATEMNT_TABLE'
                ,'DSN_STRUCT_TABLE'
                ,'DSN_VIEWREF_TABLE'
                ,'DSN_VIRTUAL_INDEXES'
                ,'EEEAUTH'
                ,'EEEDBRM'
                ,'EEEHINT'
                ,'EEEPATH'
                ,'EEEPLAN'
                ,'EEEWORK'
                ,'OBJECT_DATA'
                ,'OBJECT_DIRECTORY'
                ,'F2PLAN_TABLE'
                ,'PLAN_TABLE'
                ,'PREDICATE_TABLE'
                ,'STRUCTURE_TABLE'
                )
)
select distinct otTb from s
  --     where otTb is not null