zOs/SQL/PLANCLE3

//A540769V JOB (CP00,KE50),'DB2 ADMIN',
//             TIME=1440,REGION=0M,CLASS=M1,SCHENV=DB2ALL,
//             MSGCLASS=T,NOTIFY=&SYSUID
//*
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,                      00020001
//            PARM='WSH'
//SYSPROC    DD DSN=DSN.DB2.EXEC,DISP=SHR
//SYSPRINT   DD SYSOUT=*
//SYSUDUMP   DD SYSOUT=*
//SYSTSPRT   DD SYSOUT=*
//ABNLIGNR   DD DUMMY                 SUPPRESS ABENDAID DUMPS
//OUT        DD SYSOUT=*
//SYSTSIN    DD DUMMY
//WSH        DD *
$#@
$= dbSys=DE0G
$*( *******************************************************************
  planCle3: delete rows from explain tables (dsn_),
            that have no corresponing rows in plan_table
      use: change dbSys above; sub

      plan table / explain table cleanup
          planCle0 sql to select/count explains to delete
          planCle1 alter clustering, reorg plantable
          planCle2 delete rows from plan_table
          planCle3 delete rows from all other explain tables
******************************************************************* $*)
$=cr=CMNBATCH
comAftR = 500        $** commit after so many delete
comAftK = 1e5        $** commit after so many keep
sleepAft = 50
call sqlConnect $dbSys
cTb = 0
$<>
$<.^queryTables
$forWith $@/oneTb/
    cTb = cTb + 1
    say time() 'tableBegin' cTb $CR'.'$TB
    if cTb <= 3 then iterate
    cCom = 0
    cR = 0
    cRRw = 0
    cK = 0
    cKRw = 0
    call sqlQuery 55, "select queryNo, explain_time, count(*) cnt",
          " , case when exists (select 1 from" $cr".plan_Table t" ,
                "where t.queryNo = d.queryNo" ,
                      "and t.explain_time = d.explain_time)",
              " then 'k' else 'r' end kr" ,
        "from" $CR'.'$TB d "group by queryNo, explain_Time" ,
                         "order by queryNo, explain_Time desc"
    say time() 'tableQuery' cTb $CR'.'$TB
    $@comPre
    cRNx = cRRw + comAftR
    cKNx = cKRw + comAftK
    $do while sqlFetch(55, i) $@/oneFetch/
         $** say m.i.kr m.i.queryNo m.i.explain_time m.i.cnt
         if m.i.kr == 'k' then $@[
             cK = cK + 1
             cKRw = cKRw + m.i.cnt
         $] else $@[
             cR = cR + 1
             cRRw = cRRw + m.i.cnt
             call sqlUpdateExecute 7, m.i.queryNo, m.i.explain_time
             if m.i.cnt <> m.sql.7.updateCount then
                 call err 'mismatch cnt='m.i.cnt ,
                              '<> updateCount='m.sql.7.updateCount
             $]
         if cRRw >= cRNx | cKRw >= cKNx then $@[
             cCom = cCom + 1
             cRNx = cRRw + comAftR
             cKNx = cKRw + comAftK
             $@comPre
             say time() cCom 'commits, deleted' cRRw '#t='cR',',
                                         'kept' cKRw '#t='cK
             if cCom // sleepAft = 0 then
                 call sleep 3
             $]
         $/oneFetch/
    cCom = cCom + 1
    $@comPre
    say time() 'tableEnd' cTb $CR'.'$TB ,
              cCom 'commits, deleted' cRRw '#t='cR',',
                               'kept' cKRw '#t='cK
    call sqlClose 55
    $/oneTb/
call sqlDisconnect

$proc $@/comPre/
    call sqlCommit
    call sqlUpdatePrepare 7, 'delete from' $CR'.'$TB ,
        'where queryNo = ? and explain_time = ?'
$/comPre/

$proc $@/queryTables/
    return in2Buf(sqlRdr( ,
     "select creator cr, name tb from sysibm.sysTables" ,
       "where creator = '"$cr"' and type = 'T'" ,
        "and name like 'DSN%TABLE%' and right(strip(name),6)='_TABLE'",
         "and not (name like '%QUERY_TABLE%'" ,
         "or name like '%TEMENT_CACH%' or name like '%VIRTUAL_INDE%')",
       "order by creator, name"))
$/queryTables/
$#out                                              20120208 13:45:49