zOs/SQL/PDBGENBE
set current path oa1p;
set current schema = RZ2MM;
select *
from tacct_general
-- where corrName = 'BES0404P'
where plan_Name = 'BE5000'
and timestamp = '2011-06-30-09.53.00.000000'
-- and timestamp between '2011-06-30-09.00.00'
-- and '2011-06-30-11.00.00'
order by timestamp
with ur
;;;
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(select+fetch+insert+update+delete)
/ sum(commit)) "ops/com",
fosFmte7(sum(LOG_WRT_IO_SUSP)
/ sum(commit)) "lIO/com",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(SYNC_IO_SUSP)) "synIOTi",
fosFmte7(sum(CLASS3_DB_IO)
/ sum(DB_IO_SUSP)) "dbIOTi",
fosFmte7(sum(class3_LOG_WRT_IO)
/ sum(LOG_WRT_IO_SUSP)) "logIOTi",
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_PAGE_LATCH)
/ sum(select+fetch+insert+update+delete)) "pgLat/o",
fosFmte7(sum(CLASS3_SYNC_IO)
/ sum(select+fetch+insert+update+delete)) "synIO/o",
fosFmte7(sum(CLASS3_LOG_WRT_io)
/ sum(select+fetch+insert+update+delete)) "logIO/o",
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(deadlock)) "deadlock",
fosFmte7(sum(timeout)) "timeout",
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 1=1 and plan_name = 'BE5000'
and timestamp > current timestamp - 375 days
group by date(timestamp)
order by date(timestamp) desc
-- order by sum(class2_elapsed) desc
fetch first 1000 rows only
with ur
; x
set current path oa1p;
set current schema = RZ2MM;
select timestamp,
fosFmte7((class3_LOG_WRT_IO)
/ (LOG_WRT_IO_SUSP)) "logIOTi",
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",
fosFmte7((CLASS3_DB_IO)
/ (DB_IO_SUSP)) "dbIOTi",
fosFmte7((class3_LOG_WRT_IO)
/ (LOG_WRT_IO_SUSP)) "logIOTi",
g.*
from TACCT_GENERAL g
where plan_name = 'BE5000' and insert > 200000
-- and timestamp > current timestamp - 30 days
-- order by class2_elapsed desc
order by class3_LOG_WRT_IO
/ (LOG_WRT_IO_SUSP) 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
;