zOs/WK/PLANCLEU

$#@
$=cr=CMNBATCH
$=subsys=DAEG
$=cutOff = 2012-02-13-07.00.00
$= fun  = u     $*(   u = unload from cr to svCr existing in plan_table
                      d = reorg discard cr
                      l = load from svCr to cr with resume
                $*)
$= svCr = DB2ADMIN
$;
$>.fEdit() $@/allJobs/
$= resuYes = RESUME YES
$= resuRep = RESUME NO REPLACE COPYDDN(TCOPYS)
if $fun == 'u' then $@[
    $= fCr  = $cr
    $= tCr  = $svCr
    $= jo7  = YDB2PLU
    $= funL = 1
    $= resu1 = $resuRep
    $= log   = LOG NO
    $= shr   = SHRLEVEL NONE
$] else if $fun == 'l' then $@[
    $= fCr  = $svCr
    $= tCr  = $cr
    $= jo7  = YDB2PLL
    $= funL = 1
    $= resu1 = $resuYes
    $= log   = LOG YES
    $= shr   = SHRLEVEL NONE
$] else if $fun == 'd' then $@[
    $= fCr  = $cr
    $= tCr  = $cr
    $= funL = 0
    $= jo7  = YDB2PLD
    $= resu1 = ???resu1
$]  else $@[
    call err 'bad fun' $fun
$]
call sqlConnect $subsys
call sqlPreAllCl 1,
    , "select t.name,value(d.dbName, ''),value(d.tsName, '')" ,
      "from sysibm.sysTables t left join sysIbm.sysTables d" ,
        "on d.creator='"$tCr"' and d.name = t.name" ,
       "where t.creator = '"$fCr"' and t.type = 'T'" ,
         "and t.name like 'DSN%' and not (t.name like '%_OLD%'" ,
           "or t.name like '%QUERY_TABLE%' or t.name like '%MENT_CAC%'",
           "or t.name like '%VIRTUAL_INDE%')",
       "order by d.tsName" ,
     , "TBL", ":m.tbl.sx.tb, :m.tbl.sx.db, :m.tbl.sx.ts"
$= jc = none
$= jx = 0
$@do tx=1 to m.tbl.0 $@/oneTb/
    $= isNewJob =- $jc \== right(strip(m.tbl.tx.ts), 1)
    if $isNewJob then $@/newJob/
        if $jc <> 'none' then
            $$ $copy
        $= jc =- right(strip(m.tbl.tx.ts), 1)
        $= job = $jo7$jc
        $= jx =- $jx + 1
        $= resume  = $resu1
        $@=[
//$job JOB (CP00,KE50),'DB2 PLAN CLEANUP',
//         MSGCLASS=T,TIME=1440,
//         NOTIFY=&SYSUID,REGION=0M
//*MAIN CLASS=LOG0
//*
//STEP1   EXEC PGM=DSNUTILB,TIME=1440,
//           PARM=($subsys,'$job.UNLOAD'),
//   REGION=0M
//DSSPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$subsys.DBAA.LISTDEF(TEMPL)
//SYSIN DD *
  LISTDEF LST INCLUDE TABLESPACE $-{m.tbl.tx.db'.'m.tbl.tx.ts}
                                 PARTLEVEL ALL
$=copy=COPY LIST LST COPYDDN(TCOPYS) PARALLEL SHRLEVEL CHANGE
$copy
        $]
    $/newJob/
    $= tb =- m.tbl.tx.tb
    say tx $tb 'dest in' m.tbl.tx.db'.'m.tbl.tx.ts time()
if $funL then $@/funLoad/
    cols = catTbCols($fCr, m.tbl.tx.tb)
    $@=[
EXEC SQL
  DECLARE CUR$tx CURSOR FOR
    SELECT * FROM $fCr.$tb r
    $]
    if $fun == 'u' then $@[
        $@=[
        WHERE EXPLAIN_TIME <= '$cutOff'
          and exists ( select 1 from $fCr.plan_table p
            where 1=1
        $]
        wh = ''
        $=a =- right('and', 17)
        if wordPos('PROGNAME', cols) > 0 then
             $$ $a r.progName         = p.progName
        if wordPos('COLLID', cols) > 0 then
             $$ $a r.collid           = p.collid
        $$ $a r.explain_time = p.bind_time
        $$ $a r.queryno          = p.queryno
        if wordPos('QBLOCKNO', cols) > 0 then
            $$ $a r.qBlockNo         = p.qBlockNo
        if wordPos('PLANNO', cols) > 0 then
             $$ $a r.PlanNo           = p.PlanNo
        $$ $'          )'
        $]
$@=[
         with cs
  ENDEXEC
LOAD DATA $log $resume
      $shr
      SORTDEVT DISK
      WORKDDN(TSYUTS,TSOUTS)
    INCURSOR CUR$tx
    INTO TABLE  $tCr.$tb
$]
    $=resume = $resuYes
$/funLoad/ else $@/funDiscard/
    if $isNewJob then $@=[
REORG TABLESPACE $-{m.tbl.tx.db'.'m.tbl.tx.ts}
         LOG NO
         SORTDATA
         COPYDDN(TCOPYS)
         SHRLEVEL CHANGE
         MAPPINGTABLE S100447.MAPTAB0$jx
         DRAIN_WAIT 40
           RETRY 20
           RETRY_DELAY 180
           MAXRO 40
           DRAIN WRITERS
           LONGLOG CONTINUE
           DELAY 1200
           TIMEOUT TERM
         UNLOAD CONTINUE
         WORKDDN(TSYUTD,TSOUTD)
         SORTKEYS
         SORTDEVT DISK
         STATISTICS
           INDEX ALL KEYCARD
         REPORT NO
           UPDATE ALL
         UNLDDN    TSRECD
  DISCARD
    $]
    $@=[
    FROM TABLE $fCr.$tb
      WHEN (EXPLAIN_TIME <= '$cutOff')
    $]
$/funDiscard/
    $/oneTb/
if $jc <> 'none' then
    $$ $copy
$/allJobs/
$@proc jobEnd $@=[
     $@=[
  LISTDEF LST INCLUDE TABLESPACE $db.$ts PARTLEVEL ALL
COPY LIST LST COPYDDN(TCOPYS) PARALLEL SHRLEVEL CHANGE
     $]
$]
$#out                                              20120210 17:36:02