zOs/SQL/CATIX

select *
    from sysibm.sysKeyTargets where ixName = 'IQZ008S1'
;x;
with t0 as
(
  select dbname db, name ts, pgSize
      , partitions parts, maxPartitions maxParts, dsSize
      , case
            when type <> ' ' then type
            when partitions > 0 and segsize = 0 then 'p' -- classic part
            when partitions = 0 and segsize = 0 then 'i' -- simple
            when partitions = 0 and segsize > 0 then 's' -- segmented
            else raise_error(70000, 'unknown ts type='||type
                || ' partitions=' || strip(char(partitions))
                || ' segsize=' || strip(char(segsize))
                || ' db.ts=' || strip(dbName) ||'.'||name) end tsTy
      , case
            when dssize <> 0 then int(dssize / 1048576)
            when type in ('G', 'O', 'P', 'R', 'L') then 4
            when partitions = 0     then  64
            when partitions > 254 then    pgSize
            when partitions > 64    then   4
            when partitions > 32    then   1
            when partitions > 16    then   2
                                    else   4
        end dsGB
      , dbid, psid
      from sysibm.sysTablespace
)
, ts as
( select t0.*
      , case when tsTy = 'G' or parts = 0 then null else 1 end scope
      , case when tsTy = 'G' then dsGB * maxparts
             when tsTy = 'O' then dsGB * 254
             else dsGb end limGB
      from t0
)
, tb as
(
    select t.creator tbCr, t.name tb, ts.*
      from ts join sysibm.sysTables t
          on ts.db = t.dbName and ts.ts = t.tsName
)
, i0 as
(
  select indexType, pieceSize, relCreated, createdTS
            , spaceF, creator ixCr, name ix
         , case when i.pgSize = 4096 or i.compress = 'Y' then 4
                else i.pgSize
           end ixPgSz
         , (select min(partition)
              from sysibm.sysIndexPart p
                  where p.ixCreator = i.creator
                    and p.ixName = i.name
           ) ixMin
         , (select max(partition)
              from sysibm.sysIndexPart p
                  where p.ixCreator = i.creator
                    and p.ixName = i.name
            ) ixMax
         , tb.*
    from sysibm.sysIndexes i join tb
      on i.tbCreator = tb.tbCr and i.tbName = tb.tb
)
, i1 as
(
  select case when piecesize <> 0 then real(pieceSize) / 1048576
              when parts <> 0 then real(dsGB) * ixPgSz / pgSize
              else 2
         end pcGb
       , i0.*
      from i0
)
,  i2 as
(
  select case when ixMin = 1 then 1
              when parts = 0 then 32
              when tsTy <> 'L' and dsSize = 0 and parts <= 64 then 32
              when parts > 254 then 4096
              else smallInt(min(4096, 4096 / pcGb * ixPgSz))
         end pcMx
         , i1.*
      from i1
)
,  r as
(
  select pcGb * pcMx ixpGb
       , float(r.nActive) * i2.ixPgSz / 1048576 actGB
       , float(r.space) / 1048576 spaceGB
       , i2.*
    from i2 join sysibm.sysindexspacestats r
      on i2.ixCr = r.creator and i2.ix = r.name
)
select smallInt(100 * actGB/ixpGb) actPc
     , smallInt(100 * spaceGB/ixpGb) spcPc
     , r.*
    from r
 -- where db = 'DA540769'
    where actGB is not null
    order by 1 desc
    fetch first 100 rows only
;x;
select * from r
    where db = 'DA540769'
    and ixpGB <= 0
;x; group by pcMx, pcGb, ixMin, min(parts, 1), tsTy
     --  ixPgSz <> 4
     --  or  ( not ((ixMin=0 and ixMax=0)
     --            or (ixMin =1 and ixMax= parts)))
 -- order by spaceF desc
;x;
, i1 as
(
  select indexType, pieceSize, relCreated, createdTS
      , case when ixMin = 0 and ixMax = 0 then 'nonP'
             when ixMin = 1 and ixMax >= 1 then 'part'
             else raise_error(70001, 'parts ' || char(ixMin)
                                || ' - ' || char(ixMax))
        end partd
    from i0
)
select count(*), indexType, pieceSize, partd
    , max(relCreated), min(relCreated)
    , max(createdTS), min(createdTS)
    from i1
    group by indexType, pieceSize, partd
;x;
with ii as
(
select case when s.partitions = 0 then 'npTs ' else 'paTs ' end tt,
       i.indexType ixType, s.type tsType,
       ( select case when max(p.partition) = 0 then 'npIx ' else 'paIx '
                end
            || char(min(p.partition))
            || char(max(p.partition))
            || char(count(*))
           from sysibm.sysIndexPart p
           where p.ixCreator = i.creator and p.ixName = i.Name
       ) it,
       s.partitions, t.cardf,
       i.*
    from sysibm.sysIndexes i
      join sysibm.sysTables t
        on i.tbCreator = t.creator and i.tbName = t.name
      join sysibm.sysTableSpace s
        on s.dbName = t.dbName and s.name = t.tsname
 -- where i.dbName = 'DA540769'
)
select tt, ixType, left(it, 5), count(*)
    from ii
    group by tt, ixType, left(it, 5)
    order by tt, ixType, left(it, 5)
;;,
select * from sysibm.sysIndexPart
    where ixcreator = 'OA1A' and ixname like 'IMF150H%'
;
x
select '01 create    ',
        char(ixCreator, 8) cr, char(ixName, 16) ix, p.partition,
        statsTime "runStats",
        cardf,
        r.space/4 alPg, leafDist, leafNear, leafFar,
        nearOffPosf, farOffPosf,
        updatestatsTime "realTimeStats",
        nLeaf, reorgLeafNear, reorgLeafFar
    from sysibm.sysindexpart p
        left join sysibm.sysindexspacestats r
        on     p.ixCreator = r.creator
           and p.ixName    = r.name
           and p.partition = r.partition
    where ixCreator = 'GDB9998' and ixName like 'IWK465%'
;