zOs/SQL/CATV9NEV

set current sqlid = 'S100447'
;
drop   view s100447.db2v9ts ;
drop   view s100447.db2v9tb ;
drop   view s100447.db2v9cl ;
drop   view s100447.db2v9ix ;
-- drop   view s100447.db2ixPart;
-- drop   view s100447.db2v9 ;

create view s100447.db2v9ts 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.*
from sysibm.systablespace ts
;
create view s100447.db2v9tb as
select 'tb' TP, strip(creator) || '.' || strip(name) nm,
    case when append <> 'N' then 'append ' else '' end v9,
    tb.*
    from sysibm.systables tb
;
create view s100447.db2v9cl 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.*
    from sysibm.sysColumns  cl
;
create view s100447.db2v9ix 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.*
    from sysibm.sysIndexes ix
;
create view s100447.db2ixPart as
select 'tb' TP, strip(t.creator) || '.' || strip(t.name) nm,
    case when s.partitions > 0 and t.partKeyCOLNUM < 1
         then 'indexBased partitioning' else '' end v9,
    t.*
    from sysibm.systables t, sysibm.sysTablespace s
    where t.dbName = s.dbName and t.tsName = s.name
        and t.type = 'T'
;
create view s100447.db2v9   (tp, nm, v9, cr, tb, cl) as
select
    tp, nm, v9, tb.creator, tb.name, ' -- 1 ts'
    from s100447.db2V9ts ts join sysibm.sysTables tb
    on ts.dbName = tb.dbName and ts.name = tb.tsName
union all select
    tp, nm, v9, creator, name, ' -- 2 tb'
    from s100447.db2V9tb tb
union all select
    tp, nm, v9, ix.tbCreator,ix.tbName, ' -- 3 ix'
    from s100447.db2V9ix ix
union all select
    tp, nm, v9, cl.tbCreator, cl.tbName, cl.name
    from s100447.db2V9cl cl
union all select
    tp, nm, v9, creator, name, ' -- 4 ip'
    from s100447.db2ixPart
;
commit
;
select tp, char(left(nm, 40), 40), v9, v.*
from s100447.db2v9 v
    where v9 <> ''  and cr in ('GDB9998', 'A540769')
    order by cr, tb, cl
;