zOs/SQL/CATV9NEW

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

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
), v9 (tp, nm, v9, cr, tb, cl) as
(
select
    tp, nm, v9,                 cr     , tb  , ' -- 2 tb'
    from            V9tb tb
union     select
    tp, nm, v9, ix.cr       ,ix.tb,
    ' -- 3 ix'
    from            V9ix ix -- ysibm.sysTables tb
 --   where ix.tbCreator = tb.creator and ix.tbName = tb.name
union     select
    tp, nm, v9, cl.cr       , cl.tb    , cl.cl
    from            V9cl cl -- , sysibm.sysTables tb
 --   where cl.tbCreator = tb.creator and cl.tbName = tb.name
)
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' )
                    or ( cr = 'GDB9998' and tb = 'TWK576A1' )
                    or ( cr = 'GDB9998' and tb = 'TWK135A1' )
                    or ( cr = 'GDB9998' and tb = 'TWK145A1' )
                  )
    order by cr, tb, cl
;
select * from plan_view5a
    where queryno in ( 111, 222)
    order by applname, progname, queryNo, qblockno, planno
;
commit;
    tp, nm, v9,
        ifNull(tb.creator, ''), ifNull(tb.name, ''), ' -- 1 ts'
    from            V9ts ts left join sysibm.sysTables tb
    on ts.db     = tb.dbName and ts.ts = tb.tsName
union all select