zOs/SQL/CATV9NEX

set current sqlid = 'A540769'
;
delete from plan_table where queryno = 111
;
   explain plan set queryno = 111 for

with tstb as
(
select ts.dbName db, ts.name ts, tb.creator cr, tb.name tb
    from sysibm.systablespace ts
        full outer join sysibm.systables tb
        on ts.dbName = tb.dbName and ts.name = tb.tsName
) select * from tstb
      where (    ( cr = 'GDB9998' and tb = 'TWK573A1' )
              or ( cr = 'GDB9998' and tb = 'TWK574A1' )
              or ( cr = 'GDB9998' and tb = 'TWK575A1' )
              or ( cr = 'GDB9998' and tb = 'TWK576A1' )
              or ( cr = 'GDB9998' and tb = 'TWK135A1' )
              or ( cr = 'GDB9998' and tb = 'TWK145A1' )
            )
;
select * from plan_view5a
    where queryno in ( 111, 222)
    order by applname, progname, queryNo, qblockno, planno
;
rollback;
x
with v9ts as
(
select 'ts' tp, strip(dbName) || '.' || strip(name) nm,
    case when maxPartitions > 0 then 'partitionByGrowth ' else '' end
  || case when partitions > 0 and segSize > 0
        then 'universal ' else '' end
  || case when log <> 'Y' then 'notLogged ' else '' end v9,
  ts.dbName db, ts.name ts
from sysibm.systablespace ts
), v9tb as
(
select 'tb' TP, strip(creator) || '.' || strip(name) nm,
    case when append <> 'N' then 'append ' else '' end v9,
    tb.dbName db, tb.tsName ts, tb.tbCreator cr, tb.tbName tb
    from sysibm.systables tb
), v9cl as
(
select 'cl' TP, strip(tbCreator) || '.' || strip(tbName)
                                 || '.' || strip(name) nm,
      case when hidden <> 'N' then 'hidden ' else '' end
   || case when coltype in('BIGINT', 'BINARY', 'VARBIN', 'DECFLOAT'
          , 'XML') then strip(colType) || ' ' else '' end
   || case when default in ('E','F') then 'onUpdate ' else '' end v9
      , cl.tbCreator cr, cl.tbName tb, cl.name cl
    from sysibm.sysColumns  cl
), v9ix as
(
select 'ix' tp, strip(creator) || '.' || strip(name) nm,
      case when compress <> 'N' then 'compress ' else '' end
  || case when ix_extension_type <>'' then 'expression ' else '' end v9,
    ix.tbCreator cr, ix.tbName tb
    from sysibm.sysIndexes ix
) , tstb as
(
select ts.dbName db, ts.name ts, tb.creator cr, tb.name tb
    from sysibm.systablespace ts
        full outer join sysibm.systables tb
        on ts.dbName = tb.dbName and ts.name = tb.tsName
), v9 (tp, nm, v9, db, ts, cr, tb, cl) as
(
select
    tp, nm, v9, tt.*, ' -- 1 ts'
    from V9ts ts, tstb tt
    where ts.db = tt.db and ts.ts = tt.ts
union all select
    tp, nm, v9, tt.*, ' -- 2 tb'
    from V9tb tb, tstb tt
    where tb.cr = tt.cr and tb.tb = tt.tb
union all select
    tp, nm, v9, tt.*, ' -- 3 ix'
    from V9ix ix, tstb tt
    where ix.cr = tt.cr and ix.tb = tt.tb
union all select
    tp, nm, v9, tt.*, cl.cl
    from V9cl cl, tstb tt
    where cl.cr = tt.cr and cl.tb = tt.tb
)
select tp, char(left(nm, 40), 40), v9, v.*
from v9 v
    where  v9 <> ''
      --and cr = 'GDB9998'
        and (    ( cr = 'GDB9998' and tb = 'TWK573A1' )
              or ( cr = 'GDB9998' and tb = 'TWK574A1' )
              or ( cr = 'GDB9998' and tb = 'TWK575A1' )
            )
    order by cr, tb, cl
;
select * from plan_view5a
    where queryno in ( 111, 222)
    order by applname, progname, queryNo, qblockno, planno
;
rollback;