zOs/SQL/CATSPACE

-- 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 fosfmtE8
set current path = oa1P;
with spTs as
(
select dbName db, sum(max(spaceF, 0) * 1024) sp,
       count(*) objs, sum(max(1, partitions)) parts
    from sysibm.sysTablespace
    group by dbName
) , spTsRts as
(
select dbName db, sum(float(max(space, 0)) * 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(max(spaceF, 0) * 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(max(space, 0)) * 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
) , 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(max(spTs,spTsRts)+max(spIx, spIxRts), 1), sp3.*
    from sp3
)
select char(db, 8)
    , char(fosfmte8(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         db in ('XBDL1001', 'XBDPK001', 'XBDPL001'
--                      ,'XBD2R001', 'XBDM3001', 'XBDL0001')
    order by (max(spTs,spTsRts)+max(spIx, spIxRts)) desc
    fetch first 21 rows only
;