zOs/SQL/CATIXPIE

set current sqlid = 'S100447';
with j as
(
select i.PIECESIZE, i.ibmReqd, s.partitions
     ,  case
            when s.dssize <> 0 then s.dssize
            when s.type in ('G', 'O', 'P', 'R', 'L') then 4194304
            when S.partitions > 254 then 1048576 * s.pgSize
            when s.partitions > 64 then  4194304
            when s.partitions > 32 then  1048576
            when s.partitions > 16 then  2097152
            when s.partitions > 0 then   4194304
                                  else   2097152
        end dsSize
        from sysibm.sysIndexes i join sysibm.sysTables t
            on i.tbCreator = t.creator and i.tbName = t.name
               and i.dbName = t.dbName
          join sysibm.sysTablespace s
            on s.dbName = t.dbName and s.name = t.tsName
)
select count(*), pieceSize, partitions, dsSize
               , min(ibmReqd)
               , max(translate(ibmReqD, '  ', 'YN'))
               , max(case when ibmReqD = 'N' then 'N' else ' ' end)
               , max(case when ibmReqD = 'Y' then 'Y' else ' ' end)
    from j
    group by pieceSize, partitions, dsSize
;x;
select PIECESIZE, (select
        case
            when s.dssize <> 0 then s.dssize
            when s.type in ('G', 'O', 'P', 'R', 'L') then 4194304
            when S.partitions > 254 then 1048576 * s.pgSize
            when s.partitions > 64 then  4194304
            when s.partitions > 32 then  1048576
            when s.partitions > 16 then  2097152
            when s.partitions > 0 then   4194304
                                  else   2097152
        end dssWk
        from sysibm.sysTablespace s join sysibm.sysTables t
            on s.dbName = t.dbName and s.name = t.tsName
        where  i.tbCreator = t.creator and i.tbName = t.name
               and i.dbName = t.dbName
        ) tsDss
    from sysibm.sysIndexes i
    fetch first 100 rows only
;x;
CREATE        UNIQUE INDEX "A540769"."IWK993t1"
       ON "A540769"."TWK993PAR"
      (PARID ASC
      )
       NOT CLUSTER
       DEFINE YES
       COMPRESS NO
       BUFFERPOOL BP1
       CLOSE YES
       DEFER NO
       COPY NO
       USING STOGROUP GSMS
           PRIQTY -1
           SECQTY -1
           ERASE NO
       FREEPAGE 5
       PCTFREE 10
       GBPCACHE CHANGED;
select PIECESIZE, name
    from sysibm.sysIndexes
    where tbCreator = 'A540769' and tbName = 'TWK993PAR'
;
rollback