zOs/SQL/CATPARTC

-- count index/table based partitioned ts
set current application compatibility 'V11R1';
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
    from sysibm.sysTables t join sysibm.sysTablespace s
      on t.dbName = s.dbname and t.tsName = s.name
 )
select count(*), ty
    from x
    group by rollup(ty)