zOs/SQL/CATTSPLV

-- test cattsPlu versus old gbGr
with dummy as(select * from sysibm.sysDummy1 )
-- copy catTsPlu begin
-- tsPlus: sysIbm.sysTableSpace plus missing infos
, tsPlus2 as
(
  select s.*
      , 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 tsTy
      , 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 dsGB
      from sysibm.sysTablespace s
)
, tsPlus as
( select s.*
      , case when tsTy = 'G' or partitions = 0
             then 0 else 1 end partScope
      , case when tsTy = 'G' then dsGB * maxPartitions
             when tsTy = 'O' then dsGB * 254
             else dsGb end limGB
      from tsPlus2 s
)
-- copy catTsPlu end
, 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(70101, '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 0 else 9999 end scope
      , case when tsTy = 'G' then dsGB * maxparts
             when tsTy = 'O' then dsGB * 254
             else dsGb end limGB
      from t0
)
select n.tsTy, o.tsTy, n.limGb, o.limGb, n.partScope, o.scope, n.*
    from tsPlus n
      full outer join ts o
        on n.dbName = o.db and n.name = o.ts
    where n.dbName is null or o.db is null
        or n.tsTy <> o.tsTy or n.limGb <> o.limGb
        or n.partScope * 9999 <> o.scope
    fetch first 1000 rows only