zOs/SQL/CATTSPLU

-- 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