zOs/SQL/PDBGENXB
set current path oa1p;
set current schema = RZ2dd;
with c as
(
select left(corrname, 4) corr,
date(trunc_timestamp(timestamp - 4 days, 'iw')) + 4 days woche,
(sum(class1_cpu_total)) c1Cpu,
(sum(class2_cpu_total)) c2Cpu,
(sum(class1_elapsed)) c1Ela,
(sum(class2_elapsed)) c2Ela,
(sum(commit)) commit,
(sum(select)) select,
(sum(open )) open,
(sum(fetch )) fetch,
(sum(insert)) insert,
(sum(update)) update,
(sum(delete)) delete,
count(*) count,
(sum(occurrences)) occ ,
count(distinct date(timestamp)) tage,
date(min(timestamp)) wocheVon,
date(min(timestamp)) wocheBis
from TACCT_GENERAL g
where group_name = 'DVBP' and plan_name = 'DSNUTIL'
and timestamp > '2012-09-28-00.00.00'
group by group_name, trunc_timestamp(timestamp - 4 days, 'iw'),
left(corrname, 4)
)
,r as
(
select row_number() over (partition by woche
order by c2Cpu desc) rng
, c.*
from c
)
select woche, corr || '*' corr,
fosFmtE7(c1Cpu) c1Cpu, fosFmtE7(c2Cpu) c2Cpu,
fosFmtE7(c1Ela) c1Ela, fosFmtE7(c2Ela) c2Ela,
fosFmtE7(commit) commit, fosFmtE7(select) select,
fosFmtE7(open) open,
fosFmtE7(fetch) fetch, fosFmtE7(insert) insert,
fosFmtE7(update) update, fosFmtE7(delete) delete,
fosFmtE7(count) count, fosFmtE7(occ) occ,
tage, wocheVon, wocheBis
from r
where rng < 4
order by woche desc, r.c2cpu desc
;;;;;
set current path oa1p;
set current schema = RZ2dd;
with p as
(
select plan_name plan,
date(trunc_timestamp(timestamp - 4 days, 'iw')) + 4 days woche,
(sum(class1_cpu_total)) c1Cpu,
(sum(class2_cpu_total)) c2Cpu,
(sum(class1_elapsed)) c1Ela,
(sum(class2_elapsed)) c2Ela,
(sum(commit)) commit,
(sum(select)) select,
(sum(open )) open,
(sum(fetch )) fetch,
(sum(insert)) insert,
(sum(update)) update,
(sum(delete)) delete,
count(*) count,
(sum(occurrences)) occ ,
count(distinct date(timestamp)) tage,
date(min(timestamp)) wocheVon,
date(min(timestamp)) wocheBis
from TACCT_GENERAL g
where group_name = 'DVBP'
and timestamp > '2012-09-28-00.00.00'
group by group_name, trunc_timestamp(timestamp - 4 days, 'iw'),
plan_name
order by group_name
)
, w as
(
select 0 rng, '*' plan , woche,
sum(c1Cpu) c1Cpu, sum(c2Cpu) c2Cpu,
sum(c1Ela) c1Ela, sum(c2Ela) c2Ela,
sum(commit) commit, sum(select) select, sum(open) open,
sum(fetch) fetch, sum(insert) insert,
sum(update) update, sum(delete) delete,
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 c2Cpu desc) rng
, p.*
from p
)
, f as
(
select * from w
union all select * from r where rng <= 12
)
select smallInt(rng) rng, plan , woche,
smallint(c1Cpu * 1000/
(select c1Cpu from w where w.woche = f.woche)) c1Prm,
smallint(c2Cpu * 1000/
(select c2Cpu from w where w.woche = f.woche)) c2Prm,
fosFmtE7(c1Cpu) c1Cpu, fosFmtE7(c2Cpu) c2Cpu,
fosFmtE7(c1Ela) c1Ela, fosFmtE7(c2Ela) c2Ela,
fosFmtE7(commit) commit, fosFmtE7(select) select,
fosFmtE7(open) open,
fosFmtE7(fetch) fetch, fosFmtE7(insert) insert,
fosFmtE7(update) update, fosFmtE7(delete) delete,
fosFmtE7(count) count, fosFmtE7(occ) occ,
tage, wocheVon, wocheBis
from f
order by f.plan, f.woche desc
;x
set current path oa1p;
set current schema = RZ2dd;
with p as
(
select plan_name plan,
date(trunc_timestamp(timestamp - 4 days, 'iw')) + 4 days woche,
(sum(class1_cpu_total)) c1Cpu,
(sum(class2_cpu_total)) c2Cpu,
(sum(class1_elapsed)) c1Ela,
(sum(class2_elapsed)) c2Ela,
(sum(commit)) commit,
(sum(select)) select,
(sum(open )) open,
(sum(fetch )) fetch,
(sum(insert)) insert,
(sum(update)) update,
(sum(delete)) delete,
count(*) count,
(sum(occurrences)) occ ,
count(distinct date(timestamp)) tage,
date(min(timestamp)) wocheVon,
date(min(timestamp)) wocheBis
from TACCT_GENERAL g
where group_name = 'DVBP'
and timestamp > '2012-09-28-00.00.00'
group by group_name, trunc_timestamp(timestamp - 4 days, 'iw'),
plan_name
order by group_name
)
, w as
(
select 0 rng, '*' plan , woche,
sum(c1Cpu) c1Cpu, sum(c2Cpu) c2Cpu,
sum(c1Ela) c1Ela, sum(c2Ela) c2Ela,
sum(commit) commit, sum(select) select, sum(open) open,
sum(fetch) fetch, sum(insert) insert,
sum(update) update, sum(delete) delete,
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 c2Cpu desc) rng
, p.*
from p
)
, f as
(
select * from w
union all select * from r where rng <= 12
)
select smallInt(rng) rng, plan , woche,
fosFmtE7(c1Cpu) c1Cpu, fosFmtE7(c2Cpu) c2Cpu,
fosFmtE7(c1Ela) c1Ela, fosFmtE7(c2Ela) c2Ela,
fosFmtE7(commit) commit, fosFmtE7(select) select,
fosFmtE7(open) open,
fosFmtE7(fetch) fetch, fosFmtE7(insert) insert,
fosFmtE7(update) update, fosFmtE7(delete) delete,
fosFmtE7(count) count, fosFmtE7(occ) occ,
tage, wocheVon, wocheBis
from f
order by f.woche desc, f.c2Cpu desc
;x
wocheVon, c1Cpu, c2Cpu, c1Ela, c2Ela,
commit, select, open, fetch, insert, update, delete,
count, occ, tage, wocheBis
select plan_name, corrName, count(*), sum(class2_cpu_total)
from TACCT_GENERAL g
where ( plan_name like 'XB%'
or corrName like 'XB%'
) and timestamp > '2012-07-06-00.00.00'
and subsystem_id like 'DBP%'
group by plan_name, corrName
;x;
select plan_name, date(timestamp), group_name,
count(*) count,
fosFmte7(sum(class1_elapsed)) c1Ela,
fosFmte7(sum(class1_cpu_total)) c1Cpu,
fosFmte7(sum(class2_cpu_total)) c2Cpu,
fosFmte7(sum(commit)) commit,
fosFmte7(sum(class3_Sync_IO )) "c3Syn",
fosFmte7(min(CLASS3_LOCK_LATCH)) "~c3LoLa",
fosFmte7(min(class2_elapsed)) "c2Ela-",
fosFmte7(max(class2_elapsed)) "-c2Ela",
fosFmte7(min(class2_cpu_total)) "c2Cpu-",
fosFmte7(max(class2_cpu_total)) "-c2Cpu",
fosFmte7(min(class3_Sync_IO )) "c3Syn-",
fosFmte7(max(class3_Sync_Io )) "-c3Syn",
avg(commit) "~commit"
from TACCT_GENERAL g
where plan_name like 'java%'
and timestamp > '2012-06-01-00.00.00'
group by group_name, plan_name, date(timestamp)
order by group_name, plan_name
set current path oa1p;
set current schema = RZ2xx;
select *
from TACCT_GENERAL g
where plan_name = 'XBLE86'
and timestamp > '2012-06-06-00.00.00'
order by insert desc
-- order by class2_elapsed desc
fetch first 100 rows only
;;
set current schema = RZ2xx;
select substr(strip(group_name) || ' ' || strip(connect_type), 1, 9),
count(*) count,
fosFmte7(min(class1_elapsed)) "c1Ela-",
fosFmte7(max(class1_elapsed)) "-c1Ela",
fosFmte7(avg(class1_elapsed)) "~c1Ela",
fosFmte7(avg(class2_elapsed)) "~c2Ela",
fosFmte7(min(class1_cpu_total)) "~c1Cpu",
fosFmte7(min(class2_cpu_total)) "~c2Cpu",
fosFmte7(min(class3_Sync_IO )) "~c3Syn",
fosFmte7(min(CLASS3_LOCK_LATCH)) "~c3LoLa",
fosFmte7(min(class2_elapsed)) "c2Ela-",
fosFmte7(max(class2_elapsed)) "-c2Ela",
fosFmte7(min(class2_cpu_total)) "c2Cpu-",
fosFmte7(max(class2_cpu_total)) "-c2Cpu",
fosFmte7(min(class3_Sync_IO )) "c3Syn-",
fosFmte7(max(class3_Sync_Io )) "-c3Syn",
avg(commit) "~commit"
from TACCT_GENERAL g
where plan_name = 'XBLE80'
and timestamp between '2010-12-03-00.00.00'
and '2010-12-04-00.00.00'
group by group_name, connect_type,
int(log10(max(class1_elapsed, 0.001)) * 2.)
order by group_name, connect_type,
min(class1_elapsed)
;x
set current schema = RZ2xx;
select *
from TACCT_GENERAL g
where plan_name = 'XBLE80'
and timestamp between '2010-12-03-00.00.00'
and '2010-12-04-00.00.00'
and class3_Sync_IO = 0
and CLASS3_LOCK_LATCH = 0
order by class2_elapsed
fetch first 100 rows only
;;
set current schema = RZ2xx;
select *
from TACCT_GENERAL g
where plan_name = 'XBLE80'
and class2_Elapsed > 50
order by class2_Elapsed desc
;x
select timestamp ,
fosFmte7(sum(class2_elapsed)
/ sum(0.0+select+fetch+insert+update+delete)) "c2Ela/o",
fosFmte7(sum(class2_elapsed)
/ sum(0.0+commit)) "c2Ela/c",
fosFmte7(sum(0.0+ select+fetch+insert+update+delete)) "opsSum",
fosFmte7(sum(class2_cpu_total)
/ sum(0.0+select+fetch+insert+update+delete)) "c2Cpu/o",
fosFmte7(sum(CLASS3_LOCK_LATCH)
/ sum(0.0+select+fetch+insert+update+delete)) "locLa/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(0.0+select+fetch+insert+update+delete)) "synIO/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(SYNC_IO_SUSP)) "synIOTi",
fosFmte7(sum(class1_elapsed)) "c1Ela",
fosFmte7(sum(class2_elapsed)) "c2Ela",
fosFmte7(sum(class2_cpu_total)) "c2Cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "lockLat",
fosFmte7(sum(CLASS3_SYNC_IO)) "syncIo",
fosFmte7(sum(occurrences)) occ,
fosFmtE7(sum(0+select)) sel,
fosFmtE7(sum(open)) open,
fosFmtE7(sum(fetch)) fetch,
fosFmtE7(sum(insert)) ins,
fosFmtE7(sum(update)) upd,
fosFmtE7(sum(delete)) del,
fosFmtE7(sum(commit)) commit
from (select g.*, commit coms
from TACCT_GENERAL g) g
where plan_name = 'XBLE80'
and timestamp > current timestamp - 5 days
group by timestamp
-- order by min(timestamp) desc
order by real(sum(class2_elapsed)) / sum(coms) desc
fetch first 100 rows only
with ur
; x
set current schema = RZ2MM;
select timestamp,
fosFmte7((class2_elapsed)
/ (0.0+select+fetch+insert+update+delete)) "c2Ela/o",
fosFmte7((class2_cpu_total)
/ (0.0+select+fetch+insert+update+delete)) "c2Cpu/o",
fosFmte7((CLASS3_LOCK_LATCH)
/ (0.0+select+fetch+insert+update+delete)) "locLa/o",
fosFmte7((CLASS3_SYNC_IO)
/ (0.0+select+fetch+insert+update+delete)) "synIO/o",
fosFmte7((CLASS3_LOG_WRT_IO)
/ (0.0+select+fetch+insert+update+delete)) "logWr/o",
fosFmte7((CLASS3_SYNC_IO)
/ (SYNC_IO_SUSP)) "synIOTi",
commit,
insert,
(0.0+select+fetch+insert+update+delete) "sqls",
g.*
from TACCT_GENERAL g
where plan_name = 'XBLE80' -- and insert/occurrences > 100000
and timestamp > current timestamp - 5 days
order by class2_elapsed
/ (0.0+select+fetch+insert+update+delete) desc
fetch first 300 rows only
with ur
; x
select timestamp,
fosFmte7((class2_elapsed)
/ (0.0+select+fetch+insert+update+delete)) "c2Ela/o",
fosFmte7((class2_cpu_total)
/ (0.0+select+fetch+insert+update+delete)) "c2Cpu/o",
fosFmte7((CLASS3_LOCK_LATCH)
/ (0.0+select+fetch+insert+update+delete)) "locLa/o",
fosFmte7((CLASS3_SYNC_IO)
/ (0.0+select+fetch+insert+update+delete)) "synIO/o",
fosFmte7((CLASS3_SYNC_IO)
/ (SYNC_IO_SUSP)) "synIOTi",
g.*
from TACCT_GENERAL g
where plan_name = 'XB5000'
-- and timestamp > current timestamp - 5 days
order by class2_elapsed desc
fetch first 300 rows only
with ur
; x
set current schema = RZ2DD;
select date(timestamp), class1_elapsed, class2_elapsed,
fosFmte7(occurrences) occ,
fosFmtE7( (select)) sel,
fosFmtE7( (insert)) ins,
update upd,
g.*
from TACCT_GENERAL g
where plan_name = 'BE5000'
and timestamp > current timestamp - 5 days
order by class2_elapsed desc
with ur
;
set current schema = RZ2xx;
select timestamp ,
fosFmte7(sum(class2_elapsed)
/ sum(select+fetch+insert+update+delete)) "c2Ela/o",
fosFmte7(sum(class2_cpu_total)
/ sum(select+fetch+insert+update+delete)) "c2Cpu/o",
fosFmte7(sum(CLASS3_LOCK_LATCH)
/ sum(select+fetch+insert+update+delete)) "locLa/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(select+fetch+insert+update+delete)) "synIO/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(SYNC_IO_SUSP)) "synIOTi",
fosFmte7(sum(class1_elapsed)) "c1Ela",
fosFmte7(sum(class2_elapsed)) "c2Ela",
fosFmte7(sum(class2_cpu_total)) "c2Cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "lockLat",
fosFmte7(sum(CLASS3_SYNC_IO)) "syncIo",
fosFmte7(sum(occurrences)) occ,
fosFmtE7(sum(select)) sel,
fosFmtE7(sum(open)) open,
fosFmtE7(sum(fetch)) fetch,
fosFmtE7(sum(insert)) ins,
fosFmtE7(sum(update)) upd,
fosFmtE7(sum(delete)) del
from TACCT_GENERAL g
where plan_name = 'BE5000'
and timestamp > current timestamp - 5 days
group by timestamp
order by sum(class2_elapsed) desc
fetch first 100 rows only
with ur
;
set current schema = RZ2xx;
select date(timestamp),
fosFmte7(sum(class2_elapsed)) "c2ElaSu",
fosFmte7(max(class2_elapsed)) "c2ElaMa",
fosFmte7(sum(select+fetch+insert+update+delete)) "opsSum",
fosFmte7(max(select+fetch+insert+update+delete)) "opsMax",
fosFmte7(sum(class2_cpu_total)
/ sum(select+fetch+insert+update+delete)) "c2Cpu/o",
fosFmte7(sum(CLASS3_LOCK_LATCH)
/ sum(select+fetch+insert+update+delete)) "locLa/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(select+fetch+insert+update+delete)) "synIO/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(SYNC_IO_SUSP)) "synIOTi",
fosFmte7(sum(class1_elapsed)) "c1Ela",
fosFmte7(sum(class2_elapsed)) "c2Ela",
fosFmte7(sum(class2_cpu_total)) "c2Cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "lockLat",
fosFmte7(sum(CLASS3_SYNC_IO)) "syncIo",
fosFmte7(sum(occurrences)) occ,
fosFmtE7(sum(select)) sel,
fosFmtE7(sum(open)) open,
fosFmtE7(sum(fetch)) fetch,
fosFmtE7(sum(insert)) ins,
fosFmtE7(sum(update)) upd,
fosFmtE7(sum(delete)) del
from TACCT_GENERAL g
where plan_name = 'BE5000'
and timestamp > current timestamp - 5 days
group by date(timestamp)
order by sum(class2_elapsed) desc
fetch first 100 rows only
with ur
;
set current schema = RZ2MM;
select min(timestamp), max(timestamp)
from TACCT_GENERAL g
;;
select timestamp,
fosFmte7(sum(class2_elapsed)
/ sum(select+fetch+insert+update+delete)) "c2Ela/o",
fosFmte7(sum(class2_cpu_total)
/ sum(select+fetch+insert+update+delete)) "c2Cpu/o",
fosFmte7(sum(CLASS3_LOCK_LATCH)
/ sum(select+fetch+insert+update+delete)) "locLa/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(select+fetch+insert+update+delete)) "synIO/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(SYNC_IO_SUSP)) "synIOTi",
fosFmte7(sum(class1_elapsed)) "c1Ela",
fosFmte7(sum(class2_elapsed)) "c2Ela",
fosFmte7(sum(class2_cpu_total)) "c2Cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "lockLat",
fosFmte7(sum(CLASS3_SYNC_IO)) "syncIo",
fosFmte7(sum(occurrences)) occ,
fosFmtE7(sum(select)) sel,
fosFmtE7(sum(open)) open,
fosFmtE7(sum(fetch)) fetch,
fosFmtE7(sum(insert)) ins,
fosFmtE7(sum(update)) upd,
fosFmtE7(sum(delete)) del
from TACCT_GENERAL g
where plan_name = 'BE5000'
-- and timestamp > current timestamp - 10 days
group by timestamp
order by sum(class2_elapsed) desc
fetch first 1000 rows only
with ur
; x
select *
from TACCT_GENERAL g
where timestamp > current timestamp - 3 days
and ( corrName like 'MFT15%'
or corrName like 'MFT18%'
or corrName like 'MFT56%'
or connect_id like 'MFT56%'
or corrName = 'MFT3100P'
)
order by timestamp desc
with ur
;