zOs/SQL/PDBPKGHH

-- db2 package usage aus tacct_package pro Tag
--          average 24h, average peak Hours und max
--
set current path = oa1p;
with a as
  (
    SELECT trunc_timestamp(TIMESTAMP, 'hh') tst
          ,SUM(SQL_STMTS_ISSUED) stmts
          ,SUM(CLASS7_SU_CPU) AS c7su
          ,sum(CLASS7_CPU_TOTAL) as c7cpu
      FROM RZ2HH.TACCT_PROGRAM
        WHERE TIMESTAMP between '2012-05-01-00.00.00.000000'
                            and '2012-05-31-23.59.00.000000'
       -- AND GROUP_NAME = 'DBOF'
      GROUP BY trunc_timestamp(TIMESTAMP, 'hh')
  )
, ad as
(
select date(a.tst)  datum
    , sum(a.c7cpu) / count(*) / 3600 cpu
    , sum(a.stmts) / count(*) / 3600 stmts
    , count(*) hh
    , max(a.c7cpu) / 3600 maxCpu
    from a
    group by date(tst)
)
, pd as
(
select date(a.tst)  datum
    , sum(a.c7cpu) / count(*) / 3600 cpu
    , sum(a.stmts) / count(*) / 3600 stmts
    , count(*) hh
    , max(a.c7cpu) / 3600 maxCpu
    from a
    where dayofweek_iso(TST) <= 5
                AND hour(tst) between 9 and 11
    group by date(tst)
)
select ad.datum
    , substr(fosFmtE7(ad.cpu), 1, 7) "allCpu"
    , substr(fosFmtE7(pd.cpu), 1, 7) "peakCpu"
    , substr(fosFmtE7(ad.maxCpu), 1, 7) "maxCpu"
    , substr(fosFmtE7(ad.stmts), 1, 7) "allStmts"
    , substr(fosFmtE7(pd.stmts), 1, 7) "peakStmts"
    , smallint(ad.hh) "allHH", smallInt(pd.hh) "peakHH"
    from ad left join pd on ad.datum = pd.datum
    order by 1 desc
;
--
-- db2 package usage aus tacct_package total
--          average 7*24h, average peak Hours und max
--
--    vergleich tacct_general und tacct_package total Zahlen
--
--
set current path = oa1p;
with p as
  (
    SELECT trunc_timestamp(TIMESTAMP, 'hh') tst
          ,SUM(SQL_STMTS_ISSUED) stmts
          ,SUM(CLASS7_SU_CPU) AS c7su
          ,sum(CLASS7_CPU_TOTAL) as c7cpu
      FROM RZ2HH.TACCT_PROGRAM
        WHERE TIMESTAMP between '2012-02-27-00.00.00.000000'
                            and '2012-04-01-23.59.00.000000'
     --   AND GROUP_NAME = 'DBOF'
      GROUP BY trunc_timestamp(TIMESTAMP, 'hh')
  )
  select substr(fosFmtE7(sum(c7cpu) / count(*) / 3600), 1, 7) avg,
         substr(fosFmtE7(max(c7cpu) /3600            ), 1, 7) max,
         substr(fosFmtE7(sum(case when dayofweek_iso(TST) <= 5
                      AND hour(tst) between 9 and 11
                  then c7Cpu else 0 end)
            / sum(case when dayofweek_iso(TST) <= 5
                      AND hour(tst) between 9 and 11
                  then 1 else 0 end) /3600 ), 1, 7) avgPeak,
         count(*) hh,
         sum(case when dayofweek_iso(TST) <= 5
                      AND hour(tst) between 9 and 11
                  then 1 else 0 end) peakHours
      from p
    order by 1 desc
;
  ;;; order by 1
        ,substr(fosFmte7(C2_CPU), 1, 7) c2_cpu
        ,substr(fosFmte7(CPU_C7), 1, 7) c7_cpu
        ,substr(fosFmte7(C2_SU), 1, 7) c2_su
        ,substr(fosFmte7(SU_CLASS7), 1, 7) c7_su
        ,substr(fosFmte7(mdl), 1, 7) gen_st
        ,substr(fosFmte7(anz_sql), 1, 7) pg_st
        ,substr(fosFmte7(C2_SU/mdl), 1, 7) gen_su_st
        ,substr(fosFmte7(SU_CLASS7/anz_sql), 1, 7) pg_su_st

   from g full join p
     on p.datum = g.datum
    ORDER BY 1 DESC