zOs/SQL/PDBGENNI
select *
from RZ2MM.tacct_general
where plan_name = 'NI5730'
and ( timestamp between '2011-11-01-04.57.00'
and '2011-11-01-04.59.00'
or timestamp between '2011-10-08-03.04.00'
and '2011-10-08-03.04.00'
)
order by timestamp desc
;
select *
from RZ2MM.tacct_program
where plan_name = 'NI5730'
and pck_id = 'YAVX009'
and ( timestamp between '2011-11-01-04.57.00'
and '2011-11-01-04.59.00'
or timestamp between '2011-10-08-03.04.00'
and '2011-10-08-03.04.00'
)
order by timestamp desc
;;
select *
from RZ2MM.tacct_general
where plan_name = 'NI8610'
and timestamp between '2011-09-17-00.00.00'
and '2011-09-18-00.00.00'
order by timestamp desc
fetch first 200 rows only
;;
set current path oa1p;
set current schema = A540769;
set current path oa1p;
set current schema = A540769;
select id, rz, insDate "insDateRZ1",
min(timestamp - class1_elapsed seconds) "von",
time(max(timestamp)) "bis",
smallint(timestampDiff(4, char(max(timestamp)
- min(timestamp - class1_elapsed seconds)))) "minuten",
smallInt(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(class1_elapsed)) "c1Ela" ,
fosFmte7(sum(class1_cpu_total)) "c1Cpu",
oa1p.fosFmtE7(sum(class2_elapsed)) "c2Ela" ,
fosFmte7(sum(class2_cpu_total)) "c2Cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "c3LocLat",
fosFmte7(sum(CLASS3_GLOBAL_CONT)) "c3GloCon",
fosFmte7(sum(CLASS3_SYNC_IO)) "c3SyncIO",
fosFmte7(sum(CLASS3_LOG_WRT_IO)) "c3LogWr",
fosFmte7(sum(real(Log_RECS_WRITTEN ))) "logRecs",
fosFmte7(sum(fosRealK(BYTES_PER_LOG_REC))) "logByte",
smallInt(count(distinct subsystem_id)) "#ssid",
oa1p.fosFmtE7(sum(commit)) com,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del,
sum(ARCH_LOG_SUSP) "arch_log_sus" ,
sum(ARCH_READS_SUSP) "arch_reads_s" ,
sum(ASYNCH_IXL_REQU) "asynch_ixl_r" ,
min(BYTES_PER_LOG_REC) "bytes_per_lo<" ,
sum(CACHE_LIMIT_EXCEED) "cache_limit_" ,
sum(CHANGE_REQ) "change_req" ,
sum(CLAIM_FAILED) "claim_failed" ,
sum(CLAIM_REL_SUSP) "claim_rel_su" ,
sum(CLAIM_REQ) "claim_req" ,
fosFmtE7(sum(CLASS1_CPU_TOTAL)) "c1_cpu_total" ,
fosFmtE7(sum(CLASS1_ELAPSED)) "c1_elapsed" ,
fosFmtE7(sum(CLASS1_IIP_CPU)) "c1_iip_cpu" ,
fosFmtE7(sum(CLASS1_SU_CPU)) "c1_su_cpu" ,
fosFmtE7(sum(CLASS1_SU_IIP_CPU)) "c1_su_iip_cp" ,
min(CLASS2) "c2<" ,
fosFmtE7(sum(CLASS2_CPU_TOTAL)) "c2_cpu_total" ,
fosFmtE7(sum(CLASS2_ELAPSED)) "c2_elapsed" ,
fosFmtE7(sum(CLASS2_IIP_CPU)) "c2_iip_cpu" ,
fosFmtE7(sum(CLASS2_SU_CPU)) "c2_su_cpu" ,
fosFmtE7(sum(CLASS2_SU_IIP_CPU)) "c2_su_iip_cp" ,
min(CLASS3) "c3<" ,
fosFmtE7(sum(CLASS3_ARCH_LOG)) "c3_arch_log" ,
fosFmtE7(sum(CLASS3_ARCH_READS)) "c3_arch_read" ,
fosFmtE7(sum(CLASS3_ASYNCH_IXL)) "c3_asynch_ix" ,
fosFmtE7(sum(CLASS3_CLAIM_REL)) "c3_claim_rel" ,
fosFmtE7(sum(CLASS3_DB_IO)) "c3_db_io" ,
fosFmtE7(sum(CLASS3_DRAIN_LOCK)) "c3_drain_loc" ,
fosFmtE7(sum(CLASS3_EXT_DEL_DEF)) "c3_ext_del_d" ,
fosFmtE7(sum(CLASS3_FORCE_AT_C)) "c3_force_at_" ,
fosFmtE7(sum(CLASS3_GLOBAL_CONT)) "c3_global_co" ,
fosFmtE7(sum(CLASS3_LLOCK_CHILD)) "c3_llock_chi" ,
fosFmtE7(sum(CLASS3_LLOCK_OTHER)) "c3_llock_oth" ,
fosFmtE7(sum(CLASS3_LLOCK_PARNT)) "c3_llock_par" ,
fosFmtE7(sum(CLASS3_LOCK_LATCH)) "c3_lock_latc" ,
fosFmtE7(sum(CLASS3_LOG_WRT_IO)) "c3_log_wrt_i" ,
fosFmtE7(sum(CLASS3_NOTIFY_MSG)) "c3_notify_ms" ,
fosFmtE7(sum(CLASS3_OPEN_CLOSE)) "c3_open_clos" ,
fosFmtE7(sum(CLASS3_OTHER_READ)) "c3_other_rea" ,
fosFmtE7(sum(CLASS3_OTHER_SERV)) "c3_other_ser" ,
fosFmtE7(sum(CLASS3_OTHER_WRITE)) "c3_other_wri" ,
fosFmtE7(sum(CLASS3_PAGE_LATCH)) "c3_page_latc" ,
fosFmtE7(sum(CLASS3_PLOCK_OTHER)) "c3_plock_oth" ,
fosFmtE7(sum(CLASS3_PLOCK_PAGE)) "c3_plock_pag" ,
fosFmtE7(sum(CLASS3_PLOCK_PSET)) "c3_plock_pse" ,
fosFmtE7(sum(CLASS3_SCHEDUL_UDF)) "c3_schedul_u" ,
fosFmtE7(sum(CLASS3_SCHED_STPRC)) "c3_sched_stp" ,
fosFmtE7(sum(CLASS3_SERV_TASK)) "c3_serv_task" ,
fosFmtE7(sum(CLASS3_SYNC_IO)) "c3_sync_io" ,
fosFmtE7(sum(CLASS3_SYSLOGR_REC)) "c3_syslogr_r" ,
fosFmtE7(sum(CLASS3_UPD_COMMIT)) "c3_upd_commi" ,
fosFmtE7(sum(CLASS5_DESCRIBE)) "c5_describe" ,
fosFmtE7(sum(CLASS5_ELAPSED)) "c5_elapsed" ,
fosFmtE7(sum(CLASS5_LOG_EXTRACT)) "c5_log_extra" ,
fosFmtE7(sum(CLASS5_TCB)) "c5_tcb" ,
sum(CLOSE) "close" ,
sum(COMMIT) "commit" ,
min(CONNECT_ID) "connect_id<" ,
min(CONNECT_TYPE) "connect_type<" ,
min(CORRNAME) "corrname<" ,
min(CORRNUMBER) "corrnumber<" ,
sum(DATA_ROWS_RETURNED) "data_rows_re" ,
sum(DB_IO_SUSP) "db_io_susp" ,
sum(DEADLOCK) "deadlock" ,
sum(DELETE) "delete" ,
sum(DESCRIBE) "describe" ,
sum(DESCRIBES_PERFORM) "describes_pe" ,
sum(DESCRIBE_TABLE) "describe_tab" ,
sum(DESCRIPT_RETURNED) "descript_ret" ,
sum(DIRECT_ACCESS) "direct_acces" ,
sum(DRAIN_FAILED) "drain_failed" ,
sum(DRAIN_LOCK_SUSP) "drain_lock_s" ,
sum(DRAIN_REQ) "drain_req" ,
sum(EXT_DEL_DEF_SUSP) "ext_del_def_" ,
sum(FETCH) "fetch" ,
sum(FORCE_AT_COMMIT) "force_at_com" ,
sum(GLOBAL_CONT_SUSP) "global_cont_" ,
min(GROUP_NAME) "group_name<" ,
sum(HIGHEST_USED_CPU) "highest_used" ,
min(ID) "id<" ,
sum(IFI_CALLS) "ifi_calls" ,
sum(IIPCP_ELIGIBLE) "iipcp_eligib" ,
sum(IMPLICIT_PREPARES) "implicit_pre" ,
sum(INCOMPAT_RET_LOCK) "incompat_ret" ,
sum(INCREMENTAL_BIND) "incremental_" ,
sum(INDEX_USED) "index_used" ,
min(INSDATE) "insdate<" ,
sum(INSERT) "insert" ,
sum(LIMIT_IN_CPU) "limit_in_cpu" ,
sum(LIMIT_IN_SU) "limit_in_su" ,
sum(LLOCK_CHILD_SUSP) "llock_child_" ,
sum(LLOCK_OTHER_SUSP) "llock_other_" ,
sum(LLOCK_PARENT_SUSP) "llock_parent" ,
sum(LOCK_ESC_EXCLUSIVE) "lock_esc_exc" ,
sum(LOCK_ESC_SHARED) "lock_esc_sha" ,
sum(LOCK_LATCH_SUSP) "lock_latch_s" ,
sum(LOCK_REQ) "lock_req" ,
sum(LOCK_TABLE) "lock_table" ,
sum(LOG_READS_PERFORM) "log_reads_pe" ,
sum(LOG_RECS_CAPTURED) "log_recs_cap" ,
sum(LOG_RECS_RETURNED) "log_recs_ret" ,
sum(LOG_RECS_WRITTEN) "log_recs_wri" ,
sum(LOG_WRT_IO_SUSP) "log_wrt_io_s" ,
sum(LUW_SEQNO) "luw_seqno" ,
sum(MAX_LOCKS_HELD) "max_locks_he" ,
sum(MAX_SQL_CASCAD_LVL) "max_sql_casc" ,
sum(MAX_STOR_LOB_VALUE) "max_stor_lob" ,
sum(NOTIFY_MSG_SENT) "notify_msg_s" ,
sum(NOTIFY_MSG_SUSP) "notify_msg_s" ,
sum(OCCURRENCES) "occurrences" ,
sum(OPEN) "open" ,
sum(OPEN_CLOSE_SUSP) "open_close_s" ,
sum(OTHER_READ_SUSP) "other_read_s" ,
sum(OTHER_REQ) "other_req" ,
sum(OTHER_SERV_SUSP) "other_serv_s" ,
sum(OTHER_WRITE_SUSP) "other_write_" ,
sum(PAGE_LATCH_SUSP) "page_latch_s" ,
sum(PARTSPLIT) "partsplit" ,
min(PLAN_NAME) "plan_name<" ,
sum(PLOCK_OTHER_SUSP) "plock_other_" ,
sum(PLOCK_PAGE_SUSP) "plock_page_s" ,
sum(PLOCK_PSET_SUSP) "plock_pset_s" ,
sum(PREPARE) "prepare" ,
sum(PREP_FROM_CACHE) "prep_from_ca" ,
sum(PREP_STMT_MATCH) "prep_stmt_ma" ,
sum(PREP_STMT_NO_MATCH) "prep_stmt_no" ,
sum(PREP_STMT_PURGED) "prep_stmt_pu" ,
sum(PROGRAMS) "programs" ,
sum(P_LOCK_CHANGE) "p_lock_chang" ,
sum(P_LOCK_LOCK) "p_lock_lock" ,
sum(P_LOCK_UNLOCK) "p_lock_unloc" ,
sum(QUERY_REQ) "query_req" ,
sum(REOPTIMIZATION) "reoptimizati" ,
sum(RID_POOL_FAIL_MXLT) "rid_pool_fai" ,
sum(RID_POOL_FAIL_NOSG) "rid_pool_fai" ,
sum(RID_POOL_USED) "rid_pool_use" ,
min(RLF_TABLE) "rlf_table<" ,
sum(RLF_TYPE) "rlf_type" ,
sum(ROLLBACK) "rollback" ,
sum(ROW_TRIGGER) "row_trigger" ,
min(RZ) "rz<" ,
sum(SCHEDUL_UDF_SUSP) "schedul_udf_" ,
sum(SCHED_STPRC_SUSP) "sched_stprc_" ,
sum(SELECT) "select" ,
sum(SERV_TASK_SUSP) "serv_task_su" ,
sum(SET_CURRENT_PREC) "set_current_" ,
sum(STATEMENT_TRIGGER) "statement_tr" ,
sum(STPROC_ABEND) "stproc_abend" ,
sum(STPROC_CALL) "stproc_call" ,
sum(STPROC_REJECTED) "stproc_rejec" ,
sum(STPROC_TIMEOUT) "stproc_timeo" ,
min(SUBSYSTEM_ID) "subsystem_id<" ,
sum(SUSPEND_FALSE_CONT) "suspend_fals" ,
sum(SUSPEND_IRLM_CONT) "suspend_irlm" ,
sum(SUSPEND_IRLM_LATCH) "suspend_irlm" ,
sum(SUSPEND_LOCK) "suspend_lock" ,
sum(SUSPEND_OTHER) "suspend_othe" ,
sum(SUSPEND_XES_CONT) "suspend_xes_" ,
sum(SU_IIPCP_ELIGIBLE) "su_iipcp_eli" ,
sum(SVPT_RELEASE) "svpt_release" ,
sum(SVPT_REQUESTS) "svpt_request" ,
sum(SVPT_ROLLBACK) "svpt_rollbac" ,
sum(SYNC_IO_SUSP) "sync_io_susp" ,
sum(SYNC_XES_CHANGE) "sync_xes_cha" ,
sum(SYNC_XES_LOCK) "sync_xes_loc" ,
sum(SYNC_XES_UNLOCK) "sync_xes_unl" ,
sum(SYSLOGR_REC_SUSP) "syslogr_rec_" ,
sum(TABLES_RETURNED) "tables_retur" ,
sum(TIMEOUT) "timeout" ,
min(TIMESTAMP) "timestamp<" ,
sum(TRIGGER_SQL_ERROR) "trigger_sql_" ,
sum(TS_SCAN_USED) "ts_scan_used" ,
sum(UDF_ABENDS) "udf_abends" ,
sum(UDF_EXECUTED) "udf_executed" ,
sum(UDF_REJECTED) "udf_rejected" ,
sum(UDF_TIMEOUTS) "udf_timeouts" ,
sum(UNLOCK_REQ) "unlock_req" ,
sum(UPDATE) "update" ,
sum(UPD_COMMIT_SUSP) "upd_commit_s"
from tacct_general
where timestamp > '2011-03-01-00.00.00'
and rz in ('RR2', 'RZ2') and id like 'ni%'
group by id, rz, insDate, date(timestamp)
order by id, rz, insDate desc, date(timestamp) desc
with ur
;;;
select id, rz, insDate "insDateRZ1",
min(timestamp - class1_elapsed seconds) "von",
time(max(timestamp)) "bis",
smallint(timestampDiff(4, char(max(timestamp)
- min(timestamp - class1_elapsed seconds)))) "minuten",
smallInt(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(class1_elapsed)) "c1Ela" ,
fosFmte7(sum(class1_cpu_total)) "c1Cpu",
oa1p.fosFmtE7(sum(class2_elapsed)) "c2Ela" ,
fosFmte7(sum(class2_cpu_total)) "c2Cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "c3LocLat",
fosFmte7(sum(CLASS3_GLOBAL_CONT)) "c3GloCon",
fosFmte7(sum(CLASS3_SYNC_IO)) "c3SyncIO",
fosFmte7(sum(CLASS3_LOG_WRT_IO)) "c3LogWr",
fosFmte7(sum(real(Log_RECS_WRITTEN ))) "logRecs",
fosFmte7(sum(fosRealK(BYTES_PER_LOG_REC))) "logByte",
smallInt(count(distinct subsystem_id)) "#ssid",
oa1p.fosFmtE7(sum(commit)) com,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del
from tacct_general
where timestamp > '2011-03-01-00.00.00'
and rz in ('RR2', 'RZ2') and id like 'ni%'
group by id, rz, insDate, date(timestamp)
order by id, rz, insDate desc, date(timestamp) desc
with ur
;;;
select *
from A540769.tacct_general
where id like 'ni%'
order by timestamp desc
fetch first 200 rows only
;;
select count(*), id, rz, date(timestamp), min(timestamp),
max(timestamp + class1_elapsed seconds)
from A540769.tacct_general
group by id, rz, date(timestamp)
order by 5 desc
;;
select count(*), id, rz
from A540769.tacct_general
group by id, rz
;;
set current path oa1p;
set current schema = RZ2MM;
select dayofWeek(timestamp),
fosFmtE7( class1_elapsed) "c1Ela",
fosFmtE7( class2_elapsed) "c2Ela",
fosFmtE7( class2_Cpu_total) "c2Cpu",
fosFmtE7( select) sel,
fosFmtE7( open ) opn,
fosFmtE7( fetch ) opn,
fosFmtE7( insert) ins,
fosFmtE7( update) upd,
fosFmtE7( delete) del,
g.*
from TACCT_GENERAL g
where (corrName like '%NIT1615%')
and timestamp > current timestamp - 93 days
and class1_elapsed > 1000
order by 2 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",
smallint(timestampDiff(4, char(max(timestamp)
- min(timestamp - class1_elapsed seconds)))) "minuten",
fosFmte7(sum(class1_elapsed)) "ela sec",
fosFmte7(sum(occurrences)) occ,
oa1p.fosFmtE7(sum(class2_elapsed)) "c2Ela" ,
fosFmte7(sum(class2_cpu_total)) "c2Cpu",
fosFmte7(sum(CLASS3_LOCK_LATCH)) "c3LocLat",
fosFmte7(sum(CLASS3_SYNC_IO)) "c3SyncIO",
count(distinct subsystem_id) "#ssid",
fosFmte7(sum(CLASS3_LOG_WRT_IO)) "c3LogWa",
fosFmte7(sum(real(Log_RECS_WRITTEN ))) "logRecs",
fosFmte7(sum(fosRealK(BYTES_PER_LOG_REC))) "logByte",
oa1p.fosFmtE7(sum(commit)) com,
oa1p.fosFmtE7(sum(select)) sel,
oa1p.fosFmtE7(sum(insert)) ins,
oa1p.fosFmtE7(sum(update)) upd,
oa1p.fosFmtE7(sum(delete)) del,
plan_name, id, rz
-- min(SUBSYSTEM_ID), max(SUBSYSTEM_ID)
from g
where -- timestamp > current timestamp - 9992 month
rz = 'RZ2' and id like 'ni250Lo%'
-- and connect_ID like 'NI229%'
-- and plan_name in ( 'NI5310', 'NI5340')
-- and insert >= 1
group by g1, g2, id, rz, 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 '1919-05-19-11.00.00'
and '1919-05-19-12.30.00' then 'o'
else ' ' end g2,
g.*
from TACCT_GENERAL g
)
select * from g
where rz = 'RZ2' and id like 'ni250Lo%'
order by timestamp desc
with ur
;x;
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",
-- fosFmte7(sum(CLASS3_DB_IO)) "c3dbIO",
count(distinct subsystem_id) "#ssid", plan_name, id,
fosFmte7(sum(CLASS3_LOG_WRT_IO) /sum(insert+update)) "logWa/iu",
-- fosFmte7(sum(CLASS3_LOG_WRT_IO) ) "c3logWa",
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 = 'RZ2' 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
;x;
select * from TACCT_GENERAL g;
;x;
set current schema = RR2xx;
select * from tacct_general
where corrName like 'NIT16%'
order by timestamp desc
;;;
select count(*), date(timestamp)
from tacct_general
group by date(timestamp)
;;;
where -- corrName like 'NIT16%'
plan_name = 'DSNUTIL'
-- and timestamp > current timestamp
order by timestamp desc
;;;
select count(*), id, rz
from A540769.tacct_general
group by id, rz
;;
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, rz,
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