zOs/SQL/PDBGENWI
set current path oa1p;
set current schema = RR2DD;
select *
from rr2dd.TACCT_GENERAL g
where timestamp between '2012-07-30-00.00.00'
and '2012-07-31-00.00.00'
-- and connect_id in('WIS290OP', 'WIS992OP','WIS292OP')
and plan_name = 'NZ0500'
;;;;
select
date(trunc_timestamp(timestamp, 'DDD')) ,
fosfmte7(sum(occurrences)) occ,
fosFmte7(sum(class1_elapsed) / sum(fetch)) "c1Ela/fe",
fosFmte7(sum(class2_elapsed) / sum(fetch)) "c2Ela/fe",
fosFmte7(sum(class2_cpu_total) / sum(fetch)) "c2Cpu/fe",
fosFmte7(sum(class2_cpu_total)) cpu,
fosFmte7(sum(commit)) com,
fosfmte7(sum(select)) sel,
fosFmte7(sum(open)) open ,
fosFmte7(sum(fetch)) fetch,
fosFmte7(sum(fetch) /
sum(open)) "fet/op" ,
sum(fetch) /
sum(open)
from rz2dd.TACCT_GENERAL g
where -- plan_name like 'WI5790%'
plan_name = 'WI5600'
and timestamp > current timestamp -37 days
group by trunc_timestamp(timestamp, 'DDD')
order by 1 desc
with ur for read only
; x
select *
from TACCT_GENERAL g
where -- plan_name like 'WI5790%'
plan_name like 'WI8781%'
and timestamp > current timestamp - 12 days
;;;
select date(timestamp), sum(ASYNCH_IXL_REQU), sum(cLASS3_ASYNCH_IXL)
from TACCT_GENERAL g
where timestamp > '2011-07-01-00.00.00'
group by date(timestamp)
order by 1 desc
;;;
select timestamp,
class1_elapsed,
class2_elapsed,
CLASS3_SYNC_IO,
fosFmte7((CLASS3_SYNC_IO)
/ (SYNC_IO_SUSP)) "synIOTi",
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",
g.*
from TACCT_GENERAL g
where plan_name like 'WI5950%'
and timestamp > current timestamp - 11 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
;