zOs/SQL/DIRLGRNG

set current path oa1p;
with g as
(
  select fosC2I2(lgrDBID)dbId, fosC2i2(lgrPSID) psid, lgrPart pa
     , max(timestamp(translate('20yz-mn-de-', LGRUCDT, 'mndeyz')
              || translate('hi.mn.st.uv', lgructm, 'himnstuv'))) beg
    from sysibm.sysLgRnX
    group by lgrDBID, lgrPSID, lgrPart
)
, j as
(
  select g.*, dbName db, name ts
   from g
     full outer join sysIbm.sysTableSpace s
     on g.dbId = s.dbid and g.psid = s.psid
)
select count(*), min(db), max(beg)
       , avg(float(days(beg))), avg(float(days(current timestamp)))
   from j
   group by case when dbid is null then 0 else 1 end
         ,  case when db is null then 0 else 1 end
; x
  select hex(LGRDBID) , fosC2I2(LGRDBID) dbid
     , hex(LGRpsID) , fosC2I2(LGRpsID) psid
     , lgrPart
     , lgrUcDT
     , lgrUcTM
     , hex(lgrMemb) member
     , timestamp(translate('20yz-mn-de-', LGRUCDT, 'mndeyz')
              || translate('hi.mn.st.uv', lgructm, 'himnstuv'))
     , timestamp(substr(LGRSLRSN, 2, 8)) "startLrsn"
     , timestamp(substr(LGRELRSN, 2, 8)) "endLrsn"
--   , fosC2I8(LGRSRBA)
--   , fosC2I8(LGRSPBA)
--   , fosC2I8(LGRSPBA) - fosC2I8(LGRSRBA) rbaDelta
    from sysibm.sysLgRnX
    where (lgrDbId, lgrPsId) in
          (select fqzI2c2(dbid), fqzI2C2(psid)
              from sysibm.sysTablespace
              where dbName = 'QZ01A1P' and name like 'A006A'
          )
    order by lgrslrsn desc
    fetch first 100 rows only