zOs/SQL/CATSPACS

-- Space Auswertung für ein ganzes Subsystem:
--     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 fmtE8
set current path = oa1x;
with sp as
(
select s.dbName db, s.name ts,  max(s.spaceF, 0) * 1024 spS
          ,  1 obS, max(1, s.partitions) paS
          ,  r.spR, r.obR, r.paR
          ,  i.spI, i.obI
          ,  i.spJ, i.obJ, i.paJ
    from sysibm.sysTablespace s
    left join
      ( select dbName db, name ts, sum(float(space) * 1024) spR,
                 1 obR, count(*) paR, dbid, psid
            from sysibm.sysTablespaceStats
            group by dbid, psId, dbName, name
      ) r
    on s.dbid = r.dbId and s.psid = r.psid
           and s.dbName = r.db and s.name = r.ts
    left join
      ( select t.dbName db, t.tsName ts, count(*) obI
                , sum(i.spacef * 1024) spI
                , sum(spJ) spJ, sum(obJ) obJ, sum(paJ) paJ
            from sysibm.sysTables t
                join sysibm.sysIndexes i
                on t.creator = i.tbCreator and t.name = i.tbName
                    and t.dbName = i.dbName
                left join
                  (  select creator, name -- , dbid, isobid,
                          , sum(float(space) * 1024) spJ
                          , 1 obJ, count(*) paJ
                         from sysibm.sysIndexSpaceStats
                         group by creator, name
                  ) j
                on i.creator = j.creator and i.name = j.name
            group by t.dbName, t.tsName
      ) i
    on s.dbName = i.db and s.name = i.ts
)
select * from sp where db like 'DGDB9998%'
;
) , spTsRts as
(
select dbName db, sum(float(space) * 1024) sp,
           sum(case when partition <= 1 then 1 else 0 end) objs,
           count(*) parts
    from sysibm.sysTablespaceStats
    group by dbName
) , spIx as
(
select dbName db, sum(spaceF * 1024) sp,
           count(*) objs,  case when 1=1 then null else 1    end   parts
    from sysibm.sysIndexes
    group by dbName
) , spIxRts as
(
select dbName db, sum(float(space) * 1024) sp,
           sum(case when partition <= 1 then 1 else 0 end) objs,
           count(*) parts
    from sysibm.sysIndexspaceStats
    group by dbName
) , sp2 (db, spTs, spTsRts, tsObjs, tsParts,
             spIx, spIxRts, ixObjs, ixParts) as
(
select spTs.db,
    spTs.sp spTs, coalesce(spTsRts.sp, 0) spTsRts,
        spTs.objs tsObjs, spTs.parts tsParts,
    coalesce(spIx.sp, 0) spIx, coalesce(spIxRts.sp, 0) spIxRts,
        coalesce(spIx.objs, 0) ixObjs, coalesce(spIxRts.parts, 0)ixParts
    from spTs
       left join spTsRts
           on spTs.db = spTsRts.db
       left join spIx
           on spTs.db = spIx.db
       left join spIxRts
           on spTs.db = spIxRts.db
    where    spTs.db like 'DGDB9998%'
) , sp3 (db, spTs, spTsRts, tsObjs, tsParts,
             spIx, spIxRts, ixObjs, ixParts) as
(
select * from sp2
union  all select
       ' * ', sum(spTs), sum(spTsRts), sum(tsObjs), sum(tsParts)
            , sum(spIx), sum(spIxRts), sum(ixObjs), sum(ixParts)
    from sp2
) , sp  (sp, db, spTs, spTsRts, tsObjs, tsParts,
                 spIx, spIxRts, ixObjs, ixParts) as
(
select max(spTs,spTsRts)+max(spIx, spIxRts), sp3.*
    from sp3
)
select char(db, 8)
    , char(fmte8(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
    where sp >= 1
    and   db     like 'DGDB9998%'
    order by (max(spTs,spTsRts)+max(spIx, spIxRts)) desc
    fetch first 21 rows only
;
with sp (db, ty, sp, objs, parts) as