zOs/SQL/REORUCHK

-----------------------------------------------------------------------
-- test tReoRun* tables
-- if necessary fix them
-----------------------------------------------------------------------
select *
    from S100447.vReoRunRngChk
    where e is not null
        -- and tst > current timestamp - 5 days
    order by tst desc, rng asc
;
--select *
--    from s100447.treoRunPart
--    where tst = '2011-08-28-13.03.18.303014'
--    order by tst desc, rng asc ;

select eoj, j.*
    from s100447.vReoRunJobChk j
    where e is not null
    order by job, tst desc
    with ur
;;;;
update s100447.tReoRunJob j
    set eoj = (select eo2
                   from s100447.vReorunJobChk e
                   where e.job = j.job and e.tst = j.tst)
    where (job, tst) in
          (select job, tst from s100447.vReorunJobChk e
                           where e.e is not null
          )
;
select eoj, j.*
    from s100447.vReoRunJobChk j
    where e is not null
    order by job, tst desc
    with ur
;
rollback;
commit;                                                               m
;;;;
------------------------------------------------------------------------
-- test jobs with/without reorgs
--
select j.tst, min(j.job),
        count(*) parts,
        sum(case when p.part = p.pavon then 1 else 0 end) rngs,
        sum(case when p.part = p.pavon and p.reoTime is null
             then 1 else 0 end) rngReoNull
    from s100447.tReoRunJob j
      left join s100447.tReoRunPart p
        on j.tst = p.tst
    where job not like 'QR%'
    group by j.tst
    order by tst desc
;
------------------------------------------------------------------------
-- delete test jobs without reorgs
--     first update then delete,
--         because direct delete is disallowed because of triggers
--
update         s100447.tReoRunJob j set ty = 'de'
    where job not like 'QR%'
      and not exists (select 1
          from s100447.tReoRunPart p
          where j.tst = p.tst
              and p.part = p.pavon and p.reoTime is not null
          )
;
delete from    s100447.tReoRunJob
    where ty = 'de'
;
rollback;
commit;
;;;