zOs/SQL/CATRUPD

$#@ $*( catRUpd
                   show or update/delete outdate runstats Statistics
                   wsh script
                       1) update parameters
                       2) modify insert into session.st
                       3) wsh
                   version 0.1, 20.6.12 Walter
               $*)


call sqlConnect dp4g                                           $** <<<<<
$=fun=a        $*(  a = show all                               $** <<<<<
                    o = show old only
                    d = delete old
               $*)
$=det= 1      $** 0 summary only, 1 all rows (only fun=d)
$=lim=5 hour   $** limit for stats older than newest

$=distKeys = type, colGroupColno, quantileno, colValue
$=updCnt=200

$<> $<=[
declare global temporary table session.st
    as (select creator cr, name tb, dbName db, tsName ts,
               statstime lim
           from sysibm.sysTables
       ) with no data on commit preserve rows
;
insert into session.st
    select creator, name, dbName, tsName, '0001-01-01-00.00.00'
        from sysibm.sysTables
        where type = 'T'
            and creator = 'OA1P'                             $** <<<<<<
            and name =    'TQZ006GBGRTSSTATS'
  --        and name in  ('VTINSTRFLATSYMBOLS'
  --                     ,'VTCASHFLOW'
  --                     ,'VTINSTRUMENTEVENT'
  --                     )
        ;
$** no more changes in rest of script *********************************

update session.st st
   set lim  = ( select case when max(statstime) > '1900-01-01-00.00.00'
                       then max(statstime) - $lim
                       else timestamp('0001-01-01-00.00.00') end
                  from sysibm.sysColumns co
                  where co.tbCreator = st.cr and co.tbName = st.tb)
;
declare global temporary table session.sx
    as (select creator cr, name ix, tbCreator tbCr, tbName tb,
           statstime lim
           from sysibm.sysIndexes
       ) with no data on commit preserve rows
;
insert into session.sx
    select creator, name, tbCreator, tbName, lim
        from sysibm.sysIndexes ix join session.st st
        on ix.tbCreator = st.cr and ix.tbName = st.tb
;
-- select * from session.st;
-- select * from session.sx;
$] call sqlStmts
$;
if $fun == 'a' then $@[ $<=[
with t (tb, ct, tst) as
( select tb, 'table', statstime
     from sysibm.sysTables tb join session.st st
       on tb.creator = st.cr and tb.name = st.tb
  union all select tb, 'columns',
         case when statstime > '1900-01-01-00.00.00'
              then statstime else null end
     from sysibm.sysColumns co join session.st st
       on co.tbCreator = st.cr and co.tbName = st.tb
  union all select tb, 'colDist', statstime
    from sysibm.sysColDist cd join session.st st
       on cd.tbOwner = st.cr and cd.tbName = st.tb
  union all select tb, 'colDistStats', statstime
    from sysibm.sysColDistStats cds join session.st st
       on cds.tbOwner = st.cr and cds.tbName = st.tb
  union all select tb, 'colStats', statstime
    from sysibm.sysColStats cs join session.st st
       on cs.tbOwner = st.cr and cs.tbName = st.tb
  union all select tb, 'tabStats', statstime
    from sysibm.sysTabStats tbs join session.st st
       on tbs.owner = st.cr and tbs.name = st.tb
  union all select tb, 'tbSpace', statstime
    from sysibm.sysTableSpace ts join session.st st
       on ts.dbName = st.db and ts.name = st.ts
  union all select tb, 'tbPart', statstime
    from sysibm.sysTablePart ts join session.st st
       on ts.dbName = st.db and ts.tsName = st.ts
  union all select tb, 'indexes', statstime
    from sysibm.sysIndexes ix join session.st st
       on ix.tbCreator = st.cr and ix.tbName = st.tb
  union all select tb, 'ixPart', statstime
    from sysibm.sysIndexPart ip join session.sx sx
       on ip.ixCreator = sx.cr and ip.ixName = sx.ix
  union all select tb, 'ixStats', statstime
    from sysibm.sysIndexStats is join session.sx sx
       on is.owner = sx.cr and is.name = sx.ix
  union all select tb, 'keyTarget', statstime
    from sysibm.sysKeyTargets kt join session.sx sx
       on kt.ixSchema = sx.cr and kt.ixname = sx.ix
  union all select tb, 'keyTgtStats', statstime
    from sysibm.sysKeyTargetStats ks join session.sx sx
       on ks.ixSchema = sx.cr and ks.ixname = sx.ix
  union all select tb, 'keyTgtDist', statstime
    from sysibm.sysKeyTgtDist kt join session.sx sx
       on kt.ixSchema = sx.cr and kt.ixname = sx.ix
  union all select tb, 'keyTgtDiSta', statstime
    from sysibm.sysKeyTgtDistStats kts join session.sx sx
       on kts.ixSchema = sx.cr and kts.ixname = sx.ix
 )
select tb, ct, count(*) cnt,
    left(char(min(tst)), 19) "statsTimeMin",
    left(char(max(tst)), 19) "statsTimeMax"
    from t
    group by tb, ct
;
$] call sqlStmts
$]
$;
if $fun == 'o' then $@/old/
$<=[
with t (tb, ct, nm, tst) as
( select tb, 'columns', name, statstime
              from sysibm.sysColumns co join session.st st
                on co.tbCreator = st.cr and co.tbName = st.tb
                and statsTime < lim
                and statsTime > '0001-01-01-00.00.00.00000'
  union all select tb, 'colDist',
      case when length(colGroupColNo) <= 2 then name
      else hex(COLGROUPCOLNO) end ,
      statsTime
              from sysibm.sysColDist cd join session.st st
                on cd.tbOwner = st.cr and cd.tbName = st.tb
                  and cd.statstime < lim
  union all select tb, 'colDistStats',
      case when length(colGroupColNo) <= 2 then name
      else hex(COLGROUPCOLNO) end
      || right('     ' || strip(char(partition)), 5), statsTime
              from sysibm.sysColDistStats cds join session.st st
                on cds.tbOwner = st.cr and cds.tbName = st.tb
                  and statstime < lim
  union all select tb, 'colStats',
      name || ' '
      || right('     ' || strip(char(partition)), 5), statsTime
              from sysibm.sysColStats cs join session.st st
                on cs.tbOwner = st.cr and cs.tbName = st.tb
                  and statstime < lim
 )
$@[
if $det then $@=[
select tb, ct, left(char(tst), 19) "statsTime", nm
    from t
    order by tb, ct, nm
$] else $@=[
select tb, ct, count(*) cnt,
    left(char(min(tst)), 19) "statsTimeMin",
    left(char(max(tst)), 19) "statsTimeMax"
    from t
    group by tb, ct
    order by tb, ct
$]
$]
;
$] call sqlStmts
$/old/
$;
if $fun == 'd' then $@/delete/
$<=[
update sysibm.sysColumns co set
       statstime = '0001-01-01-00.00.00.00000'
     , colCardf = -1
    where (tbCreator, tbName, name) in
          ( select cr, tb, name
              from sysibm.sysColumns co join session.st st
                on co.tbCreator = st.cr and co.tbName = st.tb
                and statsTime < lim
                and statsTime > '0001-01-01-00.00.00.00000'
            fetch first $updCnt rows only
          )
$] $$- sqlUpdComLoop(scanSqlIn2Stmt( , 0))
$;
$<=[
delete
    from sysibm.sysColDist d
    where (tbOwner, tbName, name, $distKeys)
    in    ( select cr, tb, name, $distKeys
              from sysibm.sysColDist cd join session.st st
                on cd.tbOwner = st.cr and cd.tbName = st.tb
                  and cd.statstime < lim
              fetch first $updCnt rows only
          )
$] $$- sqlUpdComLoop(scanSqlIn2Stmt( , 0))
$;
$<=[
delete
    from sysibm.sysColDistStats
    where (tbOwner, tbName, name, partition, $distKeys)
    in    ( select cr, tb, name, partition, $distKeys
              from sysibm.sysColDistStats cds join session.st st
                on cds.tbOwner = st.cr and cds.tbName = st.tb
                  and statstime < lim
              fetch first $updCnt rows only
          )
$] $$- sqlUpdComLoop(scanSqlIn2Stmt( , 0))
$;
$<=[
delete
    from sysibm.sysColStats
    where (tbOwner, tbName, name, partition)
    in    ( select cr, tb, name, partition
              from sysibm.sysColStats cs join session.st st
                on cs.tbOwner = st.cr and cs.tbName = st.tb
                  and statstime < lim
              fetch first $updCnt rows only
          )
$] $$- sqlUpdComLoop(scanSqlIn2Stmt( , 0))
$/delete/
$#out                                              20150907 15:35:52