zOs/SQL/XBREDO

set current path oa1p;
set current application compatibility 'V11R1';
with q as
(
  select qq.*
        , case when posStr(lgRnRecLoad, 'unlTst<lastEq') < 1 then ''
               when lastEq < unlTst + 30 minutes then ' leq<30min'
               when lastEq < '2014-07-23-00.00.00' then ' leq<2014'
               else '' end leqTxt
      from oa1p.vQz005RecovDeltaLoadLgRn qq
      where db like 'XB%' -- and db = 'XBDL1001'
)
, r as
(
  select db, ts, pa
        , max(value(pSpc, 0), value(rSpc, 0), 0) * 1024.0 spc
        , stage
        , case when stage not in ('-r', '-w', 'CL', 'DL', 'RW', 'UL')
                   then 'fixMeta'
               when stage in ('UL', 'DL')
                   and substr(lgRnRecLoad, 1, 1) <> 'l'
                   and lgRnRecLoad not in ('r ok:F=fulCopy ok'
                         ,'r ok:F=fulCopy ok lgRnNone')
                   and not ( leqTxt <> '' and lgRnRecLoad in
                         ('? unlTst<lastEq'
                         ,'? unlTst<lastEq lgRnNone') )
                   then 'redoUnl'
               when substr(lgRnRecLoad, 1, 1) not in ('r', 'l')
                   and not ( leqTxt <> '' and lgRnRecLoad in
                         ('? unlTst<lastEq'
                         ,'? unlTst<lastEq lgRnNone') )
                   then 'recErr' else '' end  redo
        , lgRnRecLoad || ' ' || leqTxt recoveryState
        , staUpd
        , unlTst
        , lastEq
      from q
)
select substr(fosFmtE7(sum(spc))
                 !! right('       ' !! count(*), 8), 1, 15)
                   "spaceBy   count"
        , redo
        , stage
        , substr(recoverySTate, 1, 70) recoveryState
        , min(unlTst) unlFrom
        , max(unlTst) unlTo
        , min(staUpd) staFrom
        , max(staUpd) staTo
    from r
    group by grouping sets ((), (redo)
          , (redo, stage, recoveryState) )
       order by 2, 3, 4
   --  order by sum(spc) desc, 2, 3, 4
;
with q as
(
  select qq.*
        , case when posStr(lgRnRecLoad, 'unlTst<lastEq') < 1 then ''
               when lastEq < unlTst + 30 minutes then ' leq<30min'
               when lastEq < '2014-07-23-00.00.00' then ' leq<2014'
               else '' end leqTxt
      from oa1p.vQz005RecovDeltaLoadLgRn qq
      where db like 'XB%' -- and db = 'XBDL1001'
)
, r as
(
  select db, ts, pa
        , max(value(pSpc, 0), value(rSpc, 0), 0) * 1024.0 spc
        , stage
        , case when stage not in ('-r', '-w', 'CL', 'DL', 'RW', 'UL')
                   then 'fixMeta'
               when stage in ('UL', 'DL')
                   and substr(lgRnRecLoad, 1, 1) <> 'l'
                   and lgRnRecLoad not in ('r ok:F=fulCopy ok'
                         ,'r ok:F=fulCopy ok lgRnNone')
                   and not ( leqTxt <> '' and lgRnRecLoad in
                         ('? unlTst<lastEq'
                         ,'? unlTst<lastEq lgRnNone') )
                   then 'redoUnl'
               when substr(lgRnRecLoad, 1, 1) not in ('r', 'l')
                   and not ( leqTxt <> '' and lgRnRecLoad in
                         ('? unlTst<lastEq'
                         ,'? unlTst<lastEq lgRnNone') )
                   then 'recErr' else '' end  redo
        , lgRnRecLoad || ' ' || leqTxt recoveryState
        , staUpd
        , unlTst
        , lastEq
      from q
)
select substr(db, 1, 8) db
      , substr(ts, 1, 8) ts
      , smallint(pa) pa
      , redo
      , stage
      , substr(fosFmtE7(spc), 1, 7) spcBy
      , substr(recoveryState, 1, 60) recoveryState
      , staUpd
      , unlTst
      , lastEq
    from r
    where redo not in ('', 'recErr')
    order by redo, 1, 2, 3
;