zOs/SQL/PLANCLE2

//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=*
//OUT       DD SYSOUT=*
//SYSTSIN   DD DUMMY
//WSH       DD *
$#@
$= dbSys=DP4G
$*( *******************************************************************
  planCle2:  delete rows from planTable, that are no longer needed
      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 plantable
          planCle3 delete rows from all other explain tables
******************************************************************* $*)
$=tb=CMNBATCH.Plan_Table
comAft = 1000
comNxt = 0
cCom = 0
cDel = 0
cRow = 0
sleepAft = 20
call sqlConnect $dbSys
$= qx = 55
$= dx = 6
dx = $dx
$@queryExplain
$@comPre
$do fx=1 while sqlFetch($qx, i) $@[
    say fx m.i.ty m.i.collid m.i.prog m.i.version m.i.expTi m.i.eCnt
    if  m.i.kr == 'r' then $@[
         call sqlUpdateExecute dx, m.i.collid, m.i.prog, m.i.version,
                                 , m.i.expTi
         say 'deleted' m.sql.dx.updatecount
         cDel = cDel + 1
         comNxt = comNxt + m.sql.dx.updateCount
         if comNxt >= comAft then $@[
              call sqlUpdate , rollback; say 'rollback abend for test'
              $@comPre
              cCom = cCom + 1
              cRow = cRow + comNxt
              comNxt = 0
              say time() cCom 'commits,' cDel 'dels,' ,
                 cRow 'rows deleted' m.i.collid m.i.prog m.i.version
              if cCom // sleepAft = 0 then
                  call sleep 10
              $]
         $]
    $]
              say time() cCom+1 'commits,' cDel 'dels,' ,
                  cRow+comNxt 'rows deleted'
$@comPre
$@ call sqlClose $qx

$proc $@/comPre/
    call sqlCommit
    call sqlUpdatePrepare $dx, 'delete from' $tb ,
        'where collid = ? and progName = ? and version = ?' ,
         'and explain_time = ?'
    say 'commit prepare'
$/comPre/

$proc $@/queryExplain/
    call sqlQuery $qx, scanSqlIn2Stmt()
$<=[
$*( *******************************************************************
      plan table / explain talbe cleanug
          planCle0 sql to select/count explains to delete
          planCle1 alter clustering, reorg plantable
          planCle2 delete rows from plantable
          planCle3 delete rows from all other explain tables
******************************************************************* $*)
with p2 as
(
  select p.*
       , case when timestamp > current timestamp - 15 days
              then 1 else 0 end timNew
       , case when lastUsed > current date - 3 years
              then 1 else 0 end useLaY
    from sysibm.sysPackage p
 )
, p3 as
(
  select collid, name, version, timestamp, lastUsed, timNew, useLaY
       , sum(useLaY) over(partition by collid, name, timNew) useLaC
       , dense_rank() over(partition by collid, name, timNew
                           order by lastUsed desc) useRn
       , dense_rank() over(partition by collid, name, timNew
                           order by date(timestamp) desc) timRn
       , dense_rank() over(partition by collid, name, timNew
                           order by date(pcTimestamp) desc) pctRn
       , max(lastUsed) over (partition by collid, Name, timNew
                              order by lastUsed asc
                              rows between 1 following
                                       and 1 following) useNext
    from p2
)
, p as
(
  select p3.*
     , case when lastUsed > '01.01.1900' and useNext is not null
            then lastUsed else '31.12.2999' end until
    from p3
    where timNew = 1 or useLaY = 1
        or useLaC + useRn <= 2 and lastUsed > '01.01.1900'
        or useLaC + timRn <= 2
        or useLaC + pctRn <= 2
)
, e2 as
(
  select collid, progName, version, explain_Time
    , count(*) eCnt
    , sum(case when optHint <> '' then 1
               when hint_Used = '' or hint_Used = 'APREUSE' then 0
               else 1 end) eHint
    , case when explain_time > current timestamp - 15 days
           then 1 else 0 end eNew
    from cmnbatch.plan_table
    group by collid, progName, version, explain_Time
)
, e as
(
  select e2.*
    , row_number() over (partition by collid, progName, version, eNew
                         order by explain_time desc) eRng
    , max(explain_time)
          over (partition by collid, progName, version, eNew
                order by explain_time asc
                rows between 1 following and 1 following) expNext
    , 10 win
    from e2
)
, f as
(
  select e.collid, e.progName, e.version
    , case when eNew = 1 then 'knew'
           when eHint > 0 then 'khint'
           when p.Name is null then 'rnoPk'
           when explain_time < timestamp then 'rexp<pkg'
           when date(e.explain_time) >= until then 'rafter'
           when value(date(expNext), until) >= date(explain_time)
                   + win days then 'kuse>' || win
           when eRng <= 2 then 'keRng<=2'
           when days(value(date(expNext), until)) / 90
                <> days(explain_time) / 90 then 'kShort90'
           else 'rtooShort' end ty
    , p.timestamp, p.lastUsed, p.timRn, p.useNext, p.until
    , e.explain_Time, eCnt
    from e
      left join p
        on p.collid = e.collid and p.name = e.progName
            and p.version = e.version
)
, g as
(
  select f.*, left(ty, 1) kr
    from f
)
select collid, progName prog, version, explain_time expTi, kr, ty, eCnt
    from g
    order by progName, collid, version desc, explain_time desc
$]
$/queryExplain/
$#out