zOs/SQL/PDBPKGY2

//A540769E  JOB (CP00,KE50),                                            00010001
//         MSGCLASS=T,TIME=1440,
//         NOTIFY=A540769
//*MAIN CLASS=LOG0 SYSTEM=S12
//S01      EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99                       00020001
//SYSTSIN  DD *
    DSN SYSTEM(DBOC)
   RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//***PRINT DD DISP=SHR,DSN=A540769.WK.TEXW(PDBGENNJ)
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD DUMMY
//SYSIN    DD *
-- 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-03-12-00.00.00.000000'
                            and '2012-04-01-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
;