zOs/SQL/PDBGENX1
set current path oa1p;
select plan_name
, subsystem_id
, fosFmte7(sum(OCCURRENCES)) occ
, fosFmte7(sum(commit)) commit
, fosFmte7(sum(class1_elapsed)) c1Ela
, fosFmte7(sum(class1_cpu_total)) c1cpu
, fosFmte7(sum(class1_su_cpu )) c1su
, fosFmte7(sum(class2_elapsed)) c2Ela
, fosFmte7(sum(class2_cpu_total)) c2cpu
, fosFmte7(sum(class2_su_cpu )) c2su
, group_name
, CONNECT_TYPE
, CONNECT_ID
, CORRNAME
, PLAN_NAME
, min(TIMESTAMP)
, max(TIMESTAMP)
from RZ2dd.TACCT_GENERAL g
where plan_name in ('ER0044')
and timestamp between '2012-12-21-00.00.00'
and '2012-12-21-23.59.59'
group by date(timestamp)
, plan_name
, subsystem_id
, group_name
, CONNECT_TYPE
, CONNECT_ID
, CORRNAME
, PLAN_NAME
order by subsystem_id
;;;
with s as(
select date(timestamp) dt, hour(timestamp) hh, int(sum(commit))commit
from RZ2hh.TACCT_GENERAL g
where plan_name in ('ER0044')
and subsystem_id like 'DOF%'
and timestamp between '2012-12-01-00.00.00'
and '2012-12-31-00.00.00'
group by timestamp
)
, d as (
select dt from s group by dt
)
select d.dt,
right(' ' || value(strip(char(s00.commit)), ''), 4)
|| right(' ' || value(strip(char(s01.commit)), ''), 4)
|| right(' ' || value(strip(char(s02.commit)), ''), 4)
|| right(' ' || value(strip(char(s03.commit)), ''), 4)
|| right(' ' || value(strip(char(s04.commit)), ''), 4)
|| right(' ' || value(strip(char(s05.commit)), ''), 4)
|| right(' ' || value(strip(char(s06.commit)), ''), 4)
|| right(' ' || value(strip(char(s07.commit)), ''), 4)
|| right(' ' || value(strip(char(s08.commit)), ''), 4)
|| right(' ' || value(strip(char(s09.commit)), ''), 4)
|| right(' ' || value(strip(char(s10.commit)), ''), 4)
|| right(' ' || value(strip(char(s11.commit)), ''), 4)
|| right(' ' || value(strip(char(s12.commit)), ''), 4)
|| right(' ' || value(strip(char(s13.commit)), ''), 4)
|| right(' ' || value(strip(char(s14.commit)), ''), 4)
|| right(' ' || value(strip(char(s15.commit)), ''), 4)
|| right(' ' || value(strip(char(s16.commit)), ''), 4)
|| right(' ' || value(strip(char(s17.commit)), ''), 4)
|| right(' ' || value(strip(char(s18.commit)), ''), 4)
|| right(' ' || value(strip(char(s19.commit)), ''), 4)
|| right(' ' || value(strip(char(s20.commit)), ''), 4)
|| right(' ' || value(strip(char(s21.commit)), ''), 4)
|| right(' ' || value(strip(char(s22.commit)), ''), 4)
|| right(' ' || value(strip(char(s23.commit)), ''), 4)
from d
left join s s00 on s00.dt = d.dt and s00.hh = 00
left join s s01 on s01.dt = d.dt and s01.hh = 01
left join s s02 on s02.dt = d.dt and s02.hh = 02
left join s s03 on s03.dt = d.dt and s03.hh = 03
left join s s04 on s04.dt = d.dt and s04.hh = 04
left join s s05 on s05.dt = d.dt and s05.hh = 05
left join s s06 on s06.dt = d.dt and s06.hh = 06
left join s s07 on s07.dt = d.dt and s07.hh = 07
left join s s08 on s08.dt = d.dt and s08.hh = 08
left join s s09 on s09.dt = d.dt and s09.hh = 09
left join s s10 on s10.dt = d.dt and s10.hh = 10
left join s s11 on s11.dt = d.dt and s11.hh = 11
left join s s12 on s12.dt = d.dt and s12.hh = 12
left join s s13 on s13.dt = d.dt and s13.hh = 13
left join s s14 on s14.dt = d.dt and s14.hh = 14
left join s s15 on s15.dt = d.dt and s15.hh = 15
left join s s16 on s16.dt = d.dt and s16.hh = 16
left join s s17 on s17.dt = d.dt and s17.hh = 17
left join s s18 on s18.dt = d.dt and s18.hh = 18
left join s s19 on s19.dt = d.dt and s19.hh = 19
left join s s20 on s20.dt = d.dt and s20.hh = 20
left join s s21 on s21.dt = d.dt and s21.hh = 21
left join s s22 on s22.dt = d.dt and s22.hh = 22
left join s s23 on s23.dt = d.dt and s23.hh = 23
order by d.dt desc
;;;;
select plan_name, timestamp,
fosFmte7(sum(class1_cpu_total)) "c1cpu",
fosFmte7(sum(class2_cpu_total)) "c2cpu",
fosFmte7(sum(sqls) ) "sqls",
int(sum(commit)) "commit",
int(sum(rollback)) "rollback",
fosFmte7(sum(real(select))) "select",
fosFmte7(sum(real(open))) "open",
fosFmte7(sum(real(fetch))) "fetch",
fosFmte7(sum(real(insert))) "insert",
fosFmte7(sum(real(update))) "update",
fosFmte7(sum(real(delete))) "delete",
fosFmte7(sum(real(PROGRAMS))) "pkgs"
from s
-- group by plan_name, floor(log10(class1_elapsed/max(sqls, 1))*3)
-- order by plan_name, min(class1_elapsed/max(sqls, 1))
group by plan_name, timestamp
order by plan_name, timestamp desc
;;;
??????????????
with s as(
select real(select+open+fetch+insert+update+delete) sqls,
real(max(1, commit+rollback)) cr,
g.*
from RZ2hh.TACCT_GENERAL g
where plan_name in ('ER0044')
and subsystem_id like 'DOF%'
and timestamp between '2012-12-01-00.00.00'
and '2012-12-31-00.00.00'
)
select plan_name, timestamp,
fosFmte7(sum(class1_cpu_total)) "c1cpu",
fosFmte7(sum(class2_cpu_total)) "c2cpu",
fosFmte7(sum(sqls) ) "sqls",
int(sum(commit)) "commit",
int(sum(rollback)) "rollback",
fosFmte7(sum(real(select))) "select",
fosFmte7(sum(real(open))) "open",
fosFmte7(sum(real(fetch))) "fetch",
fosFmte7(sum(real(insert))) "insert",
fosFmte7(sum(real(update))) "update",
fosFmte7(sum(real(delete))) "delete",
fosFmte7(sum(real(PROGRAMS))) "pkgs"
from s
-- group by plan_name, floor(log10(class1_elapsed/max(sqls, 1))*3)
-- order by plan_name, min(class1_elapsed/max(sqls, 1))
group by plan_name, timestamp
order by plan_name, timestamp desc
;;;
??????????????
with s as(
select real(select+open+fetch+insert+update+delete) sqls,
real(max(1, commit+rollback)) cr,
g.*
from RZ2HH.TACCT_GENERAL g
where plan_name in ('PW5200', 'PW5220')
and subsystem_id like 'DOF%'
and timestamp between '2012-01-01-00.00.00'
and '2012-12-02-00.00.00'
)
select trunc_timestamp(timestamp,'HH'),
plan_name,
fosFmte7((class1_elapsed)/(cr) ) "c1ela/coR",
fosFmte7((class2_elapsed)/(cr) ) "c2ela/coR",
fosFmte7((class1_cpu_total)/(cr)) "c1cpu/coR",
fosFmte7((class2_cpu_total)/(cr)) "c2cpu/coR",
fosFmte7((CLASS3_ASYNCH_IXL) /(cr) ) "c3IXL/coR",
int((occurrences)) "occ",
int((commit)) "commit",
int((rollback)) "rollBack",
fosFmte7((sqls)/(cr) ) "sqls/coR",
fosFmte7((class3_global_cont)/(cr)) "c2gloCon",
fosFmte7((class3_DB_IO)/(cr)) "c3dbIo",
fosFmte7((class3_LOG_WRT_IO)/(cr)) "c2logIo",
fosFmte7((real(OPEN_CLOSE_SUSP))/(cr)) "opClo",
fosFmte7((CLASS3_OPEN_CLOSE)/(cr)) "c3opClo",
fosFmte7((real(SERV_TASK_SUSP))/(cr)) "serTa",
fosFmte7((CLASS3_SERV_TASK)/(cr)) "c3serTa",
fosFmte7((real(commit))/(cr)) "commit",
fosFmte7((real(rollback))/(cr)) "rollback",
fosFmte7((real(select))/(cr)) "select",
fosFmte7((real(open) )/(cr)) "open",
fosFmte7((real(fetch) )/(cr)) "fetch",
fosFmte7((real(insert))/(cr)) "insert",
fosFmte7((real(update))/(cr)) "update",
fosFmte7((real(delete))/(cr)) "delete",
fosFmte7((real(INCREMENTAL_BIND))/(cr))"incrBi",
fosFmte7((real(PROGRAMS)) /(cr)) "pkgs"
from s
-- group by trunc_timestamp(timestamp,'HH'), plan_name
order by plan_name, (class1_elapsed)/(cr) desc
fetch first 1000 rows only
;;;
;;;
select count(*), plan_name
from rz1xx.tacct_program
where pck_id = 'YWPR2PX' and group_Name = 'DE0G'
group by plan_name
with ur
set current path = oa1p;
with i as
(
select plan_name
, sum(class2_elapsed) ela
, sum(class2_cpu_total) cpu
, sum(commit) com
, sum(select) sel
, sum(open) op
, sum(fetch) fet
, min(timestamp) von
, max(timestamp) bis
from rz2mm.tacct_general
where timestamp > '2011-01-01 00:00:00' and commit > 0
and plan_name in ('PW0602', 'PW0408')
group by plan_name, floor( 2*log10(class2_elapsed / commit))
)
select plan_name
, fosFmte7(ela / com) "ela/com"
, fosFmte7(com) commit
, fosFmtE7(cpu / com) "cpu/com"
, fosFmtE7(sel / com) "sel/Com"
, fosFmtE7(op / com) "ope/Com"
, fosFmtE7(fet / com) "fet/Com"
, von , bis
from i
order by plan_name, ela/com desc
with ur