zOs/SQL/ABUBSE2

select * from oa1p.vqz046AbubCal
    where cal = 'logEx'
;
select * from oa1p.vqz046AbubCur
    where cal = 'logEx'
;
select * from oa1p.vQZ045AbubState -- where rz = 'RR2'
;x;
with r as
-- create view oa1p.vQZ046AbUbCur as
with c as
(
   select c.*
       , row_number() over (partition by cal order by start desc) rn
     from oa1p.vQZ046AbUbCal c
     where start <= current timestamp
)
select a.cal, a.start, a.tiOuTy, a.tiOuSecs, a.calVa4
            , b.start prStart
   from c a left join c b on a.cal = b.cal and b.rn = 2
   where a.rn = 1
      and a.cal = 'logEx'
;
-- create view oa1p.vQZ045AbubState as
with r as
(
  select cast( case when tiOuTy = 'e' then evTst else start end
                + tiOuSecs seconds as timestamp(0)) until
      , r.*, c.*
    from oa1p.vQZ045AbUbLast r
      left join oa1p.vQZ046AbUbCur c
         on r.type = 'ab' and r.subType = c.cal
)
, s as
(
  select
        case when event is null then 'timeout'
             when cal is null   then event
             when evTst >= start and event in ('ok', 'err') then event
             when evTst >= start and until > current timestamp then '>'
             when evTst >= prStart and until > current timestamp
                  and event in ('ok', 'err') then event
             when conEv = 'err' then 'conTimeout'
             when conTst + tiOuSecs seconds > current timestamp
                                               then 'conRestart'
             else 'timeout'
        end state
      , r.*
    from r
)
, o as
(
  select rz, dbSy, ab
      , char(case when type = 'conn' and conTst > abubTst then 'a'
             when type = 'conn' or state in ('ok', '>')   then ''
             when state in ('conTimeout', 'conRestart')   then 'c'
             when evTst is Null or evTst <= abubTst       then ''
                                                          else 'a'
        end, 1) alarm
      , char(state, 10) state
      , event, evTst, orTst, link, cont
      , start, until, cal, tiOuTy, tiOuSecs, calVa4
      , conEv, conTst
      , abubTst
      , type, subType, va3, va4
    from s
    order by conPri, dbSy, ab
)
select * from o
     where rz = 'RR2' and ab like 'logE%'
;
commit