zOs/SQL/RTSSPACE

with sp (ty, db, ts, alloc, act , #ts, #pa) as
(   select 'tsCat', dbname, name ,
            spaceF * 1024,
            real(nActive) * pgSize* 1024,
            1,
            case when partitions = 0 then 1 else partitions end
        from sysibm.sysTableSpace
    union ( select 'tsRts', r.dbname, r.name ,
            sum(real(r.space) * 1024),
            sum(real(r.nActive) * s.pgSize * 1024),
            1,
            count(*)
        from sysibm.TableSpaceStats r , sysibm.sysTableSpace s
            where r.dbid = s.dbid  and r.psid = s.psid
                    and r.dbName= s.dbName  and r.name = s.name
            group by r.dbName, r.name
           )
    union ( select 'ixCat', i.dbname, i.indexSpace,
            max(i.spaceF * 1024),
            max(real(i.nLeaf) * i.pgSize),
            1,
            count(*)
        from sysIbm.sysIndexes i, sysIbm.sysIndexPart p
                where p.ixCreator = i.creator and p.ixName = i.name
            group by i.dbName, i.indexSpace -- ,i.creator, i.name

          )
    union ( select 'ixRts', r.dbname, r.indexSpace ,
            sum(real(r.space) * 1024),
            sum(real(r.nActive) * i.pgSize),
            1,
            count(*)
        from sysibm.IndexSpaceStats r , sysibm.sysIndexes i
            where r.dbid = i.dbid  and r.isobid = i.isobid
                    and r.dbName=i.dbName and r.indexSpace=i.indexSpace
            group by r.dbName, r.indexSpace
           )
)
select ty, db,
     sum(alloc) alloc, sum(act) act, sum(#ts) #ts, sum(#pa) #pa
    from sp
--  where db like 'MF%'
    group by ty, db
    order by 4 desc
;
            max(s.spaceF * 1024) cSpa,
            sum(real(r.space) * 1024) rSpa,
            sum(real(r.nActive) * s.pgSize * 1024) rAct
        from sysibm.tablespacestats r, sysibm.sysTablespace s
        where r.dbid = s.dbid  and r.psid = s.psid
             and r.dbName= s.dbName  and r.name = s.name
          group by s.dbid, s.psid, s.dbname, s.name
)
with tsDet as
(   select s.dbname db, s.name ts,
            max(real(s.nActive) * s.pgSize* 1024) cAct,
            max(s.spaceF * 1024) cSpa,
            sum(real(r.space) * 1024) rSpa,
            sum(real(r.nActive) * s.pgSize * 1024) rAct
        from sysibm.tablespacestats r, sysibm.sysTablespace s
        where r.dbid = s.dbid  and r.psid = s.psid
             and r.dbName= s.dbName  and r.name = s.name
          group by s.dbid, s.psid, s.dbname, s.name
)
select sum(cAct), sum(cSpa), sum(rSpa), sum(rAct) from tsDet;
select * from tsDet; -- where db like 'MF01%'
;
x
with ixDet as
(   select  i.creator cr, i.name ix, i.dbName db,
            sum(max(real(nLeaf) * i.pgSize * 1024, 0)) cLeaf,
            sum(max(i.spacef * 1024, 0)) cSpa,
            sum(real(s.space) * 1024) rSpa,
            sum(real(s.nActive) * i.pgSize * 1024) rAct
        from sysibm.sysindexes i
            left join sysibm.indexSpaceStats s
            on s.dbid = i.dbid  and s.isobid = i.isobid
               and s.dbName = i.dbName and s.indexSpace = i.indexSpace
        group by i.creator, i.name, i.dbName, i.dbId, i.isobid,
                 i.indexSpace
)
select * from ixDet where db like 'MF01%'
;
select sum(cLeaf), sum(cSpa), sum(rSpa) rSpa, sum(rAct)
         from ixDet where db like 'MF01%'
;
x
select count(*) from
(
select i.creator, i.name, i.spacef, sum(p.spacef) from
    sysibm.sysIndexes i, sysIbm.sysIndexPart p
    where i.creator = p.ixCreator and i.name = p.ixName
        and i.dbName like '%'
    group by i.creator, i.name, i.spacef
    having i.spacef = sum(p.spacef)
         or (i.spacef < 0 and sum(p.spacef) < 0)
) x
;
select i.creator, i.name, i.spacef, sum(p.spacef) from
    sysibm.sysIndexes i, sysIbm.sysIndexPart p
    where i.creator = p.ixCreator and i.name = p.ixName
        and i.dbName like '%'
    group by i.creator, i.name, i.spacef
    having i.spacef <> sum(p.spacef) and
           (i.spacef >= 0 or sum(p.spacef) >= 0)
;
X
    select  i.creator, i.name, i.dbname, i.indexspace, p.partition,
            sum(real(s.space)) rSpa, count(s.space) rSpaCnt,
            sum(real(s.nActive) * i.pgSize * 1024) rAct,
                                     , count(s.nActive) rActCnt,
             , 0,
            1, sum(s.totalEntries),
            count(*), sum(s.extents)
        from sysibm.indexspacestats s, sysibm.sysIndexes i
        where s.dbid = i.dbid  and s.isobid = i.isobid
        group by s.dbid, s.isobid, s.dbName, s.indexSpace
with st (db, name, alloc, active, ts, rows, ix, entries, part, ext) as
(   select  s.dbname, s.indexspace,
            sum(s.space), sum(s.nActive * i.pgSize / 1024),
            0, 0,
            1, sum(s.totalEntries),
            count(*), sum(s.extents)
        from sysibm.indexspacestats s, sysibm.sysIndexes i
        where s.dbid = i.dbid  and s.isobid = i.isobid
        group by s.dbid, s.isobid, s.dbName, s.indexSpace
    union select s.dbname, s.name,
            sum(s.space), sum(s.nActive * p.pgSize),
            1, sum(s.totalRows),
            0, 0,
            count(*), sum(s.extents)
        from sysibm.tablespacestats s, sysibm.sysTablespace p
        where s.dbid = p.dbid  and s.psid = p.psid
        group by s.dbid, s.psid, s.dbname, s.name
)
select  db,
        sum(alloc) "alloc kb", sum(active) "active kb",
        sum(ts) "ts #", sum(rows) "ts rows",
        sum(ix) "ix #", sum(entries) "ix entries",
        sum(part) "partitions", sum(ext) "extents"
    from st
 -- where db like 'WL%'
    where db = 'DA540769' and(name like 'IWK001A%' or name = 'A001A')
    group by db
    with ur
;
x
select s.dbname,
       s.name,
  --     s.partition,
       count(*) "total part",
       sum(s.totalRows) "rows",
       sum(s.nActive * p.pgSize) "active kb",
       sum(s.space) "alloc kb"
       -- updateStatsTime , strip(p.dbName) || '.' || p.name
    from sysibm.tablespacestats s, sysibm.sysTablespace p
    where s.dbid = p.dbid  and s.psid = p.psid
           AND s.dbNAME = 'DGDB9998'
    group by s.dbname, s.name
    with ur
;
select s.dbname,
       s.indexspace,
  --     s.partition,
       count(*) "total part",
       sum(s.totalEntries) "entries",
       sum(s.nActive * i.pgSize / 1024) "active kb",
       sum(s.space) "alloc kb",
       sum(s.extents) "extents"
       -- updateStatsTime , strip(p.dbName) || '.' || p.name
    from sysibm.indexspacestats s, sysibm.sysIndexes i
    where s.dbid = i.dbid  and s.isobid = i.isobid
           AND s.dbNAME = 'DGDB9998'
    group by s.dbname, s.indexspace
    with ur
;