zOs/SQL/PDBPKGX4

set current path oa1p;
with a as                             -- programm mit grösster Zunahme
( select
         sum(stmts) / count(*)  stmts,
         sum(dayCnt) / count(*) dayCnt,
         max(1, sum(stmts) / count(*))  stmt1,
         sum(c7cpu) / count(*)  c7cpu,
         sum(c7Ela) / count(*)  c7ela,
         pkg
      from A540769.tPkgWeek
      group by pkg
)
, w as
(
  select w.pkg, w.timestamp,
         a.c7cpu * w.stmts/a.stmt1 c7Avg,
         w.c7cpu - a.c7cpu * w.stmts/a.stmt1 c7Diff,
         w.c7cpu / max(1, a.c7cpu * w.stmts/a.stmt1) c7Pro,
         w.c7ela - a.c7ela * w.stmts/a.stmt1 elaDiff,
         w.stmts wStmts ,
         w.dayCnt wDayCnt ,
         w.c7Cpu wCpu ,
         w.c7Ela wEla   ,
         a.stmts aStmts ,
         a.stmt1 aStmt1 ,
         a.dayCnt aDayCnt ,
         a.c7Cpu aCpu ,
         a.c7Ela aEla
  from a join A540769.tPkgWeek w
    on a.pkg = w.pkg
)
select pkg
     , fosFmtE7(c7Pro) cpuPro
     , fosFmtE7(c7Diff) cpuDiff
     , fosFmtE7(elaDiff) elaDiff
     , w.*
from w
   where w.timestamp > '20.05.2012'
order by c7Diff desc
;;,
set current path oa1p;
with a as                      -- Wochenverteilungen
( select
         sum(stmts) / count(*)  stmts,
         max(1, sum(stmts) / count(*))  stmt1,
         sum(c7cpu) / count(*)  c7cpu,
         sum(c7Ela) / count(*)  c7ela,
         pkg
      from A540769.tPkgWeek
      group by pkg
)
, w as
(
  select w.pkg, w.timestamp,
         a.c7cpu * w.stmts/a.stmt1 c7Avg,
         w.c7cpu - a.c7cpu * w.stmts/a.stmt1 c7Diff,
         w.c7cpu / max(1, a.c7cpu * w.stmts/a.stmt1) c7Pro,
         w.stmts wStmts ,
         w.c7Cpu wCpu ,
         w.c7Ela wEla   ,
         a.stmts aStmts ,
         a.stmt1 aStmt1 ,
         a.c7Cpu aCpu ,
         a.c7Ela aEla
  from a join A540769.tPkgWeek w
    on a.pkg = w.pkg
)
select timestamp
     , fosFmtE7(min(c7Pro)) cpuProMin
     , fosFmtE7(max(c7Pro)) cpuProMax
     , count(*) cnt
     , fosFmte7(sum(  wcpu - acpu * wstmts/ aStmt1)) cpuDiff
     , fosFmte7(sum(  wEla - aEla * wstmts/ aStmt1)) elaDiff
     , fosFmte7(sum(wStmts)) wStmts
     , fosFmte7(sum(wCPu)) wCpu
     , fosFmte7(sum(wEla)) wEla
from w
     group by timestamp, floor(0 * log10(max(0.01, c7Pro)))
     order by timestamp desc, min(c7Pro) desc
;;,
set current path oa1p;
select timestamp, pkg
     , fosFmtE7(c7Cpu) "proc"
     , fosFmtE7(c7Cpu/stmts) "pro/stm"
     , fosFmtE7(c7Ela/stmts) "ela/stm"
     , w.*
from A540769.tPkgWeek w
where pkg = 'YWYU114'
order by timestamp desc
;x;          YRMVER ==> 21.5
set current path oa1p;
select timestamp, pkg
     , fosFmtE7(c7Cpu/stmts) "pro/stm"
     , fosFmtE7(c7Ela/stmts) "ela/stm"
     , w.*
from A540769.tPkgWeek w
where pkg = 'YWAPCUR'
order by timestamp desc
;x;          YRMVER ==> 21.5
set current path oa1p;
select timestamp                      -- veränderung pro woche
    , fosfmte7(sum(c7cpu)/max(1, sum(stmts))) "cpu/stmt"
    , fosfmte7(sum(c7ela)/max(1, sum(stmts))) "ela/stmt"
    , fosFmte7(sum(stmts)) "stmts"
    , fosFmte7(sum(c7Cpu)) "cpu"
    , fosFmte7(sum(c7Ela)) "ela"
    , fosFmte7(sum(occ))   "occ"
    , count(*)             "cnt"
    , count(distinct pkg)  "pkgs"
      from A540769.tPkgWeek w
      group by timestamp
      order by timestamp
;;
select timestamp,
       fosfmte7(c7cpu/max(1, stmts)) "cpu/stmt",
       fosfmte7(c7ela/max(1, stmts)) "ela/stmt",
       w.*
      from A540769.tPkgWeek w
      where pkg = 'YCTS100'
      order by timestamp desc
;;
             DSNTEP2
             YAVRATE  ==> 14. & 21.5
             KC5400   ==> 14.
             YAVRATS  ==> neues Program ??? ab 7.5
  order by w.c7cpu - a.c7cpu * w.stmts/a.stmt1   desc
;x;
with a as
( select
         sum(stmts) / count(*)  stmts,
         max(1, sum(stmts) / count(*))  stmt1,
         sum(c7cpu) / count(*)  c7cpu,
         sum(c7Ela) / count(*)  c7ela,
         pkg
      from A540769.tPkgWeek
      group by pkg
)
  select w.pkg, w.timestamp,
         fosFmtE7(a.c7cpu * w.stmts/a.stmt1) c7Avg,
         fosFmtE7(w.c7cpu - a.c7cpu * w.stmts/a.stmt1) c7Diff,
         fosFmtE7(w.c7cpu / max(1, a.c7cpu * w.stmts/a.stmt1)) c7Pro,
         w.*, a.*
  from a join A540769.tPkgWeek w
    on a.pkg = w.pkg
  order by w.c7cpu - a.c7cpu * w.stmts/a.stmt1   desc
;x;