zOs/REXX/TECSVLEQ

$#@
$*(   tecSave:
           extract last change from rz4/dp4g/OA1P.TQZ006GBGRTSSTATS
                 and import it into rz?/?   /OA1P.TQZ005TECSVRTSLASTEQ
      algo pro Partition
          1) find updatestatsTime of newest row
          2) find difNew = updatestatstime of newest row
                with NOT all columns indicating update equal
          3) find eqOld = updatestatstime of oldest row newer difNew
                i.e. all rows between updatestatstime and eqOld
                have all columns indicating update equal
          specials:
              if no difNew ==> use oldest row for 3) eqOld
              if difNew is legacy data (from old datacollection, with
                  only size) and size is within +- 16kb
                  then use min(eqOld, difNew + 5 days) for eqOld
              ==> eqOld never null
$*)
$= rz    = RZ2
$= dbSys = DBOF
$<>
$>. fEdit()
$@jobHead
$@dsntiaul
call sqlConnect dp4g
$@sql
call sqlDisConnect dp4g
$@jobSub
$@load
$$ }{
$$ //         DD DISP=(OLD,DELETE),DSN=*.UNLOAD.SYSREC00
$proc $@=/jobHead/
//A540769W JOB (CP00,KE50),'DB2 ADMIN',
//         TIME=1440,REGION=0M,CLASS=M1,SCHENV=DB2ALL,
//         MSGCLASS=T,NOTIFY=&SYSUID
$/jobHead/

$proc $@=/jobSub/
//***** submit job to $rz *****************************************
//SUB$rz  EXEC PGM=IEBGENER
//SYSPRINT   DD SYSOUT=*
//SYSUT2     DD SUBSYS=(CSM,'SYSTEM=$rz,SYSOUT=(A,INTRDR)')
//SYSUT1     DD DATA,DLM='}{'
$@jobHead
$/jobSub/

$proc $@=/dsntiaul/
//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN  DD *
    DSN SYSTEM(DP4G)
   RUN PROGRAM(DSNTIAUL) PARMS('SQL')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSPUNCH DD  SYSOUT=*,RECFM=FB,LRECL=80
//SYSREC00 DD  DISP=(MOD,PASS),
//             SPACE=(CYL,(2,1000)),MGMTCLAS=COM#A069
//SYSIN      DD *
$/dsntiaul/

$proc $@=/sql/
with k as $**--- keys of newest row per partition
(
  select rz, dbSys, dbName, name, partition, instance
        , max(updatestatstime) updatestatstime
    from OA1P.TQZ006GBGRTSSTATS k0
    where rz = '$rz' and dbSys = '$dbSys'
$@ if $dbSys == 'DBOF' then $@=[
        and dbName in ('XC01A1P', 'XR01A1P')
        and (name like 'A2%' or name like 'A5%')
  $*(   and (dbName = 'XC01A1P' and name like 'A519%' )   $*)
$] $@ else if $dbSys == 'DVBP' then $@=[
        and dbName like 'XB%'
$] $@ else if $dbSys == 'DP4G' then $@=[
        and dbName like 'QZ01A1P%'
$] $@ else call err 'where for dbSys='$dbSys
  group by rz, dbSys, dbName, name, partition, instance
)
, d2 as $**--- timestamp and type of newest different row
(
  select cD.*
    , ( select max(char(dD.updatestatsTime)
            || case when dD.ibmReqD is null or dD.ibmReqD <> ' '
                         or dD.dbid is null or dD.dbid <> 0
                         or dD.pgSIze is null or dD.pgSize <> 1
               then  'n' else '' || nActive end)
        from OA1P.TQZ006GBGRTSSTATS dD
        where kD.rz = dD.rz and kD.dbSys = dD.dbSys
                and kD.dbName = dD.dbName and kD.name = dD.name
                and kD.partition = dD.partition
                and kD.instance = dD.instance
                and not
            (   cD.state            = dD.state
            and cD.totalRows        = dD.totalRows
            and cD.nActive          = dD.nActive
            and cD.nPages           = dD.nPages
            and cD.reorgInserts     = dD.reorgInserts
            and cD.REORGDELETES     = dD.REORGDELETES
            and cD.REORGUPDATES     = dD.REORGUPDATES
            and cD.REORGMASSDELETE  = dD.REORGMASSDELETE
            and cD.dataSize         = dD.dataSize
    )       ) difNewNO
    from k kD
      join OA1P.TQZ006GBGRTSSTATS cD $**--- current row
        on kD.rz = cD.rz and kD.dbSys = cD.dbSys
          and kD.dbName = cD.dbName and kD.name = cD.name
          and kD.partition = cD.partition and kD.instance = cD.instance
          and kD.updatestatstime = cD.updateStatsTime
    where cD.state = 'a'
)
, d as $**--- decode difNewNO int difNew and difNO
(
  select timestamp(left(difNewNO, 26)) difNew
       , case when difNewNO is null then ' '
              when substr(difNewNO, 27) = 'n' then 'n'
              when nActive * pgSize
                  between int(substr(difNewNO, 27)) - 16
                      and int(substr(difNewNO, 27)) + 16
                   then 'e' else 'o' end difNO
       , d2.*
    from d2
)
, e as $**--- timestamp oldest equal row
(
  select
    ( select min(updatestatsTime)
        from OA1P.TQZ006GBGRTSSTATS eE
        where eE.rz = dE.rz and eE.dbSys = dE.dbSys and eE.state = 'a'
                and eE.dbName = dE.dbName and eE.name = dE.name
                and eE.partition = dE.partition
                and eE.instance = dE.instance
                and eE.updateStatsTime
                   > value(dE.difNew, '1111-11-11-11.11.11')
    ) eqOld
    , dE.*
    from d dE
)
select char(value(strip(dbName) || ',' || strip(name)
           || ',' || partition
           || ',' || char(case when difNO <> 'e' then eqOld
                  else min(eqOld, difNew + 3 days) end)
           || ',' || char(updateStatsTime)
                 , '') , 80) txt
    from e
    order by dbName, name, partition
;    $** dsntiaul braucht ;
$/sql/

$proc $@=/load/
//LOAD     EXEC PGM=DSNUTILB,PARM='$dbSys,A540769W.LOAD'
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL  DD DSN=$dbSys.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN    DD *
LOAD LOG NO RESUME NO REPLACE COPYDDN(TCOPYD)
     STATISTICS INDEX(ALL)
     WORKDDN(TSYUTS, TSOUTS)
     INDDN(DDIN1) FORMAT DELIMITED EBCDIC CCSID(37, 37)
    INTO TABLE OA1P.TQZ005TECSVRTSLASTEQ
//DDIN1        DD *
$/load/
$#out                                              20151221 09:29:20
$#out                                              20151215 20:18:22
$#out                                              20151214 15:49:45
$#out                                              20151210 09:59:07
$#out                                              20151210 09:55:34