zOs/SQL/SPCTPA

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
     --  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%'
  --    group by s.dbName, s.name
), t as
(  select count(*) cnt,
          sum(spc) spc, min(spc) spcMin, max(spc) spcMax,
          sum(cPa) cPa
       from r
       group by int(3 * log10(max(1, spc)))
)
select cnt,
    substr(fosFmte7(spc), 1, 7) spc,
    substr(fosFmte7(spcMin), 1, 7) spcMin,
    substr(fosFmte7(spcMax), 1, 7) spcMax,
    cPa
    from t order by t.spcMax
    with ur
;x;
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 'DA54076%'
  --    group by s.dbName, s.name
), t as
(
select * from r
union all select '*' db, '*' ts, 0 part, sum(cPa) cPa,
    sum(spc) spc, sum(rtsSpc) rtsSpc, sum(rtsSpcCnt) rtsSpcCnt,
    sum(paSpc) paSpc, sum(paSpcCnt) paSpcCnt
 from r
)
select substr(strip(db) || '.' || strip(ts), 1, 16),smallInt(part)part,
     substr(fosFmte7(spc), 1, 7) spc, cPa,
     substr(fosFmte7(rtsSpc), 1, 7) rtsSpc, rtsSpcCnt,
     substr(fosFmte7(paSpc), 1, 7) paSpc, paSpcCnt
     from t
order by t.spc desc
fetch first 100 rows only
with ur
;x;
    from sysIbm.tableSpaceStats
    group by dbName, name, dbId, psId
)
, ixRts as
( select creator cr, name ix, dbId, isobId,
        sum(real(max(space, 0))*1024) spRts
    from sysIbm.indexSpaceStats
    group by creator, name, dbId, isobId
)
, tsIx as
( select t.dbName db, t.tsName ts, count(*) indexs,
        sum(max(x.spacef, 0) * 1024) spRun, sum(spRts) spRts
    from sysIbm.sysTables t
        join sysIbm.sysIndexes x
        on x.tbCreator = t.creator and x.tbName = t.name
        left join ixRts r
        on x.creator = r.cr and x.name = r.ix
           and x.dbid = r.dbid and x.isobId = r.isobId
    group by t.dbName, t.tsName
)
, ts as
( select s.dbName db , s.name ts, max(partitions, 1) tParts,
        max(s.spacef, 0) * 1024 tSpRun,
        coalesce(r.SpRts, 0) tSpRts,
        i.indexs, i.spRun iSpRUn, i.spRts iSpRts
      from sysibm.sysTableSpace s
      left join tsRts r
      on s.dbName = r.db and s.name = r.ts
         and s.dbId = r.dbId and s.psId = r.psId
      left join tsIx  i
      on i.db = s.dbName and i.ts = s.name
)
select char(db, 8) "db", char(ts, 8) "ts",
        tParts "tParts",
        char(fosFmtE7(tSpRun), 7) "tSpRun",
        char(fosFmtE7(tSpRts), 7) "tSpRts",
        coalesce(indexs, 0) "indexs",
        char(fosFmtE7(coalesce(iSpRun, 0)), 7) "iSpRun",
        char(fosFmtE7(coalesce(iSpRts, 0)), 7) "iSpRts"
    from ts
    where db like 'DA540769%'
    order by tSpRts desc
    fetch first 100 rows only
    with ur
;