zOs/SQL/SPCTS1

set current path = 'OA1A';
with tsRts as
( select dbName db, name ts, dbId, psId,
         sum(real(max(space, 0))*1024) spRts
    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
;