zOs/SQL/CATIXPLU

-- copy catIxPlu begin
-- ixPlus: sysibm.sysIndexes plus missing infos
-- copy catTsPlu begin
-- tsPlus: sysIbm.sysTableSpace plus missing infos
, tsPlus2 as
(
  select s.*
      , char(value(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(70101, 'unknown ts type='||type
                || ' partitions=' || strip(char(partitions))
                || ' segsize=' || strip(char(segsize))
                || ' db.ts=' || strip(dbName) ||'.'||name)
        end, '?'), 1) tsTy
      , real(value(case
            when dssize <> 0 then real(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, -99)) dsGB
      from sysibm.sysTablespace s
)
, tsPlus as
( select s.*
      , smallInt(case when tsTy = 'G' or partitions = 0
                      then 0 else 1 end) limPart
      , real(value(case when tsTy = 'G' then dsGB * maxPartitions
             when tsTy = 'O' then dsGB * 254
             else dsGb end, -99)) limGB
      from tsPlus2 s
)
-- copy catTsPlu end
, ixPlus2 as
(
  select i.*
         , s.name ts
         , s.tsTy
         , s.pgSize tsPgSz
         , s.partitions tsParts
         , s.dsSize tsDsSize
         , s.dsGb   tsDsGb
         , s.limGb   tsLimGb
         , s.limPart tsLimPart
         , s.clone tsClone
         , s.instance tsInst
         , smallint(case when i.pgSize = 4096 or i.compress='Y' then 4
                else i.pgSize
           end) ixPgSz
         , smallint(value((select max(partition)
              from sysibm.sysIndexPart p
                  where p.ixCreator = i.creator
                    and p.ixName = i.name
           ), -99)) ixParts
    from sysibm.sysIndexes i
      join sysibm.sysTables t
        on i.dbName = t.dbName
            and i.tbCreator = t.creator and i.tbName = t.name
      join tsPlus s
        on i.dbName = s.dbName and t.tsName = s.name
)

, ixPlus3 as
(
  select i.*
      ,  real(value(
          case when piecesize <> 0 then real(pieceSize) / 1048576
              when ixParts <> 0 then tsDsGb * ixPgSz / tsPgSz
              when tsTy in ('s', 'i') then 2
              else 4
         end, -99)) pieceGB
      from ixPlus2 i
)
,  ixPlus as
(
  select i.*
       , real(value(case when ixParts <> 0 then 1
              when tsParts = 0 then 32
              when tsTy <> 'L' and tsDsSize=0 and tsParts <= 64 then 32
              when tsParts > 254 then 4096
              else smallInt(min(4096, 4096 / pieceGB * ixPgSz))
         end * pieceGB, -99)) limGb
      from ixPlus3 i
)
-- copy catIxPlu end