zOs/SQL/GBGRNEW

set current application compatibility 'V11R1';
with u (l1, l2, rz, dbSys, db, ts, pa, inst
       , state, load, orig, upd
       , preState, preLoad, preOrig, preUpd, lus
       ) as
(
  select 0, 'a', rz, dbSys, dbName, name, partition, instance, ' '
      , timestamp('1111-11-11-11.11.11')
      , timestamp('1111-11-11-11.11.11')
      , timestamp('1111-11-11-11.11.11')
      , ' '
      , cast(null as timestamp)
      , cast(null as timestamp)
      , cast(null as timestamp)
      , cast('' as char(53))
    from OA1P.TQZ006GBGRTSSTATS
    where rz = 'RR2'  --and dbSys = 'DBOF'
       --  and dbName like 'MF01A%'
    group by rz, dbSys, dbName, name, partition, instance
  union all select l1+1, 'b', rz, dbSys, db, ts, pa, inst
      , state, load, orig, upd, preState, preLoad, preOrig, preUpd
      , ( select char(n.loadTs) || char(n.updateStatsTime) || n.state
            from OA1P.TQZ006GBGRTSSTATS n
            where u.rz = n.rz and u.dbSys = n.dbSys
                and u.db = n.dbName and u.ts = n.name
                and u.pa = n.partition and u.inst = n.instance
                and u.load <= n.loadTs
                and (u.load < n.loadTs or u.orig < n.updateStatsTime)
            order by n.loadTs asc, n.updateStatsTime asc
            fetch first 1 rows only
        ) lus
    from u where l1 < 99999 and l2 in ( 'a', 'd')
  union all select l1+1, 'c', rz, dbSys, db, ts, pa, inst
      , substr(lus, 53)
      , timestamp(substr(lus,  1, 26))
      , timestamp(substr(lus, 27, 26))
      , cast(null as timestamp)
      , state, load, orig, upd, lus
    from u
    where l1 < 99999 and l2 = 'b' and lus is not null
  union all select l1+1, 'd', rz, dbSys, db, ts, pa, inst
      , state, load, orig
      , case when orig > preUpd and orig <= load then orig
             when   orig -(  days(orig)-days(load)) days > preUpd
               and  orig -(  days(orig)-days(load)) days <= load
               then orig -(  days(orig)-days(load)) days
             when   orig -(1+days(orig)-days(load)) days > preUpd
               and  orig -(1+days(orig)-days(load)) days <= load
               then orig -(1+days(orig)-days(load)) days
             when load > preUpd then load
             else  preUpd + 1e-6 seconds end
      , preState, preLoad, preOrig, preUpd, lus
    from u
    where l1 < 99999 and l2 = 'c' and lus is not null
)
select load, state, count(*)
   , sum(case when upd = orig then 0 else 1 end) neq
   , sum(case when state = 'd' and preState = 'd' then 1
              when state = preState and orig = preOrig then 1
              else 0 end) del
   , sum(case when orig < preOrig then 1 else 0 end) origLsPre
    from u
    where l2 = 'd'
    group by cube(load, state)
    order by load, state
-- er by rz, dbSys, db, ts, pa, inst, l1
;
;x
?elect current timestamp
     , timestamp(date('01.04.1956'), time(current timestamp))
     , timestamp(date(current timestamp), time('13:12:11'))
    from sysibm.sysDummy1 ;
with d (orig, load, preUpd) as
(
    select current timestamp + (9*86400-1234.333) seconds
          , current timestamp
          , current timestamp - 86123.45479 seconds
        from sysibm.sysDummy1
)
select
        case when orig <= load and orig > preUpd then orig
             when   orig -(days(orig)-days(load)) days <= load
               and  orig -(days(orig)-days(load)) days > preUpd
               then orig -(days(orig)-days(load)) days
             when   orig -(1+days(orig)-days(load)) days <= load
               and  orig -(1+days(orig)-days(load)) days > preUpd
               then orig -(1+days(orig)-days(load)) days
             when load >= preUpd then preUpd + 1e-6 seconds
             else load
        end
       , d.*
from d;
?elect rz, dbSys, del, sum(cBad) cBad
         , sum(case when left(del, 1) <> '-' then 1 else 0 end) delAft
         , count(*), sum(cnt) cnt
from
( -- d give reason to delete or not to
select case when cnt <= 0 then '-bad0'
            when aftLoad is null then '-aftNull ' || befState
            when aftLoad > '2015-10-22-00.00.00'
                                 then '-aftLoad> ' || befState||aftState
            when befState = 'd' and aftState = 'd' then 'dd'
            when befState <> aftState then '-state '||befState||aftState
            when befUpd = aftUpd then '=='||befState
            when befUpd > aftUpd then '=>'||befState
            else '-else ' ||befState || aftState end del
      , t.*
from
( -- t decode s
  select s.*
      , case when befLUS <> '' then timestamp(substr(befLUS, 1, 26))
             else null end befLoad
      , case when befLUS <> '' then timestamp(substr(befLUS, 27, 26))
             else null end befUpd
      , case when befLUS <> '' then substr(befLUS, 53)
             else null end befState
      , case when aftLUS <> '99' then timestamp(substr(aftLUS, 1, 26))
             else null end aftLoad
      , case when aftLUS <> '99' then timestamp(substr(aftLUS, 27, 26))
             else null end aftUpd
      , case when aftLUS <> '99' then substr(aftLUS, 53)
             else null end aftState
from
( -- s keys with duplicates
  select rz, dbSys, dbName, name, partition, instance
      , count(*) cnt
      , sum(case when loadTs <  '2015-10-14-00.00.00'
                   or loadTs >= '2015-10-16-00.00.00'
                 then 0 else 1 end) cBad
      , max(case when loadTs <  '2015-10-14-00.00.00'
                     then char(loadTs) || char(updateStatsTime) || state
                     else '' end) befLUS
      , min(case when loadTs >= '2015-10-16-00.00.00'
                     then char(loadTs) || char(updateStatsTime) || state
                     else '99' end) aftLUS
      , max(loadTs) loadFr, max(loadTs) loadTo
    from OA1P.TQZ006GBGRTSSTATS
     -- where rz = 'RZ2'  and dbSys = 'DBOF'
      -- and dbName like 'MF01A%'
    group by rz, dbSys, dbName, name, partition, instance
) s
) t
) d
group by rollup(rz, dbSys, del)
order by rz, dbSys, del
--der by rz, dbSys, dbName, name, partition, instance
;