zOs/SQL/PDBGENRX
set current path oa1p;
select *
from rz2hh.tacct_general
where plan_name = 'DSNREXX'
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(timestamp) between 9 and 11
order by
CLASS2_CPU_TOTAL desc
;;;
set current path oa1p;
select
date(trunc_timestamp(timestamp, 'ddd')),
-- min(date(timestamp)),
-- max(SUBSYSTEM_ID )),
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 rz2dd.tacct_program
where pck_id in ('YCDSGET')
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 pck_id, pck_collection_id ,
trunc_timestamp(timestamp, 'ddd')
-- case when timestamp < '2011-08-23-12.45.00' then 'alt'
-- else 'neu' end
order by pck_id, trunc_timestamp(timestamp, 'ddd') desc
;;;
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
;;;