zOs/SQL/CATTSTYP

set current path oa1p;
-- copy catTsPlu begin
-- tsPlus: sysIbm.sysTableSpace plus missing infos
with
ti as
(
  select dbName db, tsName ts, sum(colCount) cols
       , min(partKeyColNum) partKeyMin
       , max(partKeyColNum) partKeyMax
    from sysibm.SysTables
    where type not in ('A', 'V')
    group by dbName, tsName
)
, 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
)
, tsTy as
(
  select
      s.*
     , case when t.db is null then 'noTab'
            when partKeyMin is null or partKeyMax is null
            then 'partKey Null'
            when partKeyMin <> partKeyMax then 'partKey'
               ||partKeyMin || '<>' || partKeyMax
            when s.partitions = 0 then ''
            when partKeyMin = 0 then 'ixPart'
            when partKeyMin > 0 then 'tbPart'
            else 'partKey ' || partKeyMin
            end partTy
     , t.cols
     , (select sum(real(r.nActive))
         from sysibm.sysTableSpaceStats r
         where s.dbName = r.dbName and s.name = r.name
             and s.dbid = r.dbid and s.psid = r.psid ) * 1024 * s.pgSize
            actB
     from tsPlus2 s
       left join ti t
         on s.dbName = t.db and s.name = t.ts
)
, o1 as
(
  select count(*) ts
  , sum(max(partitions, 1)) parts
  , sum(cols) cols
  , sum(actB) actB
  , tsTy, partTy
  from tsTy
  group by tsTy, partTy
)
, o2 as
(
  select sum(ts) ts
  , sum(parts) parts
  , sum(cols) cols
  , sum(actB) actB
  , '*' tsTy, '*' partTy
  from o1
  union all select * from o1
)
select ts, parts, cols, substr(fosFmte7(actB), 1 ,7) actB
  , tsTy, partTy
from o2
order by tsTy, partTy
;x;
-- copy catTsPlu end
select strip(dbName) || '.' || strip(name), s.*
from tsTy s where partTy = 'noTab';xc;