zOs/SQL/DIRLGRNV

with g as
(
  select lgrdbid, lgrpsid, lgrPart
       , oa1p.fqzC2I2(LGRPSID) psID
       , LGRPART PA
       , count(*) cnt
       , sum(case when lgrelrsn = x'00000000000000000000'
                   then 1 else 0 end) cntOpen
       , max(translate('20yz-mn-de-', lgrucdt, 'mndeyz')
           || translate('hi.mn.st.uv', lgructm, 'himnstuv')) modMax
       , min(lgrslrsn) sLrsnMin
       , max(lgrslrsn) sLrsnMax
       , max(case when lgrelrsn = x'00000000000000000000'
                  then lgrslrsn
                  else x'00000000000000000000' end) sOpenMax
       , max(lgrElrsn) eLrsnMax
    from sysibm.syslgrnx l
    group by lgrdbid, lgrpsid, lgrPart
)
, h as
(
  select oa1p.fqzC2I2(LGRDBID) dbID
       , oa1p.fqzC2I2(LGRPSID) psID
       , LGRPART PA
       , cnt, cntOpen
       , timestamp(modMax) modMax
       , timestamp(substr(slrsnMin, 2, 8)) + 7174 seconds slrsnMin
       , timestamp(substr(slrsnMax, 2, 8)) + 7174 seconds slrsnMax
       , timestamp(substr(sOpenMax, 2, 8)) + 7174 seconds sOpenMax
       , timestamp(substr(eLrsnMax, 2, 8)) + 7174 seconds elrsnMax
    from g
)
, l as
(
  select h.*
      , case when cntOpen = 0 then eLrsnMax
                              else current timestamp end lgrLast
    from h
)
select s.dbName, s.name, p.partition, l.*
      , (days(lgrLast) - days(lastDataChange)) * 86400
        + midnight_seconds(lgrLast) - midnight_seconds(lastDataChange)
        + (microsecond(lgrLast) - microsecond(lastDataChange))
               / float(1e6) diff
      , r.lastDataChange
      , r.*
    from sysibm.sysTableSpace s
      join sysibm.sysTablePart p
        on s.dbName = p.dbName and s.name = p.tsName
      left join l
        on l.dbid = s.dbid and l.psid = s.psid and l.pa = p.partition
      left join sysibm.sysTableSpaceStats r
        on s.dbid = r.dbid and s.psId = r.psId
          and p.partition = r.partition
    where s.dbName like '%'
         and lastDataChange > '2016-04-01-00.00.00'
         and sLrsnMax is not null
    order by diff desc -- 1, 2, 3
    fetch first 1000 rows only
    with ur
;x;
;x;
with r as
(
  SELECT l.*
      ,row_number() over(partition by lgrdbid, lgrpsid, lgrpart
                         order by lgrSlrsn desc) rSt
      ,row_number() over(partition by lgrdbid, lgrpsid, lgrpart
                         order by lgrElrsn desc) rEn
    from sysibm.syslgrnx l
)
, s as
(
  select oa1p.fqzC2I2(LGRDBID) dbID
       , oa1p.fqzC2I2(LGRPSID) psID
       , LGRPART PA
       , TRANSLATE('20YZ-MN-DE-', LGRUCDT, 'MNDEYZ')
           || TRANSLATE('HI.MN.ST.UV', LGRUCTM, 'HIMNSTUV') mod
       , timestamp(substr(lgrslrsn, 2, 8)) slrsn
       , timestamp(substr(lgrElrsn, 2, 8)) elrsn
       , rSt, rEn, hex(lgrslrsn) lgrslrsn
                 , hex(lgrElrsn) lgrElrsn
       , case when lgrelrsn = x'00000000000000000000'
              then 'zero' else 'notZ' end eZero
       , case when lgrelrsn = ''
              then 'empt' else 'notE' end eEmpty
    from r
    where rSt = 1  or rEn = 1
)
select * from s
    where rst <> ren
    order by dbId, psId, pa
;x;
select s.dbid, s.psid, pa mod, sLrsn, eLrsn, lastDataChange
    from s
      left join sysibm.sysTableSpaceStats r
        on s.dbid = r.dbid and s.psId = r.psId and s.pa = r.partition
    order by dbId, psId, pa
;x;
(
  SELECT LGRDBID, lgrpsid
     , LGRPART PA
     , COUNT(*) CNT
     , MAX(TRANSLATE('20YZ-MN-DE-', LGRUCDT, 'MNDEYZ')
           || TRANSLATE('HI.MN.ST.UV', LGRUCTM, 'HIMNSTUV')) lgrUcTm
     , MAX(LGRSLRSN) lgrSLrsn
    FROM SYSIBM.SYSLGRNX
    GROUP BY LGRDBID, LGRPSID, LGRPART
)
, h as
(
  select oa1p.fqzC2I2(LGRDBID)dbID
       , oa1p.fqzC2I2(LGRPSID) psID
       , pa
       , cnt
       , timestamp(lgrUcTm) start
       , TIMESTAMP(SUBSTR(lgrSLrsn, 2, 8)) sLrsn
    from g
)
SELECT DBNAME DB, NAME TS, PA
     , VALUE(START, '1111-11-11-11.11.11') START
     , VALUE(SLRSN, '1111-11-11-11.11.11') SLRSN
     , CNT
   FROM h
     JOIN SYSIBM.SYSTABLESPACE S
     ON h.dbID = S.dbID AND h.psID = s.psID
WITH UR
;x;
with w as
(
  select timestamp, icType
      , (  timestamp(substr(start_rba, 2, 8))
              + current time zone) - 26 seconds start
    from sysibm.sysCopy
    where timestamp > '2016-04-01-00.00.00'
)
, d as
(
  select icType
      , (days(timestamp) - days(start)) * 86400
        + midnight_seconds(timestamp) - midnight_seconds(start)
        + (microsecond(timestamp) - microsecond(start)) / float(1e6)
    dSta
    from w
)
select count(*), icType, min(dSta) staMin, max(dSta) staMax
    from d
   group by ictype
   with ur
;x;
select current timestamp, current time zone
      , current timestamp - current time zone
     from sysibm.sysDummy1
;
select current timestamp, current time zone
      , current timestamp - current time zone
      , count(*)
       , max(translate('20yz-mn-de-', lgrucdt, 'mndeyz')
           || translate('hi.mn.st.uv', lgructm, 'himnstuv')) modMax
       , timestamp(substr(max(lgrslrsn), 2, 8)) slrsn
       , timestamp(substr(max(lgrElrsn), 2, 8)) elrsn
    from sysibm.syslgrnx l
    with ur
;
select current timestamp, current time zone
      , current timestamp - current time zone
     from sysibm.sysDummy1
;
;x;
select current timestamp, count(*)
       , max(updatestatstime), max(lastDataChange)
    from sysibm.sysTableSpaceStats
;x;