zOs/SQL/PDBPKGDD

-- db2 package usage aus tacct_package pro Tag
--          1 Tag im Verhältnis zu ganzem  range pro Package
--
set current path = oa1p;
with a as
  (
    SELECT PCK_ID       PK
          ,SUM( CASE WHEN DATE(TIMESTAMP) = '17.12.2012'
                     THEN SQL_STMTS_ISSUED  ELSE 0 END) d1stmts
          ,SUM( CASE WHEN DATE(TIMESTAMP) = '17.12.2012'
                     THEN CLASS7_CPU_TOTAL  ELSE 0 END) d1CPU
          ,SUM( CASE WHEN DATE(TIMESTAMP) = '17.12.2012'
                     THEN CLASS7_SU_CPU  ELSE 0 END) d1Su
          ,SUM(SQL_STMTS_ISSUED) stmts
          ,sum(CLASS7_CPU_TOTAL) as c7cpu
          ,sum(CLASS7_SU_CPU   ) as c7su
      FROM RZ2DD.TACCT_PROGRAM
        WHERE TIMESTAMP between '2012-12-12-00.00.00.000000'
                            and '2012-12-19-23.59.00.000000'
                            and pck_id = 'YERSGRI'
      AND GROUP_NAME = 'DBOF'
      GROUP BY pck_id
  )
SELECT substr(pk, 1, 8) pk
     , smallInt(1000 * d1Stmts / max(1, stmts)) stPrm
     , smallInt(1000 * d1Su / max(1, c7Su)) suPrm
     , fosFmte7(d1Cpu - 0.2 * c7cpu) cpuPlus
     , fosFmte7(d1Stmts)    d1Stmts
     , fosFmte7(d1cpu  )    d1cpu
     , fosFmte7(d1Su   )    d1Su
     , fosFmte7(  Stmts)    stmts
     , fosFmte7(c7cpu  )    c7cpu
     , fosFmte7(c7Su   )    c7Su
  FROM A
    ORDER BY d1Cpu - 0.2 * c7cpu
FETCH FIRST 1000 ROWS ONLY
WITH UR
;
, 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