zOs/SQL/PDBPKGXB

set current path oa1p;
set current schema = RZ2dd;
with p  as
(
select pck_id pkg,
       date(trunc_timestamp(timestamp - 4 days, 'iw')) + 4 days woche,
               (sum(class7_cpu_total)) c7Cpu,
               (sum(class7_elapsed)) c7Ela,
                sum(SQL_STMTS_ISSUED) sqls,
       count(*) count,
               (sum(occurrences)) occ ,
       count(distinct date(timestamp)) tage,
       date(min(timestamp)) wocheVon,
       date(min(timestamp)) wocheBis
    from TACCT_program
    where   group_name = 'DVBP'
       and  timestamp >       '2012-09-28-00.00.00'
    group by group_name, trunc_timestamp(timestamp - 4 days, 'iw'),
             pck_id
    order by group_name
)
, w as
(
select  0 rng, '*' pkg , woche,
        sum(c7cpu) c7cpu,
        sum(c7Ela) c7Ela,
        sum(sqls) sqls,
        sum(count) count, sum(occ) occ,
        max(tage) tage, min(wocheVon) wocheVon, max(wocheBis) wocheBis
    from p
    group by woche
)
, r as
(
select row_number() over (partition by woche
                          order by c7Cpu desc) rng
       , p.*
    from p
)
, f as
(
    select * from w
    union all select * from r where rng <= 12
)
select  smallInt(rng) rng, pkg , woche,
        smallint(c7cpu * 1000/
             (select c7cpu from w where w.woche = f.woche)) c7Prm,
        fosFmtE7(c7cpu) c7cpu,
        fosFmtE7(c7Ela) c7Ela,
        fosFmtE7(sqls) sqls,
        fosFmtE7(count) count, fosFmtE7(occ) occ,
        tage, wocheVon, wocheBis
    from f
    order by f.pkg, f.woche desc
;x
??????????????????
set current path oa1p;
select
       trunc_timestamp(timestamp, 'ww'),
       substr(PCK_ID, 1, 8) pkg,
       fosFmtE7(sum(CLASS7_ELAPSED ) /sum(SQL_STMTS_ISSUED))  "ela/stm",
       fosFmtE7(sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED))  "cpu/stm",
       fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
       fosFmtE7(sum(CLASS7_ELAPSED ))   "c7ela",
       fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
       fosFmtE7(sum(CLASS8_LOCK_LATCH )) "c8loLa" ,
       fosFmtE7(sum(CLASS8_SYNC_IO ))    "c8synIO",
       fosFmtE7(sum(CLASS8_OTHER_READ )) "c8othRe",
       fosFmtE7(sum(CLASS8_OTHER_WRITE)) "c8othWr",
       fosFmtE7(sum(CLASS8_SERV_TASK ))  "c8SrvTa",
       fosFmtE7(sum(BP_GETPAGES ))       "getPg",
       fosFmtE7(sum(SYNC_READ ))         "synRead",
       fosFmtE7(sum(SEQ_PREFETCH ))      "seqPref",
       fosFmtE7(sum(LIST_PREFETCH ))     "listPre",
       fosFmtE7(sum(DYN_PREFETCH ))      "dynPref",
       fosFmtE7(sum(ASYNC_READ ))        "asyRead",
       fosFmtE7(sum(BUFFER_UPDATE ))     "bufUpd",
       fosFmtE7(sum(SYNC_WRITE ))        "synWrit",
       fosFmtE7(sum(PAR_GETPAGES_FAIL )) "gePgFai",
       fosFmtE7(sum(CLASS7_SU_CPU  ))        "c7SU",
       fosFmtE7(sum(OCCURRENCES ))   "occ",
       min(subsystem_id) subMin ,
       max(subsystem_id) subMax ,
       min(timestamp) tstMin,
       max(timestamp) tstMax
    from rz2dd.tacct_program
    where pck_id =   'XBIM34'  and plan_name = 'XBIM34'
        and subsystem_id like 'DBP%'
        and       timestamp > current timestamp -  7 months
    group by pck_id, trunc_timestamp(timestamp, 'ww')
    order by 1 desc
;;;
set current path oa1p;
-- elect pck_id, count(*), max(timestamp)--, group_name
select plan_name, date(trunc_timestamp(timestamp, 'iw')) ,
        fosfmte7(sum(class2_cpu_total)) cpu,
        fosfmte7(sum(class2_su_cpu   )) su,
        fosfmte7(sum(commit)) com,
        fosfmte7(sum(select)) sel,
        fosfmte7(sum(open)) open,
        fosfmte7(sum(fetch)) fetch,
        fosfmte7(sum(insert)) insert,
        fosfmte7(sum(update)) update,
        fosfmte7(sum(delete)) delete,
        min(timestamp), max(timestamp),
        count(*) cnt, fosfmte7(sum(occurrences)) occs
    from rz2dd.tacct_general
    where  plan_name in ('XBLE80'
                        ,'XBLE84'
                        ,'XBARR0'
                        ,'XBARR7'
                        ,'XBDP90'
                        ,'XBAP75'
                        ,'XBLE87'
                        )
         and timestamp > '2012-05-17-00.00.00'
         and date(trunc_timestamp(timestamp, 'iw'))
             in ('21.05.2012', '28.05.2012',
                 '18.06.2012', '25.06.2012')
    group by plan_name, trunc_timestamp(timestamp, 'iw')
    order by plan_name, trunc_timestamp(timestamp, 'iw')
;x;
    where (plan_name like 'XBLE8%'
        or plan_name like 'XB8%'
        or plan_name like 'XB8%'
        or plan_name like 'XBR%'
        or plan_name like 'XBR%'
        or plan_name like 'XBARR%'
        or plan_name like 'XB9%'
        or plan_name like 'XBDP9%'
        or plan_name like 'XB7%'
        or plan_name like 'XBAP7%'
        )
         and timestamp > current timestamp - 3 days
    group by plan_name
;x;
XB80 Search Engine
XB84 Retrieve Engine
XBR0 Search Adapter
XBR7 CORBA Interface
XB90 Dispatcher Search from Adapter
XB91 Dispatcher Search from Router
XB92 Dispatcher Search from Engine
XB93 Retrieve from Engine
XB94 Search reply / Retrieve from Router
XB95 Retrieve from Adapter
XB96 Scheduler
XB97 Restarter
XB75 Router
XB87 NDBS Search Engine