zOs/SQL/XC0518RT

select *
    from oa1p.tqz005tecSvRtsLastEq;x;
with s as
(
  select *
    from OA1P.TQZ006GBGRTSSTATS a
    where rz = 'RZ2' and dbSys = 'DBOF' and dbName = 'XC01A1P'
        and name = 'A200A00' and state = 'a'
)
, n as
(
  select *
    from s a
    where updatestatstime
      = ( select max(b.updatestatstime)
            from s b
            where a.rz = b.rz and a.dbSys = b.dbSys
                and a.dbName = b.dbName and a.name = b.name
                and a.partition = b.partition
                and a.instance  = b.instance
        )
)
, d as
(
  select
    ( select max(a.updatestatsTime)
        from s a
        where a.rz = n.rz and a.dbSys = n.dbSys
                and a.dbName = n.dbName and a.name = n.name
                and a.partition = n.partition
                and a.instance  = n.instance
                and not
            (   a.totalRows         = n.totalRows
            and a.nActive           = n.nActive
            and a.nPages            = n.nPages
            and a.reorgInserts      = n.reorgInserts
            and a.REORGDELETES      = n.REORGDELETES
            and a.REORGUPDATES      = n.REORGUPDATES
            and a.REORGMASSDELETE   = n.REORGMASSDELETE
            and a.dataSize          = n.dataSize
    )       ) difNew
    , n.*
    from n
)
, e as
(
  select
    ( select min(updatestatsTime)
        from s a
        where a.rz = d.rz and a.dbSys = d.dbSys
                and a.dbName = d.dbName and a.name = d.name
                and a.partition = d.partition
                and a.instance  = d.instance
                and a.updateStatsTime > d.difNew
    ) eqOld
    , d.*
    from d
)
select dbName db, name ts, instance inst, partition part
      , eqOld, updateStatsTime, difNew
    from e
    order by db, ts, part
; x;
select *
    from OA1P.TQZ006GBGRTSSTATS a
    where rz = 'RZ2' and dbSys = 'DBOF' and dbName = 'XC01A1P'
        and name = 'A200A00' and partition = 1
    order by updatestatstime desc
select * from e ;X;
                and a.updateStatsTime > u.updateStatsTime
    union all select l+1, db, ts, part, inst, new, a.updateStatsTime
        , tot, nAct, nPag, rIns, rDel, rUpd, rMas, dat, u.rz, u.dbSys
      from u
        join OA1P.TQZ006GBGRTSSTATS a
            on a.rz = u.rz and a.dbSys = u.dbSys
                and a.dbName = u.db and a.name = u.ts
                and a.partition = u.part
                and a.instance  = u.inst
                and a.updateStatsTime =
        ( select max(b.updatestatstime)
            from OA1P.TQZ006GBGRTSSTATS b
            where a.rz = b.rz and a.dbSys = b.dbSys
                and a.dbName = b.dbName and a.name = b.name
                and a.partition = b.partition
                and a.instance  = b.instance
                and b.state = 'a'
                and b.updateStatsTime < u.old
        )
            and a.totalRows = u.tot and a.nActive = u.nAct
            and a.nPages = u.nPag
            and a.reorgInserts = u.rIns and a.REORGDELETES = u.rDel
            and a.REORGUPDATES = u.rUpd and a.REORGMASSDELETE = u.rMas
            and a.dataSize = u.dat
      where l < 1000
)
, r as
(
  select row_number() over ( partition by db, ts, part, inst
                             order by l desc ) rn
      , u.*
    from u
)
select * from r
    where rn = 1
    order by db, ts, part
;
select *
    from OA1P.TQZ006GBGRTSSTATS a
    where rz = 'RZ2' and dbSys = 'DBOF' and dbName = 'XC01A1P'
        and name = 'A200A00' and partition = 1
    order by updatestatstime desc
;x;
--- mit recursivem sql ------------------------------------------------
with u (l, db, ts, part, inst, new, old, tot, nAct, nPag, rIns, rDel
             , rUpd, rMas, dat, rz, dbSys) as
(
  select 0, dbName, name, partition, instance
      , updatestatstime, updatestatstime
      , totalRows, nActive, nPages
      , reorgInserts, REORGDELETES,  REORGUPDATES, REORGMASSDELETE
      , dataSize, rz, dbSys
    from OA1P.TQZ006GBGRTSSTATS a
    where rz = 'RZ2' and dbSys = 'DBOF' and dbName = 'XC01A1P'
        and name = 'A200A00' -- and partition = 1
        and updatestatstime =
        ( select max(b.updatestatstime)
            from OA1P.TQZ006GBGRTSSTATS b
            where a.rz = b.rz and a.dbSys = b.dbSys
                and a.dbName = b.dbName and a.name = b.name
                and a.partition = b.partition
                and a.instance  = b.instance
                and b.state = 'a'
        )
    union all select l+1, db, ts, part, inst, new, a.updateStatsTime
        , tot, nAct, nPag, rIns, rDel, rUpd, rMas, dat, u.rz, u.dbSys
      from u
        join OA1P.TQZ006GBGRTSSTATS a
            on a.rz = u.rz and a.dbSys = u.dbSys
                and a.dbName = u.db and a.name = u.ts
                and a.partition = u.part
                and a.instance  = u.inst
                and a.updateStatsTime =
        ( select max(b.updatestatstime)
            from OA1P.TQZ006GBGRTSSTATS b
            where a.rz = b.rz and a.dbSys = b.dbSys
                and a.dbName = b.dbName and a.name = b.name
                and a.partition = b.partition
                and a.instance  = b.instance
                and b.state = 'a'
                and b.updateStatsTime < u.old
        )
            and a.totalRows = u.tot and a.nActive = u.nAct
            and a.nPages = u.nPag
            and a.reorgInserts = u.rIns and a.REORGDELETES = u.rDel
            and a.REORGUPDATES = u.rUpd and a.REORGMASSDELETE = u.rMas
            and a.dataSize = u.dat
      where l < 1000
)
, r as
(
  select row_number() over ( partition by db, ts, part, inst
                             order by l desc ) rn
      , u.*
    from u
)
select * from r
    where rn = 1
    order by db, ts, part
;
select *
    from OA1P.TQZ006GBGRTSSTATS a
    where rz = 'RZ2' and dbSys = 'DBOF' and dbName = 'XC01A1P'
        and name = 'A200A00' and partition = 1
    order by updatestatstime desc