zOs/SQL/CATIXLIM

with ts as
  ( select s.*,
            case when partitions = 0 then 64 * 1024 * 1024
                 when dssize <> 0 then dssize
                 when type in ('K', 'L') then 4 * 1024 * 1024
                 when partitions <=  16 then 4 * 1024 * 1024
                 when partitions <=  32 then 2 * 1024 * 1024
                 when partitions <=  64 then 1 * 1024 * 1024
                 when partitions <= 254 then 4 * 1024 * 1024
                 when partitions <=  16 then 4 * 1024 * 1024
            end dsSz
        from sysibm.sysTablespace s
  )
  , ii as
  (
    select i.*,
        case when maxPart > 0 then 1
             when s.dsSize < 4e6 and not s.type in ('L', 'K') then 32
             else 4096
        end pieceCnt,
        case when maxPart > 0 then dsSz
             when pieceSize > 0 then piecesize
             when s.dsSz >= 4 then 4 * 1024 * 1024
             else 2  * 1024 * 1024
        end pieceSz
    from sysibm.sysindexes i, sysibm.systables t, ts s,
        ( select max(partition) maxPart, ixCreator, ixName
               from sysibm.sysindexpart p
               group by ixCreator, ixName
        ) p
    where
            i.tbCreator = t.creator and i.tbName = t.name
        and t.dbName = s.dbName and t.tsName = s.name
        and i.creator = p.ixCreator and i.name = p.ixName
      --and (t.type not in ('L', 'K') and t.dssize < 4000000)
      --     or (t.
 --     and i.piecesize > 0 and i.piecesize < 65000000
  )
select coalesce(float(r.space) / ii.pieceSz / ii.pieceCnt, -1),
       coalesce(float(r.nActive) * 4 / ii.pieceSz / ii.pieceCnt, -1),
       strip(ii.Creator) || '.' || strip(ii.name)
                         || ':' || strip(char(partition))
    from ii, sysibm.indexspacestats r
    where ii.dbid = r.dbid and ii.isobid = r.isobid
          and ii.dbname = 'DGDB9998'
    order by 1 desc, 2 desc
    ;
  , tru as
  (
select trunc(float(r.space) / ii.pieceSz / ii.pieceCnt, 1) sz,
       trunc(float(r.nActive) * 4 / ii.pieceSz / ii.pieceCnt, 1) ac
    from ii, sysibm.indexspacestats r
    where ii.dbid = r.dbid and ii.isobid = r.isobid
  )
select sz, ac, count(*) from tru
    group by sz, ac
    order by 1 desc, 2 desc
    ;

select piecesize, pieceSz, dssize, dsSz, pieceCnt, count(*)
    from ii
    group by piecesize, pieceSz, dssize, dsSz, pieceCnt
    order by piecesize, pieceSz, dssize, dsSz, pieceCnt
;
x
select strip(ixcreator) || '.' || strip(ixname)
    from sysibm.sysindexpart
    where partition > 0 and ixcreator = 'A540769'
    ;
    x
select distinct length(limitKey), limitKey,
               case when limitKey = '0' then '=0'
                    when limitKey = x'00' then '=x'
               else 'not' end,
               hex(substr(limitKey,1,1))
               from sysibm.sysindexpart p
               where partition = 0 and length(limitKey) > 0
;
xelect length(limitKey), hex(limitKey)
select length(limitKey), hex(char(limitKey, 5)) || '::' || limitKey, p.*
               from sysibm.sysindexpart p
               where partition = 0 and length(limitKey) > 0
;
xelect length(limitKey), hex(limitKey)
               from sysibm.sysindexpart p
               where ixName = 'IWK101A0'
;
select piecesize, i.indexType, miPa, maPa, cnPa, liKe, count(*)
    from sysibm.sysindexes i left join
        ( select min(partition) mipa, max(partition) mapa,
                 count(*) cnpa, ixCreator, ixName,
                 sum(length(limitKey)) liKe
               from sysibm.sysindexpart p
               group by ixCreator, ixName
        ) p
    on i.creator=p.ixCreator and i.name = p.ixName
    group by piecesize, i.indexType, miPa, maPa, cnPa, like
;
xelect distinct piecesize,
    case when partition = 0 then 'np' else 'pa' end, i.*
    from sysibm.sysindexes i, sysibm.sysindexpart p
    where i.creator=p.ixCreator and i.name = p.ixName
       and piecesize = 0 and partition = 0
;
 d
 d