zOs/SQL/GBGRCLEX

;x;
with s as
(
  select rz, dbSys, dbName, name, partition, instance, updatestatstime
      , count(*) cnt
    from OA1P.TQZ006GBGRTSSTATS
    where rz = 'RZ2' and dbSys = 'DBOF'
  --    and dbName in ('XC01A1P', 'XR01A1P')
  --    and (name like 'A2%' or name like 'A5%')
  group by rz, dbSys, dbName, name, partition, instance, updatestatstime
  having count(*) > 1
)
, l as
(
  select s.*
      , row_number() over(partition by s.rz, s.dbSys, s.dbName
                                     , s.name, s.partition, s.instance
                                     , s.updateSTatsTime
                          order by a.loadTs
                          ) rn
      , a.loadTs
    from s
      join OA1P.TQZ006GBGRTSSTATS a
        on a.rz = s.rz and a.dbSys = s.dbSys
                and a.dbName = s.dbName and a.name = s.name
                and a.partition = s.partition
                and a.instance = s.instance
                and a.updateStatsTime = s.updateStatsTime
)
select loadTs, count(*)
    from l
    where rn > 1
    group by loadTs
    order by 2 desc
;x;
select *
    from l
    order by rz, dbSys, dbName, name, partition, instance
               , updatestatstime, rn
;x;
, n as
(
  select nN.*
    , ( select max(nD.updatestatsTime)
        from OA1P.TQZ006GBGRTSSTATS nD
        where nS.rz = nD.rz and nS.dbSys = nD.dbSys
                and nS.dbName = nD.dbName and nS.name = nD.name
                and nS.partition = nD.partition
                and nS.instance = nD.instance and nD.state = 'a'
                and not
            (   nN.totalRows        = nD.totalRows
            and nN.nActive          = nD.nActive
            and nN.nPages           = nD.nPages
            and nN.reorgInserts     = nD.reorgInserts
            and nN.REORGDELETES     = nD.REORGDELETES
            and nN.REORGUPDATES     = nD.REORGUPDATES
            and nN.REORGMASSDELETE  = nD.REORGMASSDELETE
            and nN.dataSize         = nD.dataSize
    )       ) difNew
    from s nS
      join OA1P.TQZ006GBGRTSSTATS nN
        on nS.rz = nN.rz and nS.dbSys = nN.dbSys
          and nS.dbName = nN.dbName and nS.name = nN.name
          and nS.partition = nN.partition and nS.instance = nN.instance
          and nS.updatestatstime = nN.updateStatsTime
)
, e as
(
  select
    ( select min(updatestatsTime)
        from OA1P.TQZ006GBGRTSSTATS a
        where a.rz = d.rz and a.dbSys = d.dbSys and a.state = 'a'
                and a.dbName = d.dbName and a.name = d.name
                and a.partition = d.partition
                and a.instance  = d.instance
                and a.updateStatsTime
                   > value(d.difNew, d.updateStatsTime - 1e-6 seconds)
    ) eqOld
    , d.*
    from n d
)
select *
    from n
    where name = 'A517A'
    order by 1 desc ,   dbName, name, partition
;x;
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table  ;
delete from A540769.dsn_predicat_table;
explain plan set queryno = 3   for
with s as
(
  select rz, dbSys, dbName, name, partition, instance
        , max(updatestatstime) updatestatstime
    from OA1P.TQZ006GBGRTSSTATS s1
    where rz = 'RZ2' and dbSys = 'DBOF' and state = 'a'
        and dbName in ('XC01A1P', 'XR01A1P')
        and (name like 'A2%' or name like 'A5%')
    group by rz, dbSys, dbName, name, partition, instance
)
, n as
(
  select nN.*
    , ( select max(nD.updatestatsTime)
        from OA1P.TQZ006GBGRTSSTATS nD
        where nS.rz = nD.rz and nS.dbSys = nD.dbSys
                and nS.dbName = nD.dbName and nS.name = nD.name
                and nS.partition = nD.partition
                and nS.instance = nD.instance and nD.state = 'a'
                and not
            (   nN.totalRows        = nD.totalRows
            and nN.nActive          = nD.nActive
            and nN.nPages           = nD.nPages
            and nN.reorgInserts     = nD.reorgInserts
            and nN.REORGDELETES     = nD.REORGDELETES
            and nN.REORGUPDATES     = nD.REORGUPDATES
            and nN.REORGMASSDELETE  = nD.REORGMASSDELETE
            and nN.dataSize         = nD.dataSize
    )       ) difNew
    from s nS
      join OA1P.TQZ006GBGRTSSTATS nN
        on nS.rz = nN.rz and nS.dbSys = nN.dbSys
          and nS.dbName = nN.dbName and nS.name = nN.name
          and nS.partition = nN.partition and nS.instance = nN.instance
          and nS.updatestatstime = nN.updateStatsTime
)
, e as
(
  select
    ( select min(updatestatsTime)
        from OA1P.TQZ006GBGRTSSTATS 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.state = 'a'
                and a.updateStatsTime
                   > value(d.difNew, d.updateStatsTime - 1e-6 seconds)
    ) eqOld
    , d.*
    from n d
)
select char(value(strip(dbName) || ',' || strip(name)
           || ',' || partition
           || ',' || char(eqOld)
           || ',' || char(updateStatsTime), ''), 80) txt
    from e
    order by dbName, name, partition
;
select rz, dbSys, loadTs, state,  count(*)
    from OA1P.TQZ006GBGRTSSTATS
    where rz = 'RZ2' and dbSys = 'DBOF' and state <> 'a'
    group by loadTs, state
    order by 3 desc
;x;