zOs/SQL/GBGRMIGT

//A540769T 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,A540769T.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.tqz006gbgrTsHist
    -- where rz = 'RR2' and dbSys = 'DBOF'
    --   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.TQZ666GBGRTSKEY
EXEC SQL
DECLARE CURT CURSOR FOR
with j2 as
(
  select t.rz, t.dbsys, t.dbName, t.name, t.partition, t.instance
    , t.loadTs, t.updateStatsTime, t.state
    , ( select di from oa1p.tqz666gbgrTsKey k
          where t.rz  = k.rz and t.dbSys = k.dbSys
               and t.loadTs = k.loadTs ) di
    from oa1p.tqz006gbgrTsHist t
    where (t.rz, t.dbSys) in (select k.rz, k.dbSys
             from oa1p.tqz666gbgrTsKey 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, 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
      , t.UPDATESIZE
      , t.LASTDATACHANGE
    from oa1p.tqz006gbgrTsHist 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
ENDEXEC
LOAD DATA INCURSOR CURT  LOG NO RESUME NO REPLACE
     COPYDDN(TCOPYD) STATISTICS INDEX(ALL)
 SORTDEVT DISK
 WORKDDN(TSYUTS,TSOUTS)
 INTO TABLE  OA1P.TQZ006GBGRTSSTATS

LISTDEF TPLIST
    INCLUDE TABLESPACE QZ01A1P.A006A PARTLEVEL
---- COPY ----------------------------------------
COPY LIST TPLIST COPYDDN(TCOPYD)
    FULL YES SCOPE PENDING
    PARALLEL
    SHRLEVEL CHANGE