zOs/WK/PLANCLE2

//A540769W JOB (CP00,KE50),'DB2 REO',
//         MSGCLASS=T,TIME=1440,
//         NOTIFY=&SYSUID,REGION=0M
//*MAIN CLASS=LOG0
//*
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,
//            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 *
$#@
$=cr=CMNBATCH
$=subsys=DBAF
$=doDel = 1
fetFir = '' $** fetch first  5000 rows only
wh = "collid >= '"lCo"' and (collid > '"lCo"' or progName >= '"lPr"')"
$;
$** $>~WK.TEXV(PLANCLEA) $@[
call sqlConnect $subsys
call sqlPreAllCl 1, "select name from sysibm.sysTables" ,
       "where creator = '"$cr"' and type = 'T'" ,
         "and name like 'DSN%' and not (name like '%_OLD%'" ,
           "or name like '%QUERY_TABLE%' or name like '%TEMENT_CACH%'",
           "or name like '%VIRTUAL_INDE%')",
        , "TBL", ":m.tbl.sx.tb"
$@do tx=1 to m.tbl.0 $@/oneTb/
    cols = catTbCols($cr, m.tbl.tx.tb)
    say tx m.tbl.tx.tb time()
    wh = ''
    prgCol = "'???'"
    if wordPos('PROGNAME', cols) > 0 then do
         wh = wh "AND r.progName     = p.progName"
         prgCol = 'PROGNAME'
         end
    if wordPos('COLLID', cols) > 0 then
         wh = wh "AND r.collid       = p.collid"
    wh = wh "and r.explain_time = p.bind_time" ,
            "and r.queryno      = p.queryno"
    qBloCol = "'???'"
    if wordPos('QBLOCKNO', cols) > 0 then do
        wh = wh "AND r.qBlockNo     = p.qBlockNo "
        qBloCol = 'qBlockNo'
        end
    say 'where' wh
    if wordPos('PLANNO', cols) > 0 then
         wh = wh "AND r.PlanNo       = p.PlanNo"
         $**    not join applname, it is sometime x'0000'...
    call sqlPreOpen 51,
         , "select QUERYNO," qBloCol"," prgCol ", explain_time",
                ",(select count(*) from" $cr".PLAN_TABLE p" ,
                    "where" substr(wh, 5) ") pCnt",
                "from" $cr"."m.tbl.tx.tb "r for update" fetfir
    dx = 0
    ex = 0
    dxAft = 1000
    dxNxt = dxAft
    fxAft = 10000
    fxNxt = fxAft
    $@do fx=1 while sqlFetchInto( 51, ':qno, :qBl' $*+
            ', :prg, :exT, :pCn') $@[
        if pCn = 0 then do
            dx = dx + 1
            if $doDel then do
                call sqlExec 'delete from' $cr'.'m.tbl.tx.tb ,
                             'where current of c51'
                if 0 then
                   say  'delete from' $cr'.'m.tbl.tx.tb ,
                             'where current of c51' sqlErrD.3
                ex = ex + sqlErrD.3
                end
            end
        if fx >= fxNxt | dx >= dxNxt then do
            call sqlCommit
            say 'commit del' ex'/'dx 'at' fx 'pCn' pCn 'q' qNo qBl ,
                        'pr' prg exT time()
            dxNxt = dx + dxAft
            fxNxt = fx + fxAft
            call sleep 1, 0
            end
        $]
    say m.tbl.tx.tb 'total del' ex'/'dx 'at' fx 'pCn' pCn 'q' qNo qBl
    call sqlClose 51
    $/oneTb/
$#out                                              20120208 13:45:49
$#out                                              20120208 13:44:37
$#out                                              20120208 13:38:47
$#out                                              20120208 13:33:31
*** run error ***
SQLCODE = -206: QBLOCNO IS NOT VALID IN THE CONTEXT WHERE IT
    IS USED
stmt =  execSql prepare s51  from :src
with from :src = select QUERYNO, qBlocNo, PROGNAME , explain_time ,(select count
$#out                                              20120208 13:24:17
*** run error ***
SQLCODE = -206: QBLOCKNO IS NOT VALID IN THE CONTEXT WHERE
    IT IS USED
stmt =  execSql prepare s51  from :src
with from :src = select QUERYNO, qBlockNo, PROGNAME , explain_time ,(select coun
$#out                                              20120208 13:20:29
*** run error ***
SQLCODE = -206: R.QBLOCKNO IS NOT VALID IN THE CONTEXT WHERE
    IT IS USED
stmt =  execSql prepare s51  from :src
with from :src = select QUERYNO, qBlockNo, PROGNAME , explain_time ,(select coun
$#out                                              20120208 13:19:11