zOs/SQL/CATRUN

$#@
$=dbSys = S25/DVBP
call sqlWshIni
call sqlConnect $dbSys, 'w'
$<>
$>. fEdit()
$<=/sql/
declare global temporary table session.s
    as (select creator cr, name tb, dbName db, tsName ts,
               statstime stats, cardf
           from sysibm.sysTables
       ) with no data on commit preserve rows
;
$<>
$<#[
XBFCT001PS%
XBFCT002PS%
XBFCT003PS%
XBFCT004PS%
XBFCT005PS%
XBFCT006PS%
XBFCT007PS%
XBFCT008PS%
XBFCU001PS%
XBFCU002PS%
XBFCU003PS%
XBFCU004PS%
XBFCU005PS%
XBFCU006PS%
XBFCU007PS%
XBFCU008PS%
XBFEQ001PS%
XBFEQ002PS%
XBFEQ003PS%
XBFEQ004PS%
XBFEQ005PS%
XBFEQ006PS%
XBFQY001PS%
XBFQY002PS%
XBFQY003PS%
XBFQY004PS%
XBFQY005PS%
XBFQY006PS%
XBFQY007PS%
XBFCT001IT%
XBFCT002IT%
XBFCT003IT%
XBFCT004IT%
XBFCT005IT%
XBFCT006IT%
XBFCT008IT%
XBFCU001IT%
XBFCU002IT%
XBFCU003IT%
XBFCU004IT%
XBFCU005IT%
XBFCU006IT%
XBFCU007IT%
XBFCU008IT%
XBFEQ001IT%
XBFEQ002IT%
XBFEQ003IT%
XBFEQ004IT%
XBFEQ005IT%
XBFEQ006IT%
XBFQY001IT%
XBFQY002IT%
XBFQY003IT%
XBFQY004IT%
XBFQY005IT%
XBFQY006IT%
XBFQY007IT%
$]
$for i $@=[
$= j =- strip($i)
insert into session.s
    select creator, name, dbName, tsName, statsTime, cardF
        from sysibm.sysTables
        where type = 'T'
            and creator = 'BUA'
            and name like '$j'
   $*(      and creator = 'OA1P'
            and name like 'TQZ006GBGR%'
   $*)  ;
$]
$<>
with u(cr, tb, db, ts, stats, cardF, delta) as
(
  select cr, tb, db, ts, stats, cardF, 0
    from session.s s
  union all select s.cr, s.tb, s.db, s.ts,
              tp.statsTime, tp.cardF, 0
    from session.s s  join sysibm.sysTablePart tp
         on s.db = tp.dbName and s.ts = tp.tsname
  union all select s.cr, s.tb, s.db, s.ts,
              r.statsLastTime, r.totalRows
       , case when statsMassDelete <> 0
                  then - abs(statsMassDelete)
                  else abs(statsInserts) + abs(statsUpdates)
                                        + abs(statsDeletes) end
    from session.s s
      join sysibm.sysTableSpace ts
        on s.db = ts.dbName and s.ts = ts.name
      join sysibm.sysTableSpaceStats r
        on ts.dbid = r.dbid and ts.psid = r.psid
          and s.db = r.dbName and s.ts = r.name
  union all select s.cr, s.tb, s.db, s.ts,
              i.statsTime, i.fullKeyCardF, 0
    from session.s s
      join sysibm.sysIndexes i
        on s.cr = i.tbCreator and s.tb = i.tbName
  union all select s.cr, s.tb, s.db, s.ts,
              r.statsLastTime, r.totalEntries
       , case when statsMassDelete <> 0
                  then - abs(statsMassDelete)
                  else abs(statsInserts) + abs(statsDeletes) end
    from session.s s
      join sysibm.sysIndexes i
        on s.cr = i.tbCreator and s.tb = i.tbName
      join sysibm.sysIndexSpaceStats r
        on i.creator = r.creator and i.name = r.name
)
, g as
(
  select cr, tb, db, ts
      , min(stats) statsMin, max(stats) statsMax
      , case when min(cardF) < 0 then ' noStatsC' else '' end
      ||case when min(stats) < '1900-01-01-00.00.00'
             then ' noStatsT' else '' end
      ||case when min(delta) < 0 then ' massDel' else '' end
      reason
      , max(case when delta <= 0 or cardF < 0 then 0
                 when cardF = 0 then 999999999
                 else int(round(min( 999999999, real(max(delta, cardf))
                             / min(delta, cardF) * 100))) end ) delta
      , days(max(stats))
          - min(case when stats < '1900-01-01-00.00.00' then 999999999
                     else days(stats) end)  statsD
    from u
    group by cr , tb, db, ts
)
, v as
(
  select cr, tb, db, ts
      , substr(reason
      || case when delta < 30 then ''
              else ' delta' || right('000' || min(delta, 9999), 4)
                   || '%' end
      || case when statsD < 30  then ''
              else ' statsInt' || right('000' || min(statsD, 9999), 4)
                   || 'd' end
      , 2) reason
      , statsMin, statsMax, delta, statsD
    fr?m g
)
select * from v
    where reason <> '' or reason is null
--  fetch first 1 rows only
$/sql/
call sqlStmts , 'w', , , 'os'
$| $=jn = 0
   $forWith i $@/fetch/
   if oKindOfString($i) then
      $$ string $i
   else $@=/job/
       $= jn  =- right($jn + 1, 4, 0)
       $= job =- XBRUNS || right($jn // 3, 2, 0)
//$job JOB (CP00,KE50),'DB2 run',
//             TIME=1440,REGION=0M,SCHENV=DB2ALL,CLASS=M1,
//             MSGCLASS=T,NOTIFY=&SYSUID
//*
//* xb runstats job$jn
//* table  $CR.$TB in $DB.$TS
//* reason $REASON
//*
//S$jn    EXEC PGM=DSNUTILB,TIME=1440,
//             PARM=($dbSys,'$job.RUNSTAT'),
//             REGION=0M
//SYSPRINT   DD SYSOUT=*
//SYSUDUMP   DD SYSOUT=*
//SYSTEMPL   DD DISP=SHR,DSN=$dbSys.DBAA.LISTDEF(TEMPL)
//UTPRINT    DD SYSOUT=*
//RNPRIN01   DD SYSOUT=*
//STPRIN01   DD SYSOUT=*
//INDUMMY    DD DUMMY
//SYSIN      DD *
-- OPTIONS PREVIEW
  LISTDEF TSLIST
    INCLUDE TABLESPACE $DB.$TS
---- runstats ----------------------------------------------------------
  RUNSTATS TABLESPACE LIST TSLIST
    SHRLEVEL CHANGE
    INDEX(ALL)
$/job/
$/fetch/
$#out                                              20160822 16:21:31
$#out                                              20160822 16:21:10
$#out                                              20160822 15:57:45
$#out                                              20160822 15:55:37