zOs/SQL/TADM60
with r as
(
select
trunc_timestamp(timestamp, 'hh')
+ (smallint(minute(timestamp) / 5) * 5) minutes tst
, type, event_type
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%UU440%'
or name like '%UU02A1P%A440A%'
)
and timestamp > '2013-11-01-00.00.00'
)
, s as
(
select tst, count(*) total
, sum(case when type = 'TS-PAGE' then 1 else 0 end) tsPage
, sum(case when type = 'TS-RID' then 1 else 0 end) tsRid
, sum(case when type = 'DBID/OBID' then 1 else 0 end) dbidObid
, sum(case when event_type = 'T' then 1 else 0 end) timeout
, sum(case when event_type = 'D' then 1 else 0 end) deadlock
from r
group by tst
)
select date(trunc_timestamp(tst, 'iw'))
, smallint(count(distinct
case when total > 0 then tst else null end)) total
, smallint(count(distinct
case when tsPage > 0 then tst else null end)) tsPage
, smallint(count(distinct
case when tsRid > 0 then tst else null end)) tsRid
, smallint(count(distinct
case when dbidObid > 0 then tst else null end)) dbidObid
, smallint(count(distinct
case when timeout > 0 then tst else null end)) timeout
, smallint(count(distinct
case when deadlock > 0 then tst else null end)) deadlock
from s
group by trunc_timestamp(tst, 'iw')
order by 1 desc
;
with s as
(
select date(trunc_timestamp(timestamp, 'iw')) tst, count(*) total
, sum(case when type = 'TS-PAGE' then 1 else 0 end) tsPage
, sum(case when type = 'TS-RID' then 1 else 0 end) tsRid
, sum(case when type = 'DBID/OBID' then 1 else 0 end) dbidObid
, sum(case when event_type = 'T' then 1 else 0 end) timeout
, sum(case when event_type = 'D' then 1 else 0 end) deadlock
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%UU440%'
or name like '%UU02A1P%A440A%'
)
and timestamp > '2013-11-01-00.00.00'
group by trunc_timestamp(timestamp, 'iw')
)
select date(tst), total
, smallint(tsPage ) tsPage
, smallint(tsRid ) tsRid
, smallint(dbidObid) dbidObid id
, smallint(timeout ) timeout
, smallint(deadlock) deadlock
from s
order by 1 desc
;;;;;;;;
select date(trunc_timestamp(timestamp, 'iw')), count(*) total
, smallint(sum(case when type = 'TS-PAGE' then 1 else 0 end))tsPage
, smallint(sum(case when type = 'TS-RID' then 1 else 0 end))tsRid
, smallint(sum(case when type = 'DBID/OBID' then 1 else 0 end))dbidObid
, smallint(sum(case when event_type = 'T' then 1 else 0 end))timeout
, smallint(sum(case when event_type = 'D' then 1 else 0 end))deadlock
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%UU440%'
or name like '%UU02A1P%A440A%'
)
and timestamp > '2013-11-01-00.00.00'
group by trunc_timestamp(timestamp, 'iw')
order by 1 desc
;;;;;;;;
select source_plan, victim_plan, count(*)
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%UU440%'
or name like '%UU02A1P%A440A%'
)
and timestamp > '2013-01-14-00.00.00'
group by source_plan, victim_plan
order by 1, 2
;;;;;;;;
select (trunc_timestamp(timestamp, 'hh')), count(*) total
, sum(case when type = 'TS-PAGE' then 1 else 0 end) tsPage
, sum(case when type = 'TS-RID' then 1 else 0 end) tsRid
, sum(case when type = 'DBID/OBID' then 1 else 0 end) dbidObid
, sum(case when event_type = 'T' then 1 else 0 end) timeout
, sum(case when event_type = 'D' then 1 else 0 end) deadlock
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%UU440%'
or name like '%UU02A1P%A440A%'
)
and timestamp > '2013-11-01-00.00.00'
group by trunc_timestamp(timestamp, 'hh')
order by 1 desc
;;;;;;;;
select *
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%UU440%'
or name like '%UU02A1P%A440A%'
)
and timestamp > '2013-11-01-00.00.00'
order by timestamp desc
;;;;;;;;
select count(*), type, min(timestamp), max(timestamp)
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%UU440%'
or name like '%UU02A1P%A440A%'
)
and timestamp > '2014-01-01-00.00.00'
group by trunc_timestamp(timestamp, 'dd'), type
order by 3 desc, 2
;;;;;;;;
select count(*), source_plan, victim_plan
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%WI02A1P%'
)
and timestamp > '2013-04-01-00.00.00'
group by source_plan, victim_plan
order by 1 desc
;;;;;;;;
select count(*), substr(min(strip(name)), 1, 35), max(strip(name))
from OA1p.TADM60A1
where ssid like 'DOF%'
and ( name like '%WI02A1P%'
)
and timestamp > '2013-04-01-00.00.00'
group by left(strip(name), 16)
order by 1 desc
;;;;;;;;
select reason_code, event_type, count(*)
from OA1p.TADM60A1
group by reason_code , event_type
order by reason_code , event_type
;x;
select reason_code, min(event_type), max(event_type), count(*)
from OA1p.TADM60A1
group by reason_code
;x;
select reason_code, min(event_type), max(event_type), count(*)
from OA1p.TADM60A1
group by reason_code
;x;
select date(trunc_timestamp(timestamp, 'MON')),
smallint(count(*)) "anz",
type,
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
count(distinct left(name, 14)) "anz tb",
substr(min(case when name = '' then '9' else name end),1, 20),
max(name),
min(timestamp), max(timestamp)
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp > current timestamp - 800 days
group by trunc_timestamp(timestamp, 'MON'), type
order by min(timestamp) desc , type
;;;
select
smallint(count(*)) "anz",
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
left(name, 14) "tb",
substr(min(case when name = '' then '9' else name end),1, 20),
max(name),
min(timestamp), max(timestamp)
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp -- > '2011-07-25-00.00.00'
> current timestamp - 1 month
group by left(name, 14)
order by 1 desc
;;;;
select *
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
order by timestamp desc
fetch first 100 rows only
;;;
select count(*), date(min(timestamp))
from OA1p.TADM60A1
where ( VICTIM_PLAN like 'SN0100%'
or source_PLAN like 'SN0100%'
)
and timestamp > '2010-01-01-00.00.00'
group by trunc_timestamp(timestamp, 'mon')
order by trunc_timestamp(timestamp, 'mon') desc
;;;;;;;;
select *
from OA1p.TADM60A1
where name like 'SN01A1P .A187A%'
-- where ( VICTIM_PLAN like 'SN0100%'
-- or source_PLAN like 'SN0100%'
-- )
and timestamp > '2011-09-20-00.00.00'
order by timestamp desc
;;;;;;;;
select count(*), VICTIM_PLAN, source_PLAN
from OA1p.TADM60A1
where ( VICTIM_PLAN like 'SN0100%'
or source_PLAN like 'SN0100%'
)
and timestamp > '2011-08-28-00.00.00'
group by VICTIM_PLAN, source_PLAN
order by 1 desc
;;;;;;;;
select
smallint(count(*)) "anz",
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
left(name, 14) "tb",
substr(min(case when name = '' then '9' else name end),1, 20),
max(name),
min(timestamp), max(timestamp)
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp -- > '2011-07-25-00.00.00'
> current timestamp - 1 month
group by left(name, 14)
order by 1 desc
;;;;
group by area, left(name, 14)
select date(trunc_timestamp(timestamp, 'WW')),
smallint(count(*)) "anz",
type,
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
count(distinct left(name, 14)) "anz tb",
substr(min(case when name = '' then '9' else name end),1, 20),
max(name),
min(timestamp), max(timestamp)
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp > current timestamp - 800 days
group by trunc_timestamp(timestamp, 'WW'), type
order by min(timestamp) desc , type
;
select *
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%' -- and name = ''
and timestamp > current timestamp - 800 days
order by timestamp desc
fetch first 100 rows only
;;;
select date(trunc_timestamp(timestamp, 'WW')),
smallint(count(*)) "anz",
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
count(distinct left(name, 14)) "anz tb",
substr(min(case when name = '' then '9' else name end),1, 20),
max(name),
min(timestamp), max(timestamp)
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp > current timestamp - 800 days
group by trunc_timestamp(timestamp, 'WW')
order by min(timestamp) desc
;;;
select current date from sysibm.sysdummy1;
with g as
(
select count(*) dup, min(timestamp) timestamp, max(timestamp) tstMax,
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
-- and timestamp > current timestamp - 500 days
group by
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name,
time(timestamp),
trunc_timestamp(timestamp, 'mon')
)
, f as
(
select case when timestamp > '2011-05-14-00.00.00' then '20110514'
when timestamp > '2011-01-31-00.00.00' then '20110131'
else substr(char(year(timestamp)),1, 4) end area,
g.*
from g
where VICTIM_PLAN like 'VV5720%'
--and timestamp > current timestamp - 300 days
)
select area, count(*),
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
substr(min(name),1,30), substr(max(name), 1,30),
min(timestamp), max(timestamp)
from f
group by area, left(name, 14)
order by area desc, min(name)
;;;
with g as
(
select count(*) dup, min(timestamp) timestamp, max(timestamp) tstMax,
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name
from OA1p.TADM60A1
where -- VICTIM_PLAN like 'VV5720%'
source_PLAN like 'DSNU%' and source_corr_id like 'QR%'
and timestamp > current timestamp - 300 days
group by
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name,
time(timestamp),
trunc_timestamp(timestamp, 'mon')
)
select date(timestamp),
substr(case dayofweek(date(timestamp))
when 1 then 'So' when 2 then 'Mo' when 3 then 'Di'
when 4 then 'Mi' when 5 then 'Do' when 6 then 'Fr'
when 7 then 'Sa' end, 1, 2),
smallint(COUNT(*)),
source_plan , min(source_corr_id), max(source_corr_id)
from g
group by date(timestamp), source_plan
order by date(timestamp) desc, source_plan
;;;;;;;
with g as
(
select count(*) dup, min(timestamp) timestamp, max(timestamp) tstMax,
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name
from OA1p.TADM60A1
where -- VICTIM_PLAN like 'VV5720%'
-- or source_PLAN like 'VV5720%'
-- and timestamp > current timestamp - 500 days
group by
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name,
time(timestamp),
trunc_timestamp(timestamp, 'mon')
)
select date(trunc_timestamp(timestamp, 'WW')),
smallint(count(*)) "anz",
smallint(sum(case when left(name, 12) = 'VV20A1P .A70'
and substr(name, 13, 2) in ('1A', '3A', '9A')
then 1 else 0 end)) tb139,
-- smallint(sum(dup)) "dup",
-- max(days(tstMax)-days(timestamp)) "diffMax",
smallint(sum(case when source_plan like 'VV5720%'
then 0 else 1 end)) oth,
smallint(count(distinct left(name, 14))) "anz tb",
substr(min(case when name = '' then '9' else name end),1, 20),
max(name),
min(timestamp), max(timestamp)
from g
group by trunc_timestamp(timestamp, 'WW')
order by min(timestamp) desc
;;;
;;;
;;;
with g as
(
select count(*) dup, min(timestamp) timestamp, max(timestamp) tstMax,
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name
from OA1p.TADM60A1
where VICTIM_PLAN like 'V%'
-- where VICTIM_PLAN like 'VV5720%'
-- and timestamp > current timestamp - 500 days
group by
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
REASON_CODE, type, name,
time(timestamp),
trunc_timestamp(timestamp, 'mon')
)
select date(trunc_timestamp(timestamp, 'WW')),
smallint(count(*)) "anz",
smallint(sum(dup)) "dup",
max(days(tstMax)-days(timestamp)) "diffMax",
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
count(distinct left(name, 14)) "anz tb",
substr(min(case when name = '' then '9' else name end),1, 20),
max(name),
min(timestamp), max(timestamp)
from g
group by trunc_timestamp(timestamp, 'WW')
order by min(timestamp) desc
;;;
select *
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp > current timestamp - 7 days
and time(timestamp) in ('21.50.04'
,'13.26.47'
,'22.37.44'
,'21.50.07'
,'19.33.41'
,'14.01.11'
,'21.50.13'
,'19.33.41'
,'22.49.03'
,'14.06.50'
,'22.37.40'
,'20.17.46'
,'20.17.46'
,'14.08.20'
,'20.17.46'
,'14.52.11'
,'14.28.53'
,'22.45.32'
)
order by time(timestamp), timestamp, ssid
-- fetch first 100 rows only
;;;
select count(*), min(timestamp), max(timestamp),
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp > current timestamp - 5 days
group by
SSID, EVENT_TYPE, VICTIM_PLAN, VICTIM_CORR_ID, VICTIM_CONN_ID,
source_PLAN, source_CORR_ID, source_CONN_ID,
time(timestamp)
order by 1 desc
fetch first 100 rows only
;;;
select current date from sysibm.sysdummy1;
select *
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%' -- and name = ''
and timestamp > current timestamp - 800 days
order by timestamp desc
fetch first 100 rows only
;;;
with g as
(
select case when timestamp > '2011-05-14-00.00.00' then '20110514'
when timestamp > '2011-01-31-00.00.00' then '20110131'
else substr(char(year(timestamp)),1, 4) end area,
g.*
from OA1p.TADM60A1 g
where VICTIM_PLAN like 'VV5720%'
--and timestamp > current timestamp - 300 days
)
select area, count(*),
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
substr(min(name),1,30), substr(max(name), 1,30),
min(timestamp), max(timestamp)
from g
group by area, left(name, 14)
order by area desc, min(name)
;;;
select count(*),
sum(case when source_plan like 'VV5720%' then 0 else 1 end) oth,
min(timestamp), max(timestamp),
substr(min(name),1,30), substr(max(name), 1,30)
from OA1p.TADM60A1
where VICTIM_PLAN like 'VV5720%'
and timestamp > current timestamp - 30 days
group by left(name, 14)
order by 1 desc
;;;
select count(*), min(timestamp), max(timestamp)
from OA1A.TADM60A1
;
select event_type, count(*), min(timestamp), max(timestamp)
from OA1A.TADM60A1
group by event_type
;
select left(ssid, 3), count(*), min(timestamp), max(timestamp)
from OA1A.TADM60A1
group by left(ssid, 3)
order by left(ssid, 3)
select *
from OA1p.TADM60A1
fetch first 5 rows only
;;;