zOs/SQL/GBGRMIG2

with s as
(
  select t.rz, t.dbsys, t.dbName, t.name, t.partition, t.instance
    , t.loadTs, t.updateStatsTime, t.origStatstime
    , ( select k.loadTs
          from oa1p.tqz666gbgrTsStats k
        where t.rz = k.rz and t.dbSys = k.dbSys
          and t.dbName = k.dbName and t.name = k.name
          and t.partition = k.partition and t.instance = k.instance
          and t.updateStatsTime > k.updateStatsTime
        order by k.updateStatsTime desc
        fetch first 1 rows only
       ) loadPrec
    from oa1p.tqz666gbgrTsStats t
)
select *
    from s
    where loadPrec >= loadTs
;X;
with g2 as
(
  select rz, dbSys, loadTs, max(updateStatsTime) stats
    , days(max(updateStatsTime)) - days(loadTs) d1
    from oa1p.tqz006gbgrTsStats
    where rz = 'RZ2' and dbSys = 'DP2G'
         and loadTs > current timestamp - 1 month
    group by rz, dbSys, loadTs
)
, g  as
(
  select g2.*
    , case when stats <= loadTs then 0
           when stats - (d1-1) days <= loadTs       then d1-1
           when stats - (d1  ) days <= loadTs       then d1
           else d1+1 end di
    from g2
)
, j as
(
  select t.rz, t.dbsys, t.dbName, t.name, t.partition, t.instance
    , t.loadTs, t.updateStatsTime
    , case when t.updateStatsTime - g.di days <= t.loadTs
           then t.updateStatsTime - g.di days
           else t.loadTs end statsNew
    from oa1p.tqz006gbgrTsStats t join g
      on t.rz = g.rz and t.dbSys = g.dbSys and t.loadTs = g.loadTs
)
, k as
(
  select j.*
    , min(statsNew) over
      ( partition by rz, dbsys, dbName, name, partition, instance
        order by loadTs, statsNew
        rows between 1 preceding and 1 preceding
      ) statsPrec
    from j
)
select  t.STATE
      , t.RZ
      , t.DBSYS
      , t.TSTYPE
      , t.TSTY
      , t.PGSIZE
      , t.SEGSIZE
      , t.PARTS
      , t.MAXPARTS
      , t.DSSIZE
      , t.DSGB
      , t.LIMGB
      , t.LIMPART
      , t.OBID
      , t.CLONE
      , t.TSINST
      , t.TBCR
      , t.TB
      , t.TBTY
      , t.TBOBID
      , t.LOADTS
      , t.UPDATESTATSTIME ORIGSTATSTIME
      , case when k.statsPrec is null or k.statsPrec < k.statsNew
                 then k.statsNew else k.loadTs end UPDATESTATSTIME
      , t.NACTIVE
      , t.NPAGES
      , t.EXTENTS
      , t.LOADRLASTTIME
      , t.REORGLASTTIME
      , t.REORGINSERTS
      , t.REORGDELETES
      , t.REORGUPDATES
      , t.REORGUNCLUSTINS
      , t.REORGDISORGLOB
      , t.REORGMASSDELETE
      , t.REORGNEARINDREF
      , t.REORGFARINDREF
      , t.STATSLASTTIME
      , t.STATSINSERTS
      , t.STATSDELETES
      , t.STATSUPDATES
      , t.STATSMASSDELETE
      , t.COPYLASTTIME
      , t.COPYUPDATEDPAGES
      , t.COPYCHANGES
      , t.COPYUPDATELRSN
      , t.COPYUPDATETIME
      , t.IBMREQD
      , t.DBID
      , t.PSID
      , t.PARTITION
      , t.INSTANCE
      , t.SPACE
      , t.TOTALROWS
      , t.DATASIZE
      , t.UNCOMPRESSEDDATASIZE
      , t.DBNAME
      , t.NAME
      , t.REORGCLUSTERSENS
      , t.REORGSCANACCESS
      , t.REORGHASHACCESS
      , t.HASHLASTUSED
      , t.DRIVETYPE
      , t.LPFACILITY
      , t.STATS01
    from oa1p.tqz006gbgrTsStats t join k
      on t.rz = k.rz and t.dbSys = k.dbSys and t.loadTs = k.loadTs
          and t.dbName = k.dbName and t.name = k.name
          and t.partition = k.partition and t.instance = k.instance
          and t.updateStatsTime = k.updateStatsTime
;x;??????????
select count(*)
, sum(case when statsNew <= statsPrec then 1 else 0 end) bef
, count(distinct loadTs)
    from k
;x; where stMod is null or loadTs is null or stats is null
select statsNew, statsPrec, loadTs
    from k
    where statsPrec >= statsNew
;x;
select count(*), count(distinct loadTs)
    from k
;x; where stMod is null or loadTs is null or stats is null
        or stMod > loadTs or stMod <= loadTs - 1 day
        or d1 <> di
    order by 1 desc
;x;
with t (t) as
(
  select current timestamp from sysibm.sysDummy1
  union all select timestamp('1956-04-01-06.00.00')from sysibm.sysDummy1
  union all select timestamp('2015-01-01-01.00.00')from sysibm.sysDummy1
)
, j2 (f, t) as
(
  select f.t, t.t
    from t f, t t
    where f.t <= t.t
)
, j3 as
(
select j2.*
        , days(t) - days(f) di
        , days(t) - days(f)
           + (midnight_seconds(t) - midnight_seconds(f)
             + float(microsecond(t) - microsecond(f)) * 1e-6
             ) / 86400 dd
    from j2
)
, j as
(
  select j3.*
    , case when t - di days     between f - 1 day and f then di
           when t - (di-1) days between f - 1 day and f then di - 1
           when t - (di+1) days between f - 1 day and f then di + 1
      end dc
    from j3
)
select days(f), midnight_seconds(f), f
        , di, dc, t - dc days
        , t
        , decimal(dd, 20, 12)
        , decimal(mod(dd, 1), 20, 12)
        , dec(mod(dd, 1) * 86400, 12, 6)
    from j
;x;
(days(t)-days(b)) * 86400 "days*86400",
(days(t)-days(b)) * 86400
  + midnight_seconds(t) -  midnight_seconds(b) "days* midnight",
from sysibm.sysDummy1
select timestamp('2012-02-29-12:13:14   ')
    from sysibm.sysDummy1
;
declare global temporary table tst (tst timestamp);
insert into session.tst values (current timestamp);
insert into session.tst values ('2012-02-29 12:13:14   ');
insert into session.tst values ('2012-02-29-12:13:14   ');
select * from session.tst;
                 -- timestamp format: : statt . funktioniert nur <= v9|