zOs/SQL/CATCOPF0

-- copies gruppiert nach Anzahl Copies in den letzten 7 Tagen
--     paSpc  = runStats Space der kopierten Partitionen
--     copied = bytes kopiert
--     coCnt = Anzahl copies
--     paCnt = Anzahl Partitionen
--     taLim = 't' > 20Giga --> Tape
--     taLim = 'd' <= 20Giga --> Disk
select  s.dbName,
        case when p.spaceF > 20*1024*1024 THEN 't' else 'd' end taLim,
        coalesce(coCnt, 0) coCnt,
        count(*) paCnt,
        7 tage,
        sum(p.spaceF * 1024) paSpc,
        sum(copied * pgSize * 1024) copied,
        sum(coSize * pgSize * 1024) coSize,
        sum(coChgd * pgSize * 1024) coChgd
    from sysibm.systablespace s
        join sysibm.sysTablePart p
            on s.dbName = p.dbName and s.name = p.tsName
        left join (
            select icType, dbName, tsName, dsNum,
                    count(*) coCnt,
                    sum(copyPagesF) copied,
                    sum(nPagesF) coSize,
                    sum(cPagesF) coChgd
                from sysibm.sysCopy
                where date(timestamp) < current date
                  and date(timestamp) >= current date - 21 days
                  and icType in (     'F')
                group by icType, dbName, tsName, dsNum
            ) c
            on p.dbName = c.dbName and p.tsName = c.tsName
                                   and p.partition = c.dsNum
--  where s.dbName like 'MF01%'
    group by s.dbName, coCnt,
        case when p.spaceF > 20*1024*1024 THEN 't' else 'd' end
    having coCnt is null
    order by paSpc desc
    with ur