zOs/SQL/CATSPAIX

with i as
(
select creator cr, name ix, tbCreator tbCr, tbName tb,
        sum(i.spaceF * 1024) sp,
        sum(real(i.firstKeyCard)) fiKC,
        sum(real(i.fullKeyCard)) fuKC,
        avg(avgKeyLen) avgKey
    from sysibm.sysIndexes i
    group by creator, name, tbCreator, tbName
)
, ip as
(
select ixcreator cr, ixname ix,
        sum(spaceF * 1024) sp,
        sum(cardF) card,
        avg(avgKeyLen) AvgKey
    from sysibm.sysIndexPart
    group by ixcreator, ixname
)
, ir as
(
select creator cr, name ix,
        sum(real(space) * 1024) sp,
        sum(real(totalEntries)) card
    from sysibm.sysIndexSpaceStats
    group by creator, name
)
select substr(i.tbCr, 1, 6) tbCr
     , substr(i.tb  , 1, 12) tbCr
     , substr(i.cr  , 1, 6) Cr
     , substr(i.ix  , 1, 16) ix
     , i.sp, ip.sp ipSp, ir.sp irSp
           , ip.card ipCard, ir.card irCard
     , i.fiKC, i.fuKC
     , i.avgKey iAvgKey, ip.avgKey ipAvgKey
from i
   left join ip on i.cr = ip.cr and i.ix = ip.ix
   left join ir on i.cr = ir.cr and i.ix = ir.ix
   where tbCr = 'OA1A' and tb = 'TMF150A1'
;;;;  x;
    where tbName =
    group by t.dbName, t.tsName
) , ixR as
--- Space Auswertung für einzelne Objekte einer DB
--     zeigt das Total und die 20 grössten DBs
--       index und TS separiert
--       runstats und RTS Daten
--       tot ist die Summe der Maxima(runstats, RTS) von TS und IX
--       % Zahlen geben TS-Run, TS-RTS, IX-Run und IX-RTS in% von tot
--
--     requires fosfmtE8
set current path = oa1p;
with ts as
(
select dbName db, name ts, sum(spaceF * 1024) sp,
       count(*) objs, sum(max(1, partitions)) parts
    from sysibm.sysTablespace
    group by dbName, name
) , tsR as
(
select dbName db, name ts, sum(float(space) * 1024) sp,
           sum(case when partition <= 1 then 1 else 0 end) objs,
           count(*) parts
    from sysibm.sysTablespaceStats
    group by dbName, name
) , ix as
(
select t.dbName db, t.tsName ts,
        sum(i.spaceF * 1024) sp,
           count(*) objs,  case when 1=1 then null else 1 end parts
    from sysibm.sysIndexes i
        join sysibm.sysTables t
          on t.creator = i.tbCreator and t.name = i.tbName
    group by t.dbName, t.tsName
) , ixR as
(
select t.dbName db, t.tsName ts,
      sum(float(r.space) * 1024) sp,
           sum(case when r.partition <= 1 then 1 else 0 end) objs,
           count(*) parts
    from sysibm.sysIndexspaceStats r
        join sysibm.sysIndexes i
          on r.creator = i.creator and r.name = i.name
        join sysibm.sysTables t
          on t.creator = i.tbCreator and t.name = i.tbName
    group by t.dbName, t.tsName
) , sp1 (db, ts, spTs, spTsRts, tsObjs, tsParts,
                 spIx, spIxRts, ixObjs, ixParts) as
(
select ts.db, ts.ts,
    ts.sp spTs, coalesce(tsR.sp, 0), ts.objs, ts.parts,
    coalesce(ix.sp, 0), coalesce(ixR.sp, 0),
    coalesce(ix.objs, 0), coalesce(ixR.parts, 0)
    from ts
       left join tsR
           on ts.db = TsR.db and ts.ts = tsR.ts
       left join ix
           on ts.db = ix.db and ts.ts = ix.ts
       left join ixR
           on ts.db = ixR.db and ts.ts = ixR.ts
    where ts.db =    'DCMN'
) , sp2 (db, ts, spTs, spTsRts, tsObjs, tsParts,
                 spIx, spIxRts, ixObjs, ixParts) as
(
select * from sp1
union  all select '*', '*'
            , sum(spTs), sum(spTsRts), sum(tsObjs), sum(tsParts)
            , sum(spIx), sum(spIxRts), sum(ixObjs), sum(ixParts)
    from sp1
) , sp as
(
select max(spTs,spTsRts)+max(spIx, spIxRts) sp, sp2.*
    from sp2
)
select char(db, 8) db, char(ts, 8) ts
    , char(fosfmte7(sp), 8) "tot"
        , dec(tsObjs, 7, 0) "tsObjs", dec(tsParts, 7, 0) "tsParts"
        , dec(ixObjs, 7, 0) "ixObjs", dec(ixParts, 7, 0) "ixParts"
    , right('   '||strip(char(int(round(spTs/sp*100   , 0)))), 4)
    ||right('   '||strip(char(int(round(spTsRts/sp*100, 0)))), 4)
    ||right('   '||strip(char(int(round(spIx/sp*100   , 0)))), 4)
    ||right('   '||strip(char(int(round(spIxRts/sp*100, 0)))), 4)
           "% TS rts  IX rts"
    , dec(spTsRts/sp*100, 3, 0) "tsRts%"
    , dec(spIx/sp*100, 3, 0) "ixRun%"
    , dec(spIxRts/sp*100, 3, 0) "ixRts%"
    from sp
    order by sp desc
    fetch first 21 rows only
;