zOs/WK/PLANCLEA

//A540769W JOB (CP00,KE50),'DB2 REO',                                   00010000
//         MSGCLASS=T,TIME=1440,                                        00020000
//         NOTIFY=&SYSUID,REGION=0M                                     00030000
//*MAIN CLASS=LOG0                                                      00040000
//*
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,                      00020001
//            PARM='WSH'
//SYSPROC   DD DSN=A540769.WK.REXX,DISP=SHR
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//OUT       DD SYSOUT=*
//SYSTSIN   DD DUMMY
//WSH       DD *
$#@
$=tb=CMNBATCH.Plan_Table
$=subsys=DBBA
$=doDel = 1
paraMax = 3
pLim = 1e99
comAft = 1000
comNxt = comAft
comCnt = 0
sleepAft = 20
sleepNxt = time('e')
$;
$>~WK.TEXV(PLANCLEA) $@/toOut/
$$- '=== plan_table cleanup' sysvar(sysnode)'.'$subsys time() date('o')
say '=== plan_table cleanup' sysvar(sysnode)'.'$subsys time() date('o')
call sqlConnect $subsys
call sqlPreOpen 59, "select strip(collid), strip(progName), count(*)",
                         "from" $tb "group by collid, progName",
                         "order by collid, progName"
pCnt = 0
dCnt = 0
d3 = 0
$@do while sqlFetchInto(59, ":lCo, :lPr, :lCn") &pCnt<pLim  $@/colPrg/
$** say '===' lCo'.'lPr lCn time()
    $$- '===' lCo'.'lPr lCn time()
wh = "collid = '"lCo"' and progName = '"lPr"'"
call sqlPreOpen 51,
       , "with p2 as (",
         'select COLLID col, PROGNAME prg,VERSION vers,BIND_TIME biti',
               ", case when optHint='' and hint_used=''" ,
                     "then 0 else 1 end hints" ,
            'from' $tb 'where' wh 'order by 1, 2, 3, 4' ,
         '), p as ( select count(*) cnt, col, prg, vers, biti' ,
                      ', sum(hints) hints' ,
            'from p2 group by col, prg, vers, biTi)' ,
         'select p.*, days(biTi) biDa' ,
                 ', value(days(pcTimestamp), 0) pcDa' ,
            'from p left join sysibm.sysPackage k',
              "on k.location='' and k.collid = p.col" ,
                "and k.name = p.prg and k.version = p.vers" ,
            'order by p.COL asc, prg asc,' ,
                 'pcDa desc, biTi desc' ,
            'with ur'
$** '===     opened' sysvar(sysnode)'.'$subsys time() date('o')
$** '===     opened' sysvar(sysnode)'.'$subsys time() date('o')
lvDy = 9e9
lDy  = 9e9
para = copies('9e9 ', paraMax)
do while sqlFetchInto( 51, ':cnt, :col, :prg, :vrs, :biTi, :hints' ,
           ', :biDy, :pcDy')
     pCnt = pCnt + cnt
     if strip(col) \== lCo | strip(prg) \== lPr then
         call err col'.'prg '<>' lCo'.'lPr
     if strip(vrs) \== lVr then do
         lVr = strip(vrs)
         if lvDy > lDy then
             lvDy = lDy
         para = lDy subword(para, 1, paraMax-1)
         lDy  = 9e9
         end
     keep = hints > 0          /* keep everything with hints   */ ,
       | (  biDy < lvDy + 100  /* only 3 months in parallel */ ,
          & biDy < lDy - 5     /* at least 5 days before rebound */ ,
          & biTi >> '2005'     /* at most 5 years */      ,
          & biDy + 5 < word(para , paraMax))
     $** keep 'cnt' cnt hints col'.'prg':'vrs 'bi' biTi biDy $*+
     $**      'pc' pcDy cnPc  'para' para
     if \ keep then do
         dCnt = dCnt + cnt
         delSt = "delete from" $tb ,
                    "where collid = '"col"' and progname = '"prg"'" ,
                      "and version = '"vrs"' and bind_time = '"biTi"'"
         if 0 then
             $$- "   " delSt
         if $doDel then do
             call sqlExec delSt
             d3 = d3 + sqlErrd.3
             end
         if dCnt >= comNxt then do
             call sqlcommit
             comNxt = dCnt + comAft
             comCnt = comCnt + 1
             if time('e') > sleepNxt then do
               say '=== commit' comCnt 'after' d3'/'dCnt 'deletes of' ,
                      pCnt time() lCo'.'lPr
               $$- '==commit' comCnt 'after' d3'/'dCnt 'deletes of' $*+
                      pCnt time() lCo'.'lPr
               call sleep 2, 0
               sleepNxt = time('e') + sleepAft
               end
             end
         end
     lDy = biDy
     lPc = pcDy
     end
call sqlClose 51
$/colPrg/
call sqlClose 59
call sqlCommit
say '===== end' comCnt 'after' d3'/'dCnt 'deletes of' pCnt time()
$$- '===== end' comCnt 'after' d3'/'dCnt 'deletes of' pCnt time()
$/toOut/
$#out                                              20120207 12:29:42