zOs/SQL/CATIXTT

with s as
(
select piecesize
    , ( select case when min(partition) = '0' then 'not' else 'part' end
         from sysibm.sysIndexpart ip
         where ip.ixCreator = i.creator and ip.ixName = i.name ) pa
    from sysibm.sysindexes i
)
select count(*) cnt, piecesize, pa
    from s
    group by piecesize, pa