zOs/SQL/CATIXSPC

set current path = oa1p;
select count(*),
        substr(fosFmtE7(1024 * max(i.spacef)), 1, 7) "ixRunSp",
        substr(fosFmtE7(1024 * sum(p.spacef)), 1, 7) "paRunSp",
        substr(fosFmtE7(1024 * sum(float(r.space))), 1, 7) "rtsSp",
        substr(fosFmtE7(1024 * max(coalesce(sum(p.spacef), 0),
               coalesce(sum(float(r.space)), 0))), 1, 7) "maxRRSp",
           strip(i.creator) || '.' || strip (i.name)
       FROM  SYSIBM.SYSINDEXPART p
       join sysibm.sysIndexes i
       on     i.creator = p.ixCreator
          and i.name    = p.ixName
       left join sysibm.sysindexSpaceStats r
       on    r.DBID       = i.dbid
         and r.ISOBID     = i.isobid
         and r.PARTITION  = p.partition
    where i.tbCreator = 'BUA' and i.tbName = 'XBDM7001PM001003'
    group by i.creator, i.name
    order by 4 desc
    fetch first 100 rows only
    with ur
;
x
   SELECT B.IXCREATOR, B.IXNAME, B.GBPCACHE
       WHERE B.IXCREATOR = A.CREATOR
       AND   B.IXNAME    = A.NAME
       AND   B.GBPCACHE NOT IN (' ')
       )