zOs/SQL/CATCOPFR

-- 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  coalesce(icType, 'n') icType,
        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 - 7 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 icType, coCnt,
        case when p.spaceF > 20*1024*1024 THEN 't' else 'd' end
    order by 1, 3
    with ur
;
select  coalesce(icType, 'n') icType,
        case when p.spaceF > 20*1024*1024 THEN 't' else 'd' end taLim,
        coalesce(coCnt, 0) coCnt,
        count(*) paCnt,
        14 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 - 14 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 icType, coCnt,
        case when p.spaceF > 20*1024*1024 THEN 't' else 'd' end
    order by 1, 3
    with ur
;
select  coalesce(icType, 'n') icType,
        case when p.spaceF > 20*1024*1024 THEN 't' else 'd' end taLim,
        coalesce(coCnt, 0) coCnt,
        count(*) paCnt,
        21 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 icType, coCnt,
        case when p.spaceF > 20*1024*1024 THEN 't' else 'd' end
    order by 1, 3
    with ur
;