zOs/SQL/PLANCLE0

//A540769Z JOB (CP00,KE50),'DB2 REO',                                   00010000
//         MSGCLASS=T,TIME=1440,                                        00020000
//         NOTIFY=&SYSUID,REGION=0M,                                    00030000
//         SCHENV=DB2,CLASS=M1                                          00040000
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,                      00020001
//             PARM='%WSH s DP4G'
//SYSPROC   DD DSN=DSN.DB2.EXEC,DISP=SHR
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN   DD DUMMY
//OUT       DD SYSOUT=*,DCB=(LRECL=32755,BLKSIZE=32760,RECFM=VB)
//WSH       DD *
$*( *******************************************************************
  planCle0:  count rows from planTable to delete or to keep
      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
******************************************************************* $*)
set current application compatibility 'V11R1';
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 count(*) "#cpv", sum(eCnt) eCnt, kr, ty
    from g
    group by rollup(kr, ty)
    order by ty desc
    with ur
$*(
select *
    from f
    where collid = 'MF'
    order by collid, progName, version, explain_time desc
    fetch first 1000 rows only
$*)