zOs/SQL/DDLCH9

set current sqlid = 'S100447';
set current path oa1t;
 -- drop                    table A540769.tSels;
    create global temporary table A540769.tSels
           (ty char(3), qu varchar(128), nm varchar(128), gp char(3))
;
insert into A540769.tSels
     select 'ts', dbName, name, ''
          from sysibm.systablespace
          where dbName like 'NI%'
/*?? select 'ts', dbName, name, ''
          from sysibm.systablespace
          where dbName = 'DGDB9998' and name like 'A40%'
     union all select 't', creator, name, ''
          from sysibm.systables
          where creator = 'OA1A' and name like 'TMF150%' and type = 'T'
     union all select 'v', creator, name, ''
          from sysibm.systables
          where creator = 'GDB9998' and name like 'WK947NU%'
                   and type = 'V'
??*/
;
with ts as
(
  select 'ts' ty, dbName qu, name nm
       , 'ts:' || strip(dbName) || '.' || strip(name) pa
       , ts.*
    from sysibm.sysTablespace ts join A540769.tSels s
      on s.ty = 'ts' and s.qu = ts.dbName and s.nm = ts.name
  union all select 'ts' ty, ts.dbName qu, ts.name nm
       , 't:' || strip(t.creator) || '.' || strip(t.name)
        || 'ts:' || strip(t.dbName) || '.' || strip(t.tsname) pa
       , ts.*
    from sysibm.sysTables t join A540769.tSels s
        on s.ty = 't' and s.qu = t.creator and s.nm = t.name
      join sysibm.sysTablespace ts
        on ts.dbName = t.dbname and ts.name = t.tsname
)
, rt as
(
  select ty, qu, nm, min(pa) pa
      , real(sum(totalRows)) rows
      ,  sum(ts.pgSize * real(r.nActive)) spaceKB
    from ts left join sysibm.sysTablespaceStats r
      on r.dbName = ts.dbName and r.name = ts.name
          and r.dbid = ts.dbid and r.psid = ts.psid
    group by ty, qu, nm
)
, t as
(
  select 't' ty, t.creator qu, t.name nm
       , pa || ' t:' || strip(t.creator) || '.' || strip(t.name) pa
       , t.*
    from ts join sysibm.sysTables t
      on t.dbName = ts.qu and t.tsName = ts.nm
        and t.type not in ('A', 'V')
)
, i as
(
  select 'i' ty, ix.creator qu, ix.name nm
       , pa || ' i:' || strip(ix.creator) ||'.'|| strip(ix.name) pa
       , ix.*
    from t join sysibm.sysIndexes ix
      on qu = ix.tbCreator and nm = ix.tbName
  union all select 'i' ty, ix.creator qu, ix.name nm
       , 'i:' || strip(creator) || '.' || strip(name) pa
       , ix.*
    from sysibm.sysIndexes ix join A540769.tSels s
      on s.ty = 'i' and ix.creator = s.qu and ix.name = s.nm
)
, it as
(
  select ty, qu, nm, min(pa) pa
      , real(sum(totalEntries)) rows
      ,  sum(case when i.pgSize = 4096 then 4
                                       else i.pgSize end
              * real(r.nActive)) spaceKB
    from i left join sysibm.sysIndexspaceStats r
      on r.creator= i.creator and r.name = i.name
          and r.dbid = i.dbid and r.isobid = i.isobid
    group by ty, qu, nm
)
, v1 (ty, qu, nm, gp, tCr, pa, lv) as
(
  select ty, qu, nm, gp, creator, pa, 0
    from t
  union all select s.*, '', 'v:' || strip(s.qu) ||'.'|| strip(s.nm), 0
       from A540769.tSels s where ty = 'v'
  union all select dType, dCreator, dName, gp, tCr
       , case when length(pa) < 70 then pa
                                   else left(pa, 65) || '.....' end
         || ' v:' || strip(dCreator) || '.' || strip(dName)
       , lv+1
    from v1 join sysibm.sysViewDep d
      on d.bCreator = v1.qu and d.bName = v1.nm
    where lv < 100
)
, v  as
( select 'v' ty, qu, nm, gp, tCr, pa
    from v1
    where ty in('v', 'V')
)
, u as
(
    select             ty, qu, nm, rows, spaceKb, pa  from rt
    union all select   ty, qu, nm, rows, spaceKb, pa  from it
 -- union all select             ty, qu, nm, pa  from t
)
select ty, substr(qu, 1, 12) qu, substr(nm, 1, 16) nm
    ,  fosFmtE7(rows) rows
    ,  fosFmtE7(spaceKB) spaceKB
    , pa
    from U
    order by pa, ty
;x;
-- select * from r order by pa ;x;
select count(*), ty, cat, min(err), max(err)
                        , min(pa), max(pa)
    from r
    group by ty, cat, left(err, 10)
    order by 3, 2, 4
;
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by --collid, progName, applName, explain_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;
rollback
;x;
 -- where db = 'DGDB9998' -- and ts like 'A97%'
    order by pa, err
;
select ty, substr(qu, 1, 12) qu, substr(nm, 1, 16) nm,
      substr(err, 1, 1) cat, substr(err, 2, 40) err, pa
    from eU
    where db like 'MF%' -- and ts like 'A97%'
 -- where db = 'DGDB9998' -- and ts like 'A97%'
    order by pa, err
;
select * from v
    where db like 'VV24%' and ts like 'VDPS168%'
    order by qu, nm, pa
;x;