zOs/SQL/CATTSTYC

-- count index/table based partitioned ts -----------------------------
set current application compatibility 'V11R1';
with x as
( select s.*
    , value ( ( select max(t.partKeyCOLNUM)
                from sysibm.sysTables t
                where t.dbName = s.dbname and t.tsName = s.name
            ) , 0) partKeyCol
    from sysibm.sysTablespace s
)
, t as
( select case when partitions = 0 and segsize = 0 then 'simple'
              when partitions = 0                 then 'segmented'
              else type  end
      || case when partitions = 0 or maxPartitions > 0  then ''
              when partKeyCol = 0 then ' ixPart'
                                   else ' tbPart' end
      || case when nTables = 1 then ''
              when nTables < 1 then ' noTb'
              else ' multiTb' end ty
    , x.*
    from x
 )
 select count(*), ty, sum(nTables) nTables
    from t
    group by rollup(ty)
 ;x;
with x as
( select case when s.partitions = 0 and s.segsize = 0 then 'simple'
              when s.partitions = 0                   then 'segmented'
              when t.partKeyCOLNUM < 1 then s.type || ' ixPart'
              else                          s.type || ' tbPart' end ty
    , strip(t.creator) || '.' || t.name tb
    from sysibm.sysTables t join sysibm.sysTablespace s
      on t.dbName = s.dbname and t.tsName = s.name
    where t.type not in ('V', 'A')
 )
select count(*), ty, substr(min(tb), 1, 30), max(tb)
    from x
    group by rollup(ty /*, left(tb, 8) */)