zOs/SQL/ABUBSELC

/*
select * from oa1p.vQZ045AbUbState
 -- order by evTst desc
; x;  */
set current sqlid = 'S100447';
create view oa1p.vQZ045AbUbStat3 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'
         ) - 2 hour 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
       , pr.start prStart
       , pr.tiouTy prTiOuTy
       , pr.tiouSecs prTiOuSecs
       , pr.start + pr.tiouSecs seconds  prUntil
       , va3, va4
     from a
     left join oa1p.vQZ046AbUbCa3 nx
       on a.subType = nx.cal and nx.seq = -1
     left join oa1p.vQZ046AbUbCa3 cu
       on a.subType = cu.cal and cu.seq = 0
     left join oa1p.vQZ046AbUbCa3 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 < case tC.ct when 's' then tC.cuStart
                                        else tC.cuTst end
                   and e.tst >= case tC.ct when 's' then tC.prStart
                                        else '0001-01-01-00.00.00' end
                   and (tC.ct <> 'e' or 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
(
     select tP.*
          , cu.event cuEvent, cu.link cuLink
          , pr.event prEvent, pr.link prLink
          , cs.event csEvent, cs.link csLink
          , conTst, conEv, conPri, abubTst
          , case when conEv is null or conEv <> 'ok' then ' connTimeout'
                 when conTst + cuTiOuSecs seconds > current timestamp
                     then ' connRestarTO' else ' timeout' end conTO
     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
)
, f as
(    -- describe current state
   select rz, dbSy, ab
       , varchar(strip(value (cuEvent, '')
       || case cT
            when 's' then
              case
                when cuEvent is null or cuEvent like '>%' then
                  case
                    when abubTst > cuStart+cuTiOuSecs seconds
                      then conTO
                    when current timestamp > cuStart+cuTiOuSecs seconds
                      then conTo || ' new' else ''
                    end
                else case when cuTst > cuStart+cuTiOuSecs seconds
                            then ' late' else '' end
                   || case when cuTst>abubTst then ' new' else '' end
                end
            when 'e' then
              case
                when cuEvent is null or cuEvent like '>%' then
                  case
                    when abubTst > prTst+prTiOuSecs seconds
                        then conTo
                    when current timestamp > prTst+prTiOuSecs seconds
                        then conTo || ' new' else ''
                    end
                when cuTst + cuTiOuSecs seconds < abubTst
                    then conTO
                when cuTst + cuTiOuSecs seconds < current timestamp
                    then conTO || ' new'
                else case when prtst + prTiOuSecs seconds < cuTst
                           then ' late' else '' end
                  || case when cutst > abubTst then ' new' else '' end
                end
            else
              case when cuTst is null    then ' timeout'
                   when cuTst > abubTst  then ' new'
                   else '' end
            end), 20) cuSta
      , cuTst, cuLink
      , prEvent, prTst, prLink
      , csEvent, csTst, csLink
      , conTst, conEv, conPri, cT, conTO, abubTst
      , cuStart, cuTiOuSecs, cuTiOuTy
      , prStart, prTiOuSecs, prTiOuTy
     from e
     order by conPri, rz, dbSy, ab
)
select * from f
;
select * from oa1p.vQZ045AbUbStat3
 ; commit
 ; x;
 select * from tP
 order by ab, rz, dbSy
 ;x;
 , s3 as
 (
   select s2.*
       , case when prTiOuTy = 'e' then e1Tst
              when type = 'conn' then e1Tst
              else cuStart
         end e2Start
       , case when prTiOuTy = 'e' then null
              when type = 'conn' then null
                  else prStart + prTiouSecs seconds
         end e2Until
     from s2
 )
 select * from s3;
 rollback;
                   and pr.start <= e.tst
         ) prTst
 , s as
 (
   select ab, r.type, subType, rz, dbSy
       , nx.start nxStart
       , cu.start cuStart
       , cu.tiouTy cuTiOuTy
       , cu.tiouSecs cuTiOuSecs
       , cu.start + cu.tiouSecs seconds  cuUntil
       , (select max(e.tst)
              from oa1p.tQZ045AbUbEvent e
              where r.ab = e.ab and r.rz = e.rz and r.dbSy = e.dbSy
                   and e.tst >=
                   case when cu.tiOuTy = 'e'
                          then current timestamp - cu.tiOuSecs seconds
                        when r.type = 'conn'
                          then current timestamp - 10 years
                        else cu.start
                   end
         ) cuTst
       , pr.start prStart
       , pr.tiouTy prTiOuTy
       , pr.tiouSecs prTiOuSecs
       , pr.start + pr.tiouSecs seconds  prUntil
       , (select max(e.tst)
              from oa1p.tQZ045AbUbEvent e
              where r.ab = e.ab and r.rz = e.rz and r.dbSy = e.dbSy
                   and cu.start > e.tst
                   and pr.start <= e.tst
         ) prTst
       , va3, va4
     from r
     left join oa1p.vQZ046AbUbCa3 nx
       on r.subType = nx.cal and nx.seq = -1
     left join oa1p.vQZ046AbUbCa3 cu
       on r.subType = cu.cal and cu.seq = 0
     left join oa1p.vQZ046AbUbCa3 pr
       on r.subType = pr.cal and pr.seq = 1
 )
   select
          value(cu.event, '')
       || case when s.type = 'conn'              then ''
               when cuTiOuTy = 'e' and cu.event is null then ' timeout'
               when current timestamp <= cuUntil then ''
               when cu.event is null             then ' timeout'
               when cu.event like '>%'           then ' timeout'
               when cu.tst <= cuUntil            then ''
                                                 else ' prte'
          end cuState
     , s.*, cu.*, pr.*
     from s
       left join oa1p.tQZ045AbUbEvent cu
           on s.ab = cu.ab and s.rz = cu.rz and s.dbSy = cu.dbSy
                   and cuTst = cu.tst
       left join oa1p.tQZ045AbUbEvent pr
           on s.ab = pr.ab and s.rz = pr.rz and s.dbSy = pr.dbSy
                   and prTst = pr.tst
     order by s.rz, s.dbSy, s.ab
 ;
 rollback
 ;
 , 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

 )
 , l as
rollback;
set current sqlid = 'S100447';
drop   view oa1p.vQZ046AbUbCa3 ;
create view oa1p.vQZ046AbUbCa3 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 < 18
)
, 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
;
select * from oa1p.vQZ046AbUbCa3
    order by cal, start desc
;
commit;x;