zOs/TX/WK600SP2

select t.cardf, sum(real(r.totalRows)),
       strip(t.dbName) || '.' || strip (t.tsName)
    from sysibm.sysTables t,
         sysibm.sysTablespace s,
         sysibm.sysTableSpaceStats r
    where
         s.dbName = t.dbName and s.name = t.tsName
         and r.dbName = s.dbName and r.name = s.name
         and r.dbid = s.dbid and r.psid = s.psid
         and s.dbName = 'DA540769'
    group by t.dbName, t.tsName, t.cardf
    having
         t.cardf < sum(real(r.totalRows)) * .3
         and max(t.cardf, sum(real(r.totalRows))) > 1e7
    order by 1 desc
;
x
with rtsSpace (sp, db, ts, pa, ix) as
(
select s.space, s.dbName, name, partition, ''
    from sysIbm.sysTableSpaceStats s
union all select s.space, s.dbName, t.tsName, partition, s.name
    from sysIbm.sysIndexSpaceStats s
    join sysIbm.sysIndexes i
      on s.dbName = i.dbName and s.name = i.name
           and s.dbId = i.dbId and s.isobid = i.isobid
    join sysibm.sysTables t
        on s.dbName = t.dbName
        and i.tbCreator = t.creator and i.tbName = t.name
)
, sp as
(
select sum(case when ix = '' then sp else 0  end) tsSp,
       sum(case when ix = '' then 0  else sp end) ixSp,
       '*' db, '*' ts
    from rtsSpace
    where db = 'DMAREC'
union all select sum(case when ix = '' then sp else 0  end) tsSp,
       sum(case when ix = '' then 0  else sp end) ixSp,
       char(db, 8) db, char(ts, 8) ts
    from rtsSpace
    where db = 'DMAREC'
    group by db, ts
)
select * from sp
--  where tsSp > 1.4e6 and tsSp < 1.6e6
order by  2 desc
with ur
;
x
select i.indexSpace, t.name
    from sysibm.sysTables t, sysibm.sysindexes i
    where i.tbCreator = t.creator
        and i.tbName = t.name
        and t.dbName = 'DMAREC'
        and t.tsName in
            ('A600A000', 'A600A019', 'A600A001', 'A600A003'
            ,'A601A368', 'A600A099', 'A601A399'
            )
;
x
x
with rtsSpace (sp, db, ts, pa, ix) as
(
select s.space, s.dbName, name, partition, ''
    from sysIbm.sysTableSpaceStats s
union all select s.space, s.dbName, t.tsName, partition, s.name
    from sysIbm.sysIndexSpaceStats s
    join sysIbm.sysIndexes i
      on s.dbName = i.dbName and s.name = i.name
           and s.dbId = i.dbId and s.isobid = i.isobid
    join sysibm.sysTables t
        on s.dbName = t.dbName
        and i.tbCreator = t.creator and i.tbName = t.name
)
select sp, char(db, 8) db, char(ts, 8) ts, pa, ix
    from rtsSpace
    where db = 'DMAREC'
    order by 1 desc, 2 asc,3,4,5
    with ur
;