zOs/SQL/ABUBDDLV
-- select * from oa1p.vQZ045AbUbStat3 ; x where rz = 'RZZ' ;x;
set current sqlid = 'S100447';
drop view oa1p.vQZ045AbUbLast ;
drop view oa1p.vQZ046AbUbCal ;
drop view oa1p.vQZ046AbubCa3 ;
create view oa1p.vQZ046AbubCal as
with d (dt, x) as
(
select current date + 8 days, 0 from sysibm.sysDummy1
union all select dt - 1 days, x+1 from d where x < 25
)
, t as
(
select cast( timestamp(dt, strip(va2)||':00') as timestamp(0)) start
, r.*
from d join oa1p.tQZ046AbUbRule r
on r.type = 'cal' and r.subType not like 'trunc%'
)
, c1 as
(
select *
from t
where 0 < case when subType = 'wtZe'
then locate(dayOfWeek_iso(start), va1)
else raise_error(77701, 'cal bad subType ' || subType)
end
union all select trunc_timestamp(current timestamp, 'hh') start
, r.*
from oa1p.tQZ046AbUbRule r
where r.type = 'cal' and r.subType = 'truncHH'
union all select trunc_timestamp(current timestamp, 'hh')
+ 1 hour start, r.*
from oa1p.tQZ046AbUbRule r
where r.type = 'cal' and r.subType = 'truncHH'
union all select trunc_timestamp(current timestamp, 'hh')
- 1 hour start, r.*
from oa1p.tQZ046AbUbRule r
where r.type = 'cal' and r.subType = 'truncHH'
union all select trunc_timestamp(current timestamp, 'dd') start
, r.*
from oa1p.tQZ046AbUbRule r
where r.type = 'cal' and r.subType = 'truncDD'
union all select trunc_timestamp(current timestamp, 'dd')
+ 1 day start, r.*
from oa1p.tQZ046AbUbRule r
where r.type = 'cal' and r.subType = 'truncDD'
union all select trunc_timestamp(current timestamp, 'dd')
- 1 day start, r.*
from oa1p.tQZ046AbUbRule r
where r.type = 'cal' and r.subType = 'truncDD'
)
, c2 as
(
select c1.*
, case when strip(va3) like 'e%' then 'e' else 's' end tiOuTy
from c1
)
, c3 as
(
select c2.*
, midnight_seconds(time(
case when tiOuTy = 's' then strip(va3)
else strip(substr(strip(va3), 2)) end ||':00')) tiOuSecs
, case when cast(start as timestamp(0)) <= current timestamp
then 1 else 0 end past
from c2
)
, c as
(
select rule cal
, cast(start as timestamp(0)) start
, smallInt( (row_number() over(partition by rule, past
order by start desc) -1 ) * past
- (row_number() over(partition by rule, past
order by start asc)) * (1 - past)) seq
, tiOuTy, tiOuSecs
, va4 calVa4
from c3
)
select *
from c
where seq between -1 and 1
;
create view oa1p.vQZ045AbUbState as
with a as
(
select rule ab
, type, subType
, char(va1, 3) rz
, char(va2, 4) dbSy
, va3, va4
from oa1p.tQZ046AbUbRule
where type in ('ab', 'abub', 'conn')
)
, c1 as
(
select rz, max(tst) tst
from oa1p.tQZ045AbUbEvent e
where ab = 'connect'
group by rz
)
, con as
(
select a.rz, e.tst conTst, e.event conEv, a.subType conPri
, ( select value(max(e.tst), current timestamp)
from oa1p.tQZ045AbUbEvent e
where ab = 'abub'
) abubTst
from c1
join a
on a.ab = 'connect' and a.type = 'conn' and a.rz = c1.rz
join oa1p.tQZ045AbUbEvent e
on e.ab = 'connect' and e.rz = c1.rz and e.tst = c1.tst
)
, b as
( -- join current next and previous calender
select ab, a.type, subType, rz, dbSy
, nx.start nxStart
, cu.start cuStart
, case when type = 'conn' then 'c'
when cu.tiOuTy is null then 'n'
else cu.tiOuTy end ct
, cu.tiouTy cuTiOuTy
, cu.tiouSecs cuTiOuSecs
, cu.calVa4
, pr.start prStart
, pr.tiouTy prTiOuTy
, pr.tiouSecs prTiOuSecs
, pr.start + pr.tiouSecs seconds prUntil
, va3, va4
from a
left join oa1p.vQZ046AbubCal nx
on a.subType = nx.cal and nx.seq = -1
left join oa1p.vQZ046AbubCal cu
on a.subType = cu.cal and cu.seq = 0
left join oa1p.vQZ046AbubCal pr
on a.subType = pr.cal and pr.seq = 1
)
, tC as
( -- find timestamp of current Event
select b.*
, ( select max(e.tst)
from oa1p.tQZ045AbUbEvent e
where b.ab = e.ab and b.rz = e.rz and b.dbSy = e.dbSy
) cuTst
from b
)
, tP as
( -- find timestamp of previous Event
select tC.*
, ( select max(e.tst)
from oa1p.tQZ045AbUbEvent e
where tC.ab = e.ab and tC.rz = e.rz and tC.dbSy = e.dbSy
and e.tst < value(min(tC.cuStart, tC.cuTst)
, tC.cuTst)
and e.event <> '' and e.event not like '>%'
) prTst
, ( select max(e.tst)
from oa1p.tQZ045AbUbEvent e
where tC.ab = 'tecSv'
and tC.ab=e.ab and tC.rz = e.rz and tC.dbSy = e.dbSy
and strip(e.link) like 'DSN.ABUB.TECSV.%.CONSUM(%)'
) csTst
from tC
)
, e as -- join events
(
select tP.*
, cu.event cuEvent, cu.link cuLink
, pr.event prEvent, pr.link prLink
, cs.event csEvent, cs.link csLink
, conTst, conEv, conPri, abubTst
, cu.cont, cu.orTst
from tP
left join oa1p.tQZ045AbUbEvent cu
on tP.ab = cu.ab and tP.rz = cu.rz and tP.dbSy = cu.dbSy
and tP.cuTst = cu.tst
left join oa1p.tQZ045AbUbEvent pr
on tP.ab = pr.ab and tP.rz = pr.rz and tP.dbSy = pr.dbSy
and tP.prTst = pr.tst
left join oa1p.tQZ045AbUbEvent cs
on tP.ab = cs.ab and tP.rz = cs.rz and tP.dbSy = cs.dbSy
and tP.csTst = cs.tst
left join con on tP.rz = con.rz
)
, e2 as
( -- compute last finished event
select
case when cuEvent not like '>%' then cuEvent
when prEvent not like '>%' then prEvent
end fiEvent
, case when cuEvent not like '>%' then cuTst
when prEvent not like '>%' then prTst
end fiTst
, e.*
from e
)
, f as -- compute toTst: timeout timestamp
( -- compute timeout timestamp toTst
select case
when fiEvent is null then '0001-01-01-00.00.00'
when ct = 's' and fiTst >= cuStart then null
when ct = 's' and current timestamp
>= cuStart + cuTiOuSecs seconds
then cuStart + cuTiOuSecs seconds
when ct = 's' and fiTst >= prStart then null
when ct = 's' then prStart + prTiOuSecs seconds
when ct = 'e' and fiTst + cuTiOuSecs seconds
<= current timestamp then fiTst + cuTiOuSecs seconds
end toTst
, e2.*
from e2
)
/* ??????????????????????????????????
when ct = 's' then case
when cuEvent not like '>%' and cuTst >= cuStart then null
when cuStart + cuTiOuSecs seconds <= current timestamp
then cuStart + cuTiOuSecs seconds
when cuEvent not like '>%' and cuTst >= prStart then null
when prEvent not like '>%' and prTst >= prStart then null
else prStart + prTiOuSecs seconds
end
when ct = 'e' then case
when cuEvent not like '>%'
and cuTst + cuTiOuSecs seconds >= current timestamp
then null
when prEvent not like '>%'
and prTst + prTiOuSecs seconds >= current timestamp
then null
when cuTst + cuTiOuSecs seconds >= current timestamp
when cuStart + cuTiOuSecs seconds <= current timestamp
then cuStart + cuTiOuSecs seconds
when cuEvent not like '>%' and cuTst >= prStart then null
when prEvent not like '>%' and prTst >= prStart then null
else prStart + prTiOuSecs seconds
when cT = 's' and (cuEvent is null or cuEvent like '>%')
then cuStart+cuTiOuSecs seconds
when cT = 'e' and not (cuEvent is null or cuEvent like '>%')
then cuTst+cuTiOuSecs seconds
when cT = 'e'
then value(prTst + cuTiOuSecs seconds
, '0001-01-01-00.00.00')
end toTst
, e.*
from e
) ?????????????????????? */
, g as -- compute timeout: timeout text
( -- compute timeout timestamp toTst
select
case
when toTst is not null and (conEv is null
or conEv <> 'ok') then 'connTimeout'
when toTst is not null and conTst + cuTiOuSecs seconds
> current timestamp then 'connRestart'
when toTst is not null then 'timeout'
when cT = 's' and fiTst > cuStart + cuTiOuSecs seconds
then 'late'
when cT = 's' and fiTst < cuStart
and fiTst > prStart + prTiOuSecs seconds
then 'late'
when cT = 'e' and cuEvent not like '>%'
and prEvent not like '>%'
and cuTst > prTst + prTiOuSecs seconds
then 'late'
else ''
end timeout
, f.*
from f
)
, h as
( -- compute alarm and status
select substr(case
when fiEvent <> 'ok' then fiEvent
when timeout <> '' then timeout
when ab = 'tecSv' and rz = 'RZ2'
and dbSy in ('DBOF', 'DVBP') then 'prod'
else 'ok'
end, 1, 8) status
, case
when fiTst > abubTst then 'new'
when toTst > abubTst then 'new'
when toTst is null and nxStart > cuStart + 3 days
and cuTst < current timestamp - 1 day
then 'old' else ''
end alarm
, g.*
from g
order by conPri, rz, dbSy, ab
)
select rz, dbSy, ab
, alarm, status, timeout, cuEvent, cuTst, cuLink
, csEvent, csTst, csLink
, prEvent, prTst, prLink
, type, va3, va4
, cont, orTst, calVa4
, fiEvent, fiTst
, conTst, conEv, conPri, abubTst
, ct, nxStart
, cuStart, cuTiOuSecs, cuTiOuTy
, prStart, prTiOuSecs, prTiOuTy
from h
;
select * from oa1p.vQZ045AbUbState
;
rollback