zOs/SQL/GBGRMIGI

//A540769I JOB (CP00,KE50),'DB2 REO',                                   00010000
//         MSGCLASS=T,TIME=1440,CLASS=M1,                               00020000
//         NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2ALL                       00030003
//S1       EXEC PGM=DSNUTILB,PARM='DP4G,A540769I.LOAD'                  00020001
//SYSMAP   DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSERR   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTEMPL  DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN    DD *
EXEC SQL
DECLARE CURK CURSOR FOR
with g2 as
(
  select rz, dbSys, loadTs, max(updateStatsTime) stats
    , days(max(updateStatsTime)) - days(loadTs) d1
    from oa1p.tqz007gbgrIXHist
    -- where rz = 'RR2' -- and dbSys = 'DP2G'
    --   and loadTs > current timestamp - 1 month
    group by rz, dbSys, loadTs
)
select rz, dbSys, loadTs, stats updateStatsTime
    , value(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, 0) di
    from g2
ENDEXEC
LOAD DATA INCURSOR CURK  LOG NO RESUME NO REPLACE
 SORTDEVT DISK
 WORKDDN(TSYUTS,TSOUTS)
 INTO TABLE  OA1P.TQZ667GBGRIXKEY

EXEC SQL
DECLARE CURT CURSOR FOR
with j2 as
(
  select t.rz, t.dbsys, t.dbName, t.ts, t.indexSpace
    , t.partition, t.instance, t.state
    , t.loadTs, t.updateStatsTime
    , ( select di from oa1p.tqz667gbgrIXKey k
          where t.rz  = k.rz and t.dbSys = k.dbSys
               and t.loadTs = k.loadTs ) di
    from oa1p.tqz007gbgrIXHist t
    where (t.rz, t.dbSys) in (select k.rz, k.dbSys
             from oa1p.tqz667gbgrIXKey k
             group by k.rz, k.dbSys)
)
, j as
(
  select j2.*
    , case when     updateStatsTime - di days <= loadTs
               then updateStatsTime - di days
           else loadTs end statsNew
    from j2
)
, k as
(
  select j.*
    , min(statsNew) over
      ( partition by rz, dbsys, dbName, ts, indexSpace
           , partition, instance
        order by loadTs, statsNew
        rows between 1 preceding and 1 preceding
      ) statsPrec
    from j
)
select  t.STATE
      , t.RZ
      , t.DBSYS
      , t.INDEXTYPE
      , t.COMPRESS
      , t.IXPARTS
      , t.IXPGSZ
      , t.PIECESIZE
      , t.PIECEGB
      , t.LIMGB
      , t.TBCREATOR
      , t.TBNAME
      , t.TS
      , t.TSTY
      , t.TSPARTS
      , t.TSCLONE
      , t.TSINST
      , t.TSDSSIZE
      , t.TSDSGB
      , t.TSLIMGB
      , t.TSLIMPART
      , t.TSPGSZ
      , 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.NLEVELS
      , t.NPAGES
      , t.NLEAF
      , t.NACTIVE
      , t.SPACE
      , t.EXTENTS
      , t.LOADRLASTTIME
      , t.REBUILDLASTTIME
      , t.REORGLASTTIME
      , t.REORGINSERTS
      , t.REORGDELETES
      , t.REORGAPPENDINSERT
      , t.REORGPSEUDODELETES
      , t.REORGMASSDELETE
      , t.REORGLEAFNEAR
      , t.REORGLEAFFAR
      , t.REORGNUMLEVELS
      , t.STATSLASTTIME
      , t.STATSINSERTS
      , t.STATSDELETES
      , t.STATSMASSDELETE
      , t.COPYLASTTIME
      , t.COPYUPDATEDPAGES
      , t.COPYCHANGES
      , t.COPYUPDATELRSN
      , t.COPYUPDATETIME
      , t.LASTUSED
      , t.IBMREQD
      , t.DBID
      , t.ISOBID
      , t.PSID
      , t.PARTITION
      , t.INSTANCE
      , t.TOTALENTRIES
      , t.DBNAME
      , t.NAME
      , t.CREATOR
      , t.INDEXSPACE
      , t.REORGINDEXACCESS
      , t.DRIVETYPE
      , t.STATS101
    from oa1p.tqz007gbgrIXHist 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.ts = k.ts
          and t.indexSpace = k.indexSpace
          and t.partition = k.partition and t.instance = k.instance
          and t.updateStatsTime = k.updateStatsTime
ENDEXEC
LOAD DATA INCURSOR CURT  LOG NO RESUME NO REPLACE
     COPYDDN(TCOPYD) STATISTICS INDEX(ALL)
 SORTDEVT DISK
 WORKDDN(TSYUTS,TSOUTS)
 INTO TABLE  OA1P.TQZ007GBGRIXSTATS
  LISTDEF TPLIST
    INCLUDE TABLESPACE QZ01A1P.A007A PARTLEVEL
---- COPY ----------------------------------------
  COPY LIST TPLIST COPYDDN(TCOPYD)
    FULL YES SCOPE PENDING
    PARALLEL
    SHRLEVEL CHANGE