zOs/SQL/ABUBDDL

set current sqlid = 'S100447';
drop  view oa1p.vQZ045AbUbLast ;
drop  view oa1p.vQZ046AbUbCal ;
create view oa1p.vQZ045AbUbLast as
with r 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')
)
, s as
(
   select r.*
      , (select max(e.tst)
           from oa1p.tQZ045AbUbEvent e
           where r.ab = e.ab and r.rz = e.rz and r.dbSy = e.dbSy
        ) evTst
     from r
)
, l as
(
  select s.*, e.event, e.orTst, e.link, e.cont
    from s
      left join oa1p.tQZ045AbUbEvent e
        on s.ab = e.ab and s.rz = e.rz and s.dbSy = e.dbSy
          and s.evTst = e.tst
)
select f.*, c.evTst conTst, c.event conEv, c.subType conPri
       , ( select evTst from s where s.ab = 'abub') abubTst
    from l f
      left join l c
        on c.ab = 'connect' and c.type = 'conn'
            and c.rz = f.rz
;
create view oa1p.vQZ046AbUbCal as
with d (dt, x) as
(
   select current date, 0 from sysibm.sysDummy1
   union all select dt - 1 days, x+1 from d where x < 8
)
, 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%'
)
, s 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
        case when subType = 'truncHH'
                 then trunc_timestamp(current timestamp, 'hh')
             when subType = 'truncDD'
                 then trunc_timestamp(current timestamp, 'dd')
             else raise_error(77702, 'cal bad subType ' || subType)
        end start, r.*
     from oa1p.tQZ046AbUbRule r
     where r.type = 'cal' and r.subType like 'trunc%'
  union all select
        case when subType = 'truncHH'
                 then trunc_timestamp(current timestamp, 'hh') - 1 hour
             when subType = 'truncDD'
                 then trunc_timestamp(current timestamp, 'dd') - 1 day
             else raise_error(77702, 'cal bad subType ' || subType)
        end start, r.*
     from oa1p.tQZ046AbUbRule r
     where r.type = 'cal' and r.subType like 'trunc%'
)
, o1 as
(
  select s.*
      , case when strip(va3) like 'e%' then 'e' else 's' end tiOuTy
    from s
)
, o2 as
(
  select o1.*
      , midnight_seconds(time(
          case when tiOuTy = 's' then strip(va3)
               else strip(substr(strip(va3), 2)) end ||':00')) tiOuSecs
    from o1
)
select rule cal
      , cast(start as timestamp(0)) start
      , tiOuTy, tiOuSecs
      , va4 calVa4
  from o2
;
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
;
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
)
, s1 as
(
  select           -- s1 = timout or alarmTimeout (new)
        case when event is null or evTst is null then ' t'
             when cal is null                    then ''
             when evTst >= start and event not like '>%' then ''
             when until <  abubTst               then ' t'
             when until <  current timestamp     then 'at'
             when evTst >= start or tiOuTy = 'e' then ''
             when evTst < prStart or event like '>%' then ' t'
                                                 else ''
        end s1
        , r.*
        from r
)
, s2 as
(
  select
        case
          when type = 'conn' then  -- connection: alarm?
            case when conTst is null    then ' timeout'
                 when conTst >= abubTst then 'a' || event
                                        else ' ' || event
            end
          when s1 = '' then        -- event: in progress or alarm?
            case when event like '>%' then ' >'
                 when event = 'ok' or evTst <= abubTst then ' '||event
                                                       else 'a'||event
            end
          else                     -- timeout: is connection the reason?
            case when conEv is null or conEv <> 'ok' then 'cconTimeout'
                 when conTst + tiOuSecs seconds > current timestamp
                                               then 'cconRestart'
                 else left(s1, 1) || 'timeout'
            end
        end s2
      , s1.*
    from s1
)
, o as
(
  select rz, dbSy, ab
      , substr(s2, 1, 1) alarm
      , substr(s2, 2, 10) state
  --  , s1
      , event, evTst, orTst, link, cont
      , start, until, cal, tiOuTy, tiOuSecs, calVa4, prStart
      , conEv, conTst
      , abubTst
      , type, subType, va3, va4
    from s2
    order by conPri, dbSy, ab
)
select * from o
;
rollback