zOs/SQL/SPCTS

set current path = 'OA1T';
with r as
( select s.dbName db, s.name ts, p.partition part, 1 cPa,
      coalesce(real(r.space),
           case when p.spacef < 0 then 0 else p.spacef end) * 1024 spc,
      real(r.space) * 1024 rtsSpc,
      case when r.space is null then 0 else 1 end rtsSpcCnt,
      case when p.spacef >= 0 then p.spacef else 0 end *1024 paSpc,
      case when p.spacef >= 0 then 1 else 0 end paSpcCnt
    from sysIbm.sysTableSpace s
        left join sysIbm.sysTablePart p
        on p.dbName = s.dbName and p.tsName = s.name
        left join sysIbm.sysTableSpaceStats r
        on r.dbName = s.dbName and r.name = s.name
                               and r.partition = p.partition
            and r.dbId = s.dbid and r.psid = s.psid
    -- where s.dbName like 'MF%'
), t as
(  select db, ts, sum(cPa) cPa,
        sum(spc) spc, sum(rtsSpc) rtsSpc, sum(rtsSpcCnt) rtsSpcCnt,
        sum(paSpc) paSpc, sum(paSpcCnt) paSpcCnt
    from r
    group by db, ts
), u as
(  select * from t
union all select '*' db, strip(char(count(*))) ts, sum(cPa) cPa,
    sum(spc) spc, sum(rtsSpc) rtsSpc, sum(rtsSpcCnt) rtsSpcCnt,
    sum(paSpc) paSpc, sum(paSpcCnt) paSpcCnt
     from t
)
select substr(strip(db) || '.' || strip(ts), 1, 16),
     substr(fosFmte7(spc), 1, 7) spc, cPa,
     substr(fosFmte7(rtsSpc), 1, 7) rtsSpc, rtsSpcCnt,
     substr(fosFmte7(paSpc), 1, 7) paSpc, paSpcCnt
     from u
order by u.spc desc
fetch first 100 rows only
with ur
;
with r as
( select s.dbName db, s.name ts, p.partition part, 1 cPa,
      coalesce(real(r.space),
           case when p.spacef < 0 then 0 else p.spacef end) * 1024 spc,
      real(r.space) * 1024 rtsSpc,
      case when r.space is null then 0 else 1 end rtsSpcCnt,
      case when p.spacef >= 0 then p.spacef else 0 end *1024 paSpc,
      case when p.spacef >= 0 then 1 else 0 end paSpcCnt
     --  sum(real(max(space, 0))*1024) spRts
    from sysIbm.sysTableSpace s
        left join sysIbm.sysTablePart p
        on p.dbName = s.dbName and p.tsName = s.name
        left join sysIbm.sysTableSpaceStats r
        on r.dbName = s.dbName and r.name = s.name
                               and r.partition = p.partition
            and r.dbId = s.dbid and r.psid = s.psid
    -- where s.dbName like 'MF%'
), t as
(  select db, ts, sum(cPa) cPa,
        sum(spc) spc, sum(rtsSpc) rtsSpc, sum(rtsSpcCnt) rtsSpcCnt,
        sum(paSpc) paSpc, sum(paSpcCnt) paSpcCnt
    from r
    group by db, ts
), u as
( select  sum(spc) spc, min(spc) spcMin, max(spc) spcMax,
          count(*) cTs, sum(cPa) cPa
       from t
       group by int(3 * log10(max(1, spc)))
)
select cTs,
    substr(fosFmte7(spc), 1, 7) spc,
    substr(fosFmte7(spcMin), 1, 7) spcMin,
    substr(fosFmte7(spcMax), 1, 7) spcMax,
    cPa
    from u order by u.spcMax
    with ur
;