zOs/SQL/CATTSDSS

WITH D AS
( SELECT
    CASE S.DSSIZE
    WHEN 0 THEN
        CASE WHEN S.TYPE =  'G'  THEN 4194304
        WHEN S.TYPE =  'O'  THEN 4194304
        WHEN S.TYPE = 'P'  THEN 4194304
        WHEN S.TYPE =  'R'  THEN 4194304
        ELSE
            CASE WHEN S.PARTITIONS > 254 THEN
                CASE WHEN S.PGSIZE = 4 THEN 4194304
                WHEN S.PGSIZE = 8 THEN 8388608
                WHEN S.PGSIZE = 16 THEN 16777216
                WHEN S.PGSIZE = 32 THEN 33554432
                ELSE NULL
                END
            WHEN S.PARTITIONS > 64 THEN 4194304
            WHEN S.PARTITIONS > 32 THEN 1048576
            WHEN S.PARTITIONS > 16 THEN 2097152
            WHEN S.PARTITIONS > 0 THEN 4194304
            ELSE 2097152
            END
        END
    ELSE S.DSSIZE
    END DSSMAN
,   case
        when s.dssize <> 0 then s.dssize
        when s.type in ('G', 'O', 'P', 'R', 'L') then 4194304
        when S.partitions > 254 then 1048576 * s.pgSize
        when s.partitions > 64 then  4194304
        when s.partitions > 32 then  1048576
        when s.partitions > 16 then  2097152
        when s.partitions > 0 then   4194304
                              else   2097152
    end dssWk, s.*
    from sysibm.sysTablespace s
)
select count(*), dssMan, dssWk, min(type), max(type)
    from d
    group by dssMan, dssWk
--  where dssMan <> dssWk or dssMan is null or dssWk is null
;x;
select dssize, partitions, type, count(*)
    from sysibm.systablespace
    where type = 'L'
    group by dssize, partitions, type
;x;: