zOs/SQL/CATTBIXP

-- count tables space types incl. tb/ix partition muli Tabel ... ------
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
                     and t.type not in ('A', 'V')
            ) , 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
    , min(substr(strip(dbName) || '.' || name, 1, 30)), min(type)
    , max(strip(dbName) || '.' || name)
    from t
    group by rollup(ty)
 ;
-- list of ix base tables, including parititoning index ---------------
with t as
( select t.*
      , ( select strip(i.creator) || '.' || i.name
            from sysibm.sysIndexes i
              join sysibm.sysIndexPart p
                on i.creator = p.ixcreator and i.name = p.ixname
                  and p.partition = 1
             where t.creator = i.tbCreator and t.name = i.tbName
        ) ix
    from sysibm.sysTables t join sysibm.sysTablespace s
      on t.dbName = s.dbName and t.tsName = s.name
    where t.type not in ('A', 'V')
        and s.type in (' ', 'L') and partitions > 0
        and t.partKeyCOLNUM <= 0
)
select char(strip(creator) || '.' || name, 40) tb, ix, t.*
  from t
  order by creator, name
;x;