zOs/SQL/PDBGENVZ
set current path = oa1p;
set current path oa1p;
set current schema = RZ2XX;
select timestamp, class1_elapsed, class3_drain_lock, g.*
from TACCT_GENERAL g
where -- timestamp > current timestamp - 2 days
timestamp between '2011-04-03-06.00.00'
and '2011-04-03-07.00.00'
-- and class1_elapsed > 60
-- and rollback > 0
-- and ( corrName like 'QR00315%'
-- or corrName like 'QR00316%'
-- )
and plan_name = 'VZ52C'
-- and CLASS3_DRAIN_LOCK < 10
-- order by timestamp desc
-- order by class1_elapsed desc
-- group by floor(2*log10(class1_elapsed))
order by class1_elapsed desc
fetch first 100 rows only
with ur
;;;
select count(*), dec(avg(class1_elapsed), 8, 4) "c1ElaAvg",
dec(min(class1_elapsed), 8, 4) "c1ElaMin",
dec(max(class1_elapsed), 8, 4) "c1ElaMax"
, fosFmte7(sum(real(select)) /sum(occurrences)) "select"
, fosFmte7(sum(real(fetch )) /sum(occurrences)) "fetch"
, fosFmte7(sum(real(open )) /sum(occurrences)) "open"
, fosFmte7(sum(real(insert)) /sum(occurrences)) "insert"
, fosFmte7(sum(real(update)) /sum(occurrences)) "update"
, fosFmte7(sum(real(delete)) /sum(occurrences)) "delete"
, fosFmte7(sum(real(commit))/sum(occurrences)) "comm"
, fosFmte7(sum(real(rollback))/sum(occurrences)) "rollbac"
, fosFmte7(sum(real(prepare))/sum(occurrences)) "prepare"
, fosFmte7(sum(real(incremental_bind))/sum(occurrences)) "incBind"
, fosFmte7(sum(class1_cpu_total)/sum(occurrences)) "c1Cpu"
, fosFmte7(sum(class2_cpu_total)/sum(occurrences)) "c2Cpu"
, fosFmte7(sum(CLASS3_Sync_IO)/sum(occurrences)) "syIO/oc"
, fosFmte7(sum(log_recs_written)/sum(occurrences)) "logRecs"
, fosFmte7(sum(CLASS3_Lock_Latch) /sum(occurrences)) "c3LoL/oc"
, fosFmte7(sum(CLASS3_GLOBAL_CONT)/sum(occurrences)) "c3Glo/oc"
, fosFmte7(sum(CLASS3_LLOCK_CHILD)/sum(occurrences)) "c3LCh/oc"
, min(timestamp), max(timestamp)
from TACCT_GENERAL g
where -- timestamp > current timestamp - 2 days
-- timestamp between '2011-01-23-09.53.00'
-- and '2011-01-23-09.54.43'
-- and class1_elapsed > 60
-- and rollback > 0
-- and ( corrName like 'QR00315%'
-- or corrName like 'QR00316%'
-- )
plan_name = 'VZ52C'
and CLASS3_DRAIN_LOCK < 10
-- order by timestamp desc
-- order by class1_elapsed desc
group by floor(2*log10(class1_elapsed))
order by 4
fetch first 100 rows only
with ur
;;;
select
timestamp
, fosFmte7((occurrences)) "occs"
, fosFmte7((CLASS3_Sync_IO)/(occurrences)) "syIO/oc"
, fosFmte7((CLASS3_LLOCK_CHILD)/(occurrences)) "c3LCh/oc"
, fosFmte7((class1_elapsed)/(occurrences)) "c1ela"
, fosFmte7((class2_elapsed)/(occurrences)) "c2ela"
, fosFmte7((class1_cpu_total)/(occurrences)) "c1Cpu"
, fosFmte7((class2_cpu_total)/(occurrences)) "c2Cpu"
, fosFmte7((CLASS3_Lock_Latch) /(occurrences)) "c3LoL/oc"
, fosFmte7((CLASS3_GLOBAL_CONT)/(occurrences)) "c3Glo/oc"
, fosFmte7((CLASS3_LLOCK_CHILD)/(occurrences)) "c3LCh/oc"
, fosFmte7((real(commit))/(occurrences)) "comm"
, fosFmte7((real(rollback))/(occurrences)) "roll"
, fosFmte7((real(select))/(occurrences)) "sele"
, fosFmte7((insert)/(occurrences)) "insert"
, g.*
from RZ2MM.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp between '2011-01-25-07.00.00'
and '2011-01-25-11.00.00'
order by clASS1_elapsed / occurrences desc
with ur
;;;;
select
substr(char(trunc_timestamp(timestamp, 'mi')), 1, 16)
, (count(*)) "count"
, fosFmte7(sum(occurrences)) "occs"
, fosFmte7(sum(CLASS3_Sync_IO)/sum(occurrences)) "syIO/oc"
, fosFmte7(sum(CLASS3_LLOCK_CHILD)/sum(occurrences)) "c3LCh/oc"
, fosFmte7(sum(class1_elapsed)/sum(occurrences)) "c1ela"
, fosFmte7(sum(class2_elapsed)/sum(occurrences)) "c2ela"
, fosFmte7(sum(class1_cpu_total)/sum(occurrences)) "c1Cpu"
, fosFmte7(sum(class2_cpu_total)/sum(occurrences)) "c2Cpu"
, fosFmte7(sum(CLASS3_Lock_Latch) /sum(occurrences)) "c3LoL/oc"
, fosFmte7(sum(CLASS3_GLOBAL_CONT)/sum(occurrences)) "c3Glo/oc"
, fosFmte7(sum(CLASS3_LLOCK_CHILD)/sum(occurrences)) "c3LCh/oc"
, fosFmte7(sum(real(commit))/sum(occurrences)) "comm"
, fosFmte7(sum(real(rollback))/sum(occurrences)) "roll"
, fosFmte7(sum(real(select))/sum(occurrences)) "sele"
, fosFmte7(sum(insert)/sum(occurrences)) "insert"
, (sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences < 10
then occurrences else 0 end)) "occ>=40"
, fosFmte7(sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences < 10
then class1_elapsed else 0 end)) "ela>=40"
, fosFmte7(sum(class1_elapsed)) "ela"
, (sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences >= 10
then occurrences else 0 end)) "occ>=40Dr"
, fosFmte7(sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences >= 10
then class1_elapsed else 0 end)) "ela>=40Dr"
from RZ2MM.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp between '2011-01-25-07.00.00'
and '2011-01-25-11.00.00'
-- and CLASS3_DRAIN_LOCK / occurrences < 10
group by trunc_timestamp(timestamp, 'mi')
-- order by trunc_timestamp(timestamp, 'mi') asc
order by sum(clASS1_elapsed)/sum(occurrences) desc
with ur
;;;
select
dayOfWeek_iso(max(timestamp)) "Son=1" ,
substr(char(trunc_timestamp(timestamp, 'dd')), 1, 16)
, (count(*)) "count"
, fosFmte7(sum(occurrences)) "occs"
, fosFmte7(sum(class1_elapsed)) "ela"
from RZ2dd.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp > '2010-01-01-00.00.00'
-- and '2011-01-26-10.00.00'
-- and CLASS3_DRAIN_LOCK / occurrences < 10
group by trunc_timestamp(timestamp, 'dd')
order by trunc_timestamp(timestamp, 'dd') desc
with ur
;;;
set current path = oa1p;
select
substr(char(trunc_timestamp(timestamp, 'hh')), 1, 16)
, (count(*)) "count"
, (sum(occurrences)) "occs"
, fosFmte7(sum(class1_elapsed)) "ela"
, (sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences < 10
then occurrences else 0 end)) "occ>=40"
, fosFmte7(sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences < 10
then class1_elapsed else 0 end)) "ela>=40"
, fosFmte7(sum(class1_elapsed)) "ela"
, (sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences >= 10
then occurrences else 0 end)) "occ>=40Dr"
, fosFmte7(sum(case when class1_elapsed/occurrences >= 40
and CLASS3_DRAIN_LOCK / occurrences >= 10
then class1_elapsed else 0 end)) "ela>=40Dr"
from RZ2xx.TACCT_GENERAL g
where plan_name = 'VZ52C'
-- and timestamp > '2011-02-08-08.00.00'
-- and '2011-01-26-10.00.00'
-- and CLASS3_DRAIN_LOCK / occurrences < 10
group by trunc_timestamp(timestamp, 'hh')
order by trunc_timestamp(timestamp, 'hh') desc
with ur
;;;
set current path = oa1p;
select
substr(char(trunc_timestamp(timestamp, 'mi')), 1, 16)
, smallint(count(*)) cnt
, smallint(sum(case when class1_elapsed/occurrences >= 100
then 1 else 0 end)) ">=100"
, smallint(sum(case when class1_elapsed/occurrences < 100
and class1_elapsed/occurrences >= 30
then 1 else 0 end)) ">=30"
, smallint(sum(case when class1_elapsed/occurrences < 30
and class1_elapsed/occurrences >= 10
then 1 else 0 end)) ">=10"
, smallint(sum(case when class1_elapsed/occurrences < 10
and class1_elapsed/occurrences >= 3
then 1 else 0 end)) ">=3"
, smallint(sum(case when class1_elapsed/occurrences < 3
and class1_elapsed/occurrences >= 1
then 1 else 0 end)) ">=1"
, smallint(sum(case when class1_elapsed/occurrences < 1
and class1_elapsed/occurrences >= 0.3
then 1 else 0 end)) ">=0.3"
, smallint(sum(case when class1_elapsed/occurrences < 0.3
and class1_elapsed/occurrences >= 0.1
then 1 else 0 end)) ">=0.1"
, smallint(sum(case when class1_elapsed/occurrences < 0.1
and class1_elapsed/occurrences >= 0.03
then 1 else 0 end)) ">=0.03"
, smallint(sum(case when class1_elapsed/occurrences < 0.03
and class1_elapsed/occurrences >= 0.01
then 1 else 0 end)) ">=0.01"
, smallint(sum(case when class1_elapsed/occurrences < 0.01
and class1_elapsed/occurrences >= 0.003
then 1 else 0 end)) ">=0.003"
, smallint(sum(case when class1_elapsed/occurrences < 0.003
and class1_elapsed/occurrences >= 0.001
then 1 else 0 end)) ">=0.001"
, smallint(sum(case when class1_elapsed/occurrences < 0.001
-- and class1_elapsed/occurrences >= 0.001
then 1 else 0 end)) "< 0.001"
, fosFmte7(sum(class1_elapsed)/sum(occurrences)) "c1Ela/oc"
, fosFmte7(sum(class2_elapsed)/sum(occurrences)) "c2Ela/oc"
, fosFmte7(sum(CLASS3_Lock_Latch) /sum(occurrences)) "c3LoL/oc"
, fosFmte7(sum(CLASS3_GLOBAL_CONT)/sum(occurrences)) "c3Glo/oc"
, fosFmte7(sum(CLASS3_LLOCK_CHILD)/sum(occurrences)) "c3LCh/oc"
, fosFmte7(sum(class2_cpu_total)/sum(occurrences)) "c2Cpu/oc"
, fosFmte7(sum(commit)) "comm"
, fosFmte7(sum(rollback)) "roll"
from RZ2xx.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp between '2011-01-26-08.00.00'
and '2011-01-26-10.00.00'
group by trunc_timestamp(timestamp, 'mi')
order by trunc_timestamp(timestamp, 'mi')
with ur
;;;
select timestamp
-- , subsystem_id
, substr(strip(min(subsystem_id))
|| case when min(subsystem_id) = max(subsystem_id) then ''
else '*' || substr(max(subsystem_id), 4, 1) end, 1, 6)
, fosFmte7(sum(occurrences)) "occ"
, fosFmte7(sum(class1_elapsed)/sum(occurrences)) "c1Ela/oc"
, fosFmte7(sum(class2_elapsed)/sum(occurrences)) "c2Ela/oc"
, fosFmte7(sum(CLASS3_Lock_Latch) /sum(occurrences)) "c3LoL/oc"
, fosFmte7(sum(CLASS3_GLOBAL_CONT)/sum(occurrences)) "c3Glo/oc"
, fosFmte7(sum(CLASS3_LLOCK_CHILD)/sum(occurrences)) "c3LCh/oc"
, fosFmte7(sum(class2_cpu_total)/sum(occurrences)) "c2Cpu/oc"
, fosFmte7(sum(fetch)) "fetch"
, fosFmte7(sum(class1_elapsed)) "c1Ela"
, fosFmte7(sum(class2_elapsed)) "c2Ela"
, fosFmte7(sum(CLASS3_LOCK_LATCH)) "c3LoLa"
, fosFmte7(sum(class1_cpu_total)) "c1Cpu"
, fosFmte7(sum(class2_cpu_total)) "c2Cpu"
, fosFmte7(sum(commit)) "comm"
, fosFmte7(sum(rollback)) "roll"
-- , g.*
from RZ2mm.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp between '2011-01-25-09.00.00'
and '2011-01-25-09.20.00'
group by timestamp
order by timestamp
with ur
;;;
select *
from RZ2xx.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp between '2011-01-25-09.00.00'
and '2011-01-25-09.20.00'
order by class2_elapsed/occurrences desc
with ur
;;;
select timestamp
-- , subsystem_id
, substr(strip(min(subsystem_id))
|| case when min(subsystem_id) = max(subsystem_id) then ''
else '*' || substr(max(subsystem_id), 4, 1) end, 1, 6)
, fosFmte7(sum(occurrences)) "occ"
, fosFmte7(sum(class2_elapsed)/sum(occurrences)) "c2Ela/oc"
, fosFmte7(sum(CLASS3_DRAIN_LOCK) /sum(occurrences)) "c3Dra/oc"
, fosFmte7(sum(class2_cpu_total)/sum(occurrences)) "c2Cpu/oc"
, fosFmte7(sum(fetch)) "fetch"
, fosFmte7(sum(class1_elapsed)) "c1Ela"
, fosFmte7(sum(class2_elapsed)) "c2Ela"
, fosFmte7(sum(CLASS3_DRAIN_LOCK)) "c3Dra"
, fosFmte7(sum(class1_cpu_total)) "c1Cpu"
, fosFmte7(sum(class2_cpu_total)) "c2Cpu"
-- , g.*
from RZ2mm.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp between '2011-01-25-08.00.00'
and '2011-01-25-11.00.00'
group by timestamp
order by timestamp
with ur
;;;
select count(*), sum(occurences), pck_id
from RZ2mm.TACCT_program
select *
from RZ2xx.TACCT_GENERAL g
where plan_name = 'VZ52C'
and timestamp between '2011-01-25-09.00.00'
and '2011-01-25-09.20.00'
order by class2_elapsed/occurrences desc
with ur
;;;