zOs/SQL/PDBGENIO
with a as
(
select (CLASS3_SYNC_IO+CLASS3_DB_IO+CLASS3_LOG_WRT_IO
+CLASS3_OTHER_READ + OTHER_WRITE_SUSP) / CLASS1_ELAPSED
* min(1800, CLASS1_ELAPSED) io30,
CLASS3_SYNC_IO+CLASS3_DB_IO+CLASS3_LOG_WRT_IO
+CLASS3_OTHER_READ + OTHER_WRITE_SUSP ioTo, t.*
from rz2xx.tacct_general t
where timestamp between '2010-07-10-23.10.00'
and '2010-07-11-05.00.00'
and timestamp -class1_elapsed seconds
between '2010-07-10-22.55.00'
and '2010-07-10-23.25.00'
)
select plan_name, sum(io30), sum(ioTo), count(*),
min(timestamp), max(timestamp)
from a
group by plan_name
having sum(io30) > 10
order by 2 desc
;
;x;
select (CLASS3_SYNC_IO+CLASS3_DB_IO+CLASS3_LOG_WRT_IO
+CLASS3_OTHER_READ + OTHER_WRITE_SUSP) / CLASS1_ELAPSED
* min(1800, CLASS1_ELAPSED),
CLASS3_SYNC_IO+CLASS3_DB_IO+CLASS3_LOG_WRT_IO
+CLASS3_OTHER_READ + OTHER_WRITE_SUSP, t.*
from rz2xx.tacct_general t
where timestamp between '2010-07-10-23.00.00'
and '2010-07-11-00.59.00'
order by 1 desc
;
set current path oa1p;
set current schema = A540769;
with g as
(
select date(timestamp) g1,
case when timestamp between '1919-05-19-11.00.00'
and '1919-05-19-12.30.00' then 'o'
else ' ' end g2,
g.*
from TACCT_GENERAL g
)
select min(timestamp - class1_elapsed seconds) "von",
time(max(timestamp)) "bis",
fosFmte7(sum(class1_elapsed)) "ela sec",
fosFmte7(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del,
oa1p.fosFmtE7(sum(commit)) com,
oa1p.fosFmtE7(sum(class2_elapsed)/sum(insert+update)) "ela/i+u" ,
fosFmte7(sum(class2_cpu_total) / sum(insert+update)) "cpu/i+u",
fosFmte7(sum(CLASS3_LOCK_LATCH)/sum(insert+update)) "LocLat/i+u",
fosFmte7(sum(CLASS3_SYNC_IO)/sum(insert+update)) "syncIO/i+u",
count(distinct subsystem_id) "#ssid", plan_name, id,
fosFmte7(sum(CLASS3_LOG_WRT_IO) /sum(insert+update)) "logWa/iu",
fosFmte7(sum(real(Log_RECS_WRITTEN ))
/sum(insert+update)) "logRe/iu",
fosFmte7(sum(fosRealK(BYTES_PER_LOG_REC))
/sum(insert+update)) "logBy/i+u"
-- min(SUBSYSTEM_ID), max(SUBSYSTEM_ID)
from g
where -- timestamp > current timestamp - 9992 month
rz = 'RZ1' and id like 'ni250%'
-- and connect_ID like 'NI229%'
-- and plan_name in ( 'NI5310', 'NI5340')
-- and insert >= 1
group by g1, g2, id, plan_name
-- having sum(insert+update) > 0
order by id, 1 desc
with ur
;
with g as
(
select date(timestamp) g1,
case when timestamp between '1919-05-19-11.00.00'
and '1919-05-19-12.30.00' then 'o'
else ' ' end g2,
g.*
from TACCT_GENERAL g
)
select min(timestamp - class1_elapsed seconds) "von",
time(max(timestamp)) "bis",
fosFmte7(sum(class1_elapsed)) "ela sec",
fosFmte7(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del,
oa1p.fosFmtE7(sum(commit)) com,
oa1p.fosFmtE7(sum(class2_elapsed)) "c2ela" ,
fosFmte7(sum(class2_cpu_total) ) "c2cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "LocLat",
fosFmte7(sum(CLASS3_SYNC_IO)) "syncIO",
count(distinct subsystem_id) "#ssid", plan_name, id,
fosFmte7(sum(CLASS3_LOG_WRT_IO) ) "logWa",
fosFmte7(sum(real(Log_RECS_WRITTEN ))) "logRecs",
fosFmte7(sum(fosRealK(BYTES_PER_LOG_REC))) "logByte"
-- min(SUBSYSTEM_ID), max(SUBSYSTEM_ID)
from g
where -- timestamp > current timestamp - 9992 month
rz = 'RZ1' and id like 'ni250Loa%'
-- and connect_ID like 'NI229%'
-- and plan_name in ( 'NI5310', 'NI5340')
-- and insert >= 1
group by g1, g2, id, plan_name
-- having sum(insert+update) > 0
order by id, 1 desc
with ur
;
X;
with g as
(
select date(timestamp) g1,
case when timestamp between '2019-05-19-11.00.00'
and '2019-05-19-12.30.00'
or timestamp between '2019-05-19-13.00.00'
and '2019-05-19-13.30.00' then 'a'
when timestamp between '2019-05-19-16.00.00'
and '2019-05-19-23.00.00' then 'b'
when timestamp between '2019-04-14-21.50.00'
and '2019-04-14-23.20.00' then 'c'
else ' ' end g2,
g.*
from TACCT_GENERAL g
where not timestamp between '2010-05-19-12.30.00'
and '2010-05-19-13.00.00'
)
select min(timestamp - class1_elapsed seconds) "von",
time(max(timestamp)) "bis",
fosFmte7(sum(class1_elapsed)) "ela sec",
fosFmte7(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del,
oa1p.fosFmtE7(sum(commit)) com,
oa1p.fosFmtE7(sum(class2_elapsed)/sum(insert+update)) "ela/i+u" ,
fosFmte7(sum(class2_cpu_total) / sum(insert+update)) "cpu/i+u",
fosFmte7(sum(CLASS3_LOCK_LATCH)/sum(insert+update)) "LocLat/i+u",
fosFmte7(sum(CLASS3_SYNC_IO)/sum(insert+update)) "syncIo/i+u",
count(distinct subsystem_id) "#ssid", plan_name,
min(SUBSYSTEM_ID), max(SUBSYSTEM_ID)
from g
where timestamp > current timestamp - 9992 month
and rz = 'RR2' and id like 'ni250%'
-- and connect_ID like 'NI229%'
and plan_name in ( 'NI5310', 'NI5340')
-- and insert >= 1
group by id
having sum(insert+update) > 0
order by plan_Name, 1 desc
with ur
;
;x;
xet current path oa1p;
set current schema = RZ2DD;
select date(timestamp),
fosFmte7(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del,
oa1p.fosFmtE7(sum(class2_elapsed)/sum(insert+update)) "ela/i+u" ,
fosFmte7(sum(class2_cpu_total) / sum(insert+update)) "cpu/i+u",
fosFmte7(sum(CLASS3_LOCK_LATCH)/sum(insert+update)) "LocLat/i+u",
fosFmte7(sum(CLASS3_SYNC_IO)/sum(insert+update)) "syncIo/i+u"
from RZ2dd.TACCT_GENERAL g
where timestamp > current timestamp - 1 month
-- and connect_ID like 'NI229%'
and plan_name = 'NI5340'
-- and insert >= 1
group by timestamp
ORDER BY Timestamp desc
with ur
;
X
select plan_name,
fosFmte7(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del,
min(timestamp), max(timestamp)
from TACCT_GENERAL g
where timestamp > '2010-03-21-00.30.00.000'
and timestamp < '2010-03-21-02.30.00.000'
-- and connect_ID like 'NI229%'
-- and plan_name = 'NI5340'
-- and insert >= 1
group by plan_name
order by sum(select+insert+update+delete) desc
with ur
;
X
select sum(select) sel , sum(insert) ins, sum(update) upd,
sum(class2_elapsed) / sum(insert) "ela/ins" ,
sum(class2_cpu_total) / sum(insert) "cpu/ins",
sum(LOCK_LATCH_SUSP),
sum(CLASS3_LOCK_LATCH)/sum(insert) "LocLat/ins",
sum(SYNC_IO_SUSP),
sum(CLASS3_SYNC_IO)/sum(insert) "syncIo/ins",
sum(occurrences) occ,
timestamp
from RZ2hh.TACCT_GENERAL g
where timestamp > current timestamp - 2 DAY
-- and connect_ID like 'NI229%'
and plan_name = 'NI5340'
-- and insert >= 1
ORDER BY Timestamp desc
with ur
;
X
select subsystem_id, count(*), min(timestamp), max(timestamp)
from RR2HH.TACCT_GENERAL g
-- where corrName like 'NI%'
group by subsystem_id
order by subsystem_id
with ur
;
xelect class2_elapsed,
smallInt(1000* class2_cpu_total /class2_elapsed) cpu,
smallInt(1000* class3_global_cont /class2_elapsed) "gloCon",
smallInt(1000* class3_DB_IO /class2_elapsed) "db io",
smallInt(1000* class3_LOG_WRT_IO /class2_elapsed) "logIO",
smallInt(1000* (class2_cpu_total
+ class3_global_cont
+ class3_DB_IO
+ class3_LOG_WRT_IO ) /class2_elapsed) "sum",
g.*
from RR2HH.TACCT_GENERAL g
where -- timestamp > current timestamp - 120 DAY
corrname like 'NI229%'
-- or corrname like 'NI22922%'
-- and plan_name like 'DSNUT%'
-- and insert >= 1
ORDER BY CLASS1_ELAPSED desc
;
X
select class2_elapsed / insert , class2_su_cpu / insert,
g.*
from RZ2XX.TACCT_GENERAL g
-- where timestamp > current timestamp - 1 month
where timestamp IN ('2010-02-02-01.05.50.881483'
,'2010-01-30-06.38.00.929757')
and plan_name = 'BE5020'
and insert > 500000 -- class2_elapsed >= 1000
order by 1 desc, class2_elapsed desc
;
select -- class2_elapsed / insert , class2_su_cpu / insert,
g.*
from RZ2XX.TACCT_GENERAL g
where timestamp > current timestamp - 5 DAY
and plan_name = 'RB5000'
ORDER BY Timestamp asc
;
X