zOs/SQL/ABUBSEL

--select * from oa1p.vQZ045AbUbState
--;
select * from oa1p.tQZ045AbUbEvent
     where ab = 'mvExt' and rz = 'RZZ' -- and event <> '>'
       and tst > current timestamp - 9 days
    order by tst desc
    fetch first 100  rows  only;
; x;
;x;
delete from oa1p.tQZ045AbUbEvent
     where ab = 'tstXDoc'
;
commit;
;x;
select * from oa1p.tQZ045AbUbEvent
    where                ab = 'mvExt'
        and tst > current timestamp -16  days
 --     and rz = 'RZ2' and dbSy = 'DVBP'
    order by rz, tst desc
; x;
xpdate oa1p.tQZ046AbUbRule r
   set va4 = strip(left(va4, 46))
     where type = 'code' and rule = 'logEx'
;
select *
     from oa1p.tQZ046AbUbRule r
     where type = 'code' and rule = 'logEx'
;
commit
;x;
select * from oa1p.tQZ045AbUbEvent
    where                ab = 'logEx'
        and tst > current timestamp -16  days
    order by tst desc
; x;
select * from oa1p.vQZ045AbUbState
;x;
select * from oa1p.tQZ045AbUbEvent
    where rz = 'RQ2' and ab = 'logE2'
        and tst > current timestamp - 6  days
    order by tst desc
; x;
select * from oa1p.vQZ046AbUbCur;
    where (state <> 'ok' and (ab <> 'ddlCon'
               or evTst > current timestamp - 40 hours))
          or (rz = 'RZ2' and dbSy in ('DBOF', 'DVBP'))
          or alarm <> ''
;
  select *
    from oa1p.tQZ045AbUbEvent e
    order by tst desc
    fetch first 100 rows only
    ;x;
, n as
(
  select e.*, c.start, c.until
       , row_number() over(partition by e.ab, e.rz, e.dbSy
                           order by start asc) nRn
    from e
      left join oa1p.vQZ046AbUbCal c
         on e.type = 'ab' and e.subType = c.cal
           and c.start > value(e.evTst, '2000-01-01-00.00.00')
      where eRn = 1
)
, m as
(
  select n.*
        , case when conEv <> 'err'
               then conTst + (until - start) else null end conUntil
    from n
    where nRn = 1
)
select *
    from oa1p.vQZ046AbUbCur c
    order by cal, start desc
;x;
with f as
(
select c.*, case when start > current timestamp then 1 else 0 end isFut
    from oa1p.vQZ046AbUbCal c
)
select f.*
    , case when isFut = 1
 then row_number() over(partition by cal, isFut order by start asc)
 else 1 - row_number() over(partition by cal, isFut order by start desc)
 end rr
    from f
    order by cal, start desc
;x;
select f.*
    , row_number() over(partition by cal, isFut order by start asc) rA
    , row_number() over(partition by cal, isFut order by start desc) rD
    from f
    order by cal, start desc
;x;
select *
    from oa1p.vQZ045AbUbstate
    where state <> 'ok' or alarm <> '' or state is null
 -- where type = 'ab'
 ;
select *
    from oa1p.vQZ045AbUbState
    where state <> 'ok' or alarm <> ''
 ;;;;
    where ab <> 'connect' or state is not null
;;,
select *
    from oa1p.vQZ045AbUbState
;;,
with l as
(
  select *
    from oa1p.tQZ045AbUbEvent e
    where e.tst
    = (select f.tst
        from oa1p.tQZ045AbUbEvent f
        where e.ab = f.ab and e.rz = f.rz
            and e.dbSy = f.dbSy
        order by tst desc
        fetch first 1 row only
      )
)
, r as
(
  select r.rule ab, char(r.va2, 3) rz, char(r.va3, 4) dbSy
      , l.tst, l.event, l.link, r.va1 cal
    from oa1p.TQZ046AbUbRule r left join l
        on r.rule = l.ab and r.va2 = l.rz and r.va3 = l.dbSy
    where r.typ = 'ab'
)
, n as
(
  select r.*, c.start, c.until
       , row_number() over(partition by r.ab, r.rz, r.dbSy
                           order by start asc) rn
    from r left join oa1p.vQZ046Cal c
      on r.cal = c.cal and c.start > value(r.tst, '2000-01-01-00.00.00')
)
select ab, rz, dbSy
      , case when until < current timestamp then 'timeout'
                                            else event end state
      , event
      , cast(tst as timestamp(0)) tst
      , cast(start as timestamp(0)) start
      , cast(until as timestamp(0)) until
      , link, cal
    from n
    where rn = 1
;;;
  select ab, rz, dbSy, tst, event
      c.start, c.until, link, cal
    from r left join
;;;
select * FROM L;;;;
    from oa1p.vQZ046AbUbEvent
    where
select *
   from oa1p.vQZ046Cal
   order by cal, start desc
;;;
with d (dt, x) as
(
   select current date, 0 from sysibm.sysDummy1
   union all select dt- 1 day, x+1 from d where x < 5
)
, t as
(
   select timestamp(dt, strip(va2) || ':00') start
        , timestamp(dt, strip(va2) || ':00')
              + (time(strip(va3) || ':00') - time('00:00:00')) until
        , r.*
     from d join oa1p.tQZ046AbUbRule r
        on r.typ = 'cal'
)
, s as
(
   select case when sub = 'wtZe' then locate(dayOfWeek_iso(start), va1)
              else raise_error(77701, 'cal bad sub ' || sub)
         end sel
         , t.*
     from t
     where start between current timestamp - 5 days
                     and current timestamp
)
select rule cal, start, until
   from s
   where sel > 0
   order by cal, start desc
;;;
,  d2 as
(
   select timestamp(dt, tStart) start
       , timestamp(dt, tStart) + len stop
       , d1.*,r1.*
     from d1,join r1
 )
select * from d2 order by start desc, ab;;;
       , case when sub = 'wtZe' then locate(dayOfWeek_iso(start), va1)
              else raise_error(77701, 'cal bad sub ' || sub)
         end sel
select dayOfWeek_iso(start)
      , locate(dayOfWeek_iso(start), va1)
      , start + (hour(len) * 60 + minute(len)) minutes stop
      , t.*
    from t
    where locate(dayOfWeek_iso(start), va1) > 0
    order by start desc