zOs/SQL/PDBPKGPW
set current path oa1p;
select class7_elapsed / sql_stmts_issued,
class7_cpu_total / sql_stmts_issued,
p.*
from rr2hh.tacct_program p
where pck_id = 'YPW2KAB'
and timestamp between '2012-11-01-12.00.00'
and '2012-12-02-12.00.00'
order by subsystem_id, timestamp
;x;
select
-- substr(PCK_COLLECTION_ID, 1, 8) coll,
-- substr(PCK_ID, 1, 8) pkg,
date(timestamp),
-- min(date(timestamp)),
-- max(SUBSYSTEM_ID )),
sum(SQL_STMTS_ISSUED ) sql,
sum(CLASS7_CPU_TOTAL) / sum(SQL_STMTS_ISSUED) cpuSql,
sum(CLASS7_ELAPSED ) / sum(SQL_STMTS_ISSUED) elaSql,
min(CLASS7_CPU_TOTAL / SQL_STMTS_ISSUED) cpuSqlMin,
max(CLASS7_CPU_TOTAL / SQL_STMTS_ISSUED) cpuSqlMax,
sum(CLASS7_CPU_TOTAL) cpu,
sum(CLASS7_ELAPSED ) ela
from rr2dd.tacct_program
where pck_id in ('YPWSIQ6')
and subsystem_id like 'DOF%'
and timestamp >= '2012-07-01-00.00.00'
-- and timestamp <= '2012-05-13-00.00.00'
and date(timestamp) <> '2012-05-11'
group by date(timestamp)
-- case when timestamp < '2012-05-11-00.00.00'
order by 1 desc
;;;
select * from s
order by 1, cpuSqlMin
;;;
select
-- substr(PCK_COLLECTION_ID, 1, 8) coll,
-- substr(PCK_ID, 1, 8) pkg,
-- date(trunc_timestamp(timestamp, 'day')),
case when timestamp < '2012-05-11-00.00.00'
then 'vor 11.5' else 'nach11.5' end ,
-- min(date(timestamp)),
-- max(SUBSYSTEM_ID )),
fosFmtE7(sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)) "cpu/stm",
fosFmtE7(sum(CLASS7_ELAPSED ) /sum(SQL_STMTS_ISSUED)) "ela/stm",
fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
smallint(count(distinct date(timestamp))) "days",
fosFmtE7(sum(CLASS7_CPU_TOTAL )
/count(distinct date(timestamp))) "cpu/day",
fosFmtE7(sum(CLASS7_ELAPSED )
/count(distinct date(timestamp))) "ela/day",
fosFmtE7(sum(CLASS7_cpu_total)) "c7cpu",
fosFmtE7(sum(CLASS7_ELAPSED )) "c7ela",
-- fosFmtE7(sum(CLASS8 )),
fosFmtE7(sum(CLASS8_LOCK_LATCH )) "c8loLa" ,
fosFmtE7(sum(CLASS8_SYNC_IO )) "c8synIO",
fosFmtE7(sum(CLASS8_OTHER_READ )) "c8othRe",
fosFmtE7(sum(CLASS8_OTHER_WRITE)) "c8othWr",
fosFmtE7(sum(CLASS8_SERV_TASK )) "c8SrvTa",
fosFmtE7(sum(BP_GETPAGES )) "getPg",
fosFmtE7(sum(SYNC_READ )) "synRead",
fosFmtE7(sum(SEQ_PREFETCH )) "seqPref",
fosFmtE7(sum(LIST_PREFETCH )) "listPre",
fosFmtE7(sum(DYN_PREFETCH )) "dynPref",
fosFmtE7(sum(ASYNC_READ )) "asyRead",
fosFmtE7(sum(BUFFER_UPDATE )) "bufUpd",
fosFmtE7(sum(SYNC_WRITE )) "synWrit",
fosFmtE7(sum(PAR_GETPAGES_FAIL )) "gePgFai",
fosFmtE7(sum(CLASS7_SU_CPU )) "c7SU",
fosFmtE7(sum(OCCURRENCES )) "occ",
min(subsystem_id) subMin ,
max(subsystem_id) subMax ,
min(timestamp) tstMin,
max(timestamp) tstMax
from rz2dd.tacct_program
where pck_id in ('WM0010'
,'WM8300'
,'YYWM001'
,'YYWM005'
,'YYWM01E'
,'YYWM05E'
,'YYWM051'
,'YYWM51E'
)
-- where pck_id in ('YYWM05E', 'YYWM51E')
and subsystem_id like 'DOF%'
and timestamp >= '2012-03-01-00.00.00'
and date(timestamp) <> '2012-05-11'
-- timestamp between '2011-07-25-01.00.00'
-- and '2011-07-28-23.00.00'
-- and dayofweek_iso(timestamp) <= 5
-- AND hour(timesta p) between 9 and 11
group by -- pck_id, pck_collection_id ,
case when timestamp < '2012-05-11-00.00.00'
then 'vor 11.5' else 'nach11.5' end
order by 1 --, 2, 3
-- order by 1 -- pck_id,
-- trunc_timestamp(timestamp, 'day') desc
;;;
set current path oa1p;
select
-- trunc_timestamp(timestamp, 'day'),
-- min(date(timestamp)),
-- max(SUBSYSTEM_ID )),
-- date(TIMESTAMP),
-- case when timestamp < '2011-08-23-12.45.00' then 'alt'
-- else 'neu' end,
PLAN_NAME ,
GROUP_NAME grp,
substr(PCK_ID, 1, 8) pkg,
fosFmtE7(sum(CLASS7_ELAPSED ) /sum(SQL_STMTS_ISSUED)) "ela/stm",
fosFmtE7(sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)) "cpu/stm",
fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
fosFmtE7(sum(CLASS7_ELAPSED )) "c7ela",
fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
-- fosFmtE7(sum(CLASS8 )),
fosFmtE7(sum(CLASS8_LOCK_LATCH )) "c8loLa" ,
fosFmtE7(sum(CLASS8_SYNC_IO )) "c8synIO",
fosFmtE7(sum(CLASS8_OTHER_READ )) "c8othRe",
fosFmtE7(sum(CLASS8_OTHER_WRITE)) "c8othWr",
fosFmtE7(sum(CLASS8_SERV_TASK )) "c8SrvTa",
fosFmtE7(sum(BP_GETPAGES )) "getPg",
fosFmtE7(sum(SYNC_READ )) "synRead",
fosFmtE7(sum(SEQ_PREFETCH )) "seqPref",
fosFmtE7(sum(LIST_PREFETCH )) "listPre",
fosFmtE7(sum(DYN_PREFETCH )) "dynPref",
fosFmtE7(sum(ASYNC_READ )) "asyRead",
fosFmtE7(sum(BUFFER_UPDATE )) "bufUpd",
fosFmtE7(sum(SYNC_WRITE )) "synWrit",
fosFmtE7(sum(PAR_GETPAGES_FAIL )) "gePgFai",
fosFmtE7(sum(CLASS7_SU_CPU )) "c7SU",
fosFmtE7(sum(OCCURRENCES )) "occ",
substr(PCK_COLLECTION_ID, 1, 8) coll,
min(subsystem_id) subMin ,
max(subsystem_id) subMax ,
min(timestamp) tstMin,
max(timestamp) tstMax
from rz2hh.tacct_program
where pck_id in ('YYWM05E', 'YYWM51E')
and subsystem_id like 'DOF%'
and timestamp > current timestamp - 11 days
-- timestamp between '2011-07-25-01.00.00'
-- and '2011-07-28-23.00.00'
and dayofweek_iso(timestamp) <= 5
AND hour(timesta p) between 9 and 11
group by group_name, plan_name, PCK_COLLECTION_ID,PCK_id
-- , trunc_timestamp(timestamp, 'ddd')
-- case when timestamp < '2011-08-23-12.45.00' then 'alt'
-- else 'neu' end
order by group_name -- , PCK_COLLECTION_ID,PCK_id
, sum(CLASS7_CPU_TOTAL) desc
, plan_name
-- , min(TIMESTAMP) desc
-- order by sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)
;;;
select *
from rz2dd.tacct_general
where plan_name in ('WB0670'
,'WI0180'
,'WQ0030'
,'WC0218'
,'WQ5110'
)
and timestamp > current timestamp - 3 days
order by plan_name, timestamp desc
;;;;
select
date(timestamp),
fosFmtE7(sum(OCCURRENCES )) "occ",
-- max(SUBSYSTEM_ID )),
-- date(TIMESTAMP),
-- case when timestamp < '2011-08-23-12.45.00' then 'alt'
-- else 'neu' end,
PLAN_NAME ,
substr(PCK_COLLECTION_ID, 1, 8) coll,
substr(PCK_ID, 1, 8) pkg,
fosFmtE7(sum(CLASS7_ELAPSED ) /sum(SQL_STMTS_ISSUED)) "ela/stm",
fosFmtE7(sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)) "cpu/stm",
fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
fosFmtE7(sum(CLASS7_ELAPSED )) "c7ela",
fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
-- fosFmtE7(sum(CLASS8 )),
fosFmtE7(sum(CLASS8_LOCK_LATCH )) "c8loLa" ,
fosFmtE7(sum(CLASS8_SYNC_IO )) "c8synIO",
fosFmtE7(sum(CLASS8_OTHER_READ )) "c8othRe",
fosFmtE7(sum(CLASS8_OTHER_WRITE)) "c8othWr",
fosFmtE7(sum(CLASS8_SERV_TASK )) "c8SrvTa",
fosFmtE7(sum(BP_GETPAGES )) "getPg",
fosFmtE7(sum(SYNC_READ )) "synRead",
fosFmtE7(sum(SEQ_PREFETCH )) "seqPref",
fosFmtE7(sum(LIST_PREFETCH )) "listPre",
fosFmtE7(sum(DYN_PREFETCH )) "dynPref",
fosFmtE7(sum(ASYNC_READ )) "asyRead",
fosFmtE7(sum(BUFFER_UPDATE )) "bufUpd",
fosFmtE7(sum(SYNC_WRITE )) "synWrit",
fosFmtE7(sum(PAR_GETPAGES_FAIL )) "gePgFai",
-- fosFmtE7(sum(GROUP_NAME ))
fosFmtE7(sum(CLASS7_SU_CPU )) "c7SU",
min(left(subsystem_id, 3)) sub
from rz2dd.tacct_program
where pck_id = 'YCKSCCA'
and subsystem_id like 'DOF%'
and timestamp > '2011-08-01-00.00.00'
-- timestamp between '2011-07-25-01.00.00'
-- and '2011-07-28-23.00.00'
group by plan_name, PCK_COLLECTION_ID,PCK_id
, date(timestamp)
-- case when timestamp < '2011-08-23-12.45.00' then 'alt'
-- else 'neu' end
order by plan_name, PCK_COLLECTION_ID,PCK_id
, min(TIMESTAMP) desc
-- order by sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)
;;;
set current path oa1p;
select
-- max(SUBSYSTEM_ID )),
-- date(TIMESTAMP),
PLAN_NAME ,
-- substr(PCK_COLLECTION_ID, 1, 8) coll,
-- substr(PCK_ID, 1, 8) pkg,
fosFmtE7(sum(CLASS7_ELAPSED)/sum(SQL_STMTS_ISSUED)) "ela/stm",
fosFmtE7(sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)) "cpu/stm",
fosFmtE7(sum(OCCURRENCES )) "occ",
fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
fosFmtE7(sum(CLASS7_ELAPSED )) "c7ela",
fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
-- fosFmtE7(sum(CLASS8 )),
fosFmtE7(sum(CLASS8_LOCK_LATCH )) "c8loLa" ,
fosFmtE7(sum(CLASS8_SYNC_IO )) "c8synIO",
fosFmtE7(sum(CLASS8_OTHER_READ )) "c8othRe",
fosFmtE7(sum(CLASS8_OTHER_WRITE)) "c8othWr",
fosFmtE7(sum(CLASS8_SERV_TASK )) "c8SrvTa",
fosFmtE7(sum(BP_GETPAGES )) "getPg",
fosFmtE7(sum(SYNC_READ )) "synRead",
fosFmtE7(sum(SEQ_PREFETCH )) "seqPref",
fosFmtE7(sum(LIST_PREFETCH )) "listPre",
fosFmtE7(sum(DYN_PREFETCH )) "dynPref",
fosFmtE7(sum(ASYNC_READ )) "asyRead",
fosFmtE7(sum(BUFFER_UPDATE )) "bufUpd",
fosFmtE7(sum(SYNC_WRITE )) "synWrit",
fosFmtE7(sum(PAR_GETPAGES_FAIL )) "gePgFai",
-- fosFmtE7(sum(GROUP_NAME ))
fosFmtE7(sum(CLASS7_SU_CPU )) "c7SU",
fosFmtE7(min(CLASS7_ELAPSED / SQL_STMTS_ISSUED)) "ela/st<",
fosFmtE7(max(CLASS7_ELAPSED / SQL_STMTS_ISSUED)) "ela/st>"
from rz2xx.tacct_program
where pck_id = 'YCKSCCA'
and subsystem_id like 'DOF%'
-- and timestamp > current timestamp - 7 day
-- timestamp between '2010-01-10-00.00.00'
-- and '2010-02-20-00.00.00'
group by plan_name ,
floor(3. * log10(
max(1e-9, CLASS7_ELAPSED / SQL_STMTS_ISSUED)))
order by plan_name, min(CLASS7_ELAPSED / SQL_STMTS_ISSUED)
with ur
;;;