zOs/SQL/PKGCLEAN
//A540769P JOB (CP00,KE50),'DB2 REO',
// MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID,REGION=0M,
// SCHENV=DB2ALL,CLASS=M1
//*
//S1 EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,
// PARM='%WSH'
//SYSPROC DD DSN=DSN.DB2.EXEC,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD DUMMY
//WSH DD *
$#:
dbSys = DE0G
sqlFree = -117 -203 -204 -206 -408
sqlExpl = -219
$*( *******************************************************************
job to deactivate outdated packages
* this job can be killed and restarted anytime ****************
usage: check/correct dbSys
sub
verify output, if necessary adapt script and resubmit job
function
1) select outdated packageVersions by sql below
that are NOT deactivated yet
2) deactived by rebind enable (cics) cics(loeschen)
if rebind fails becuause of missing explain table
retry with explain(no)
if package is inValid and rebind is not possible
(has (only) sqlCodes from sqlFree above)
then free package
3) sleep 1 second after 10 rebinds
to avoid monopolisation of the catalog
********************************************************************$*)
rCnt = 0
@selPk
| forWith @rebindPk
proc $@/selPk/
call sqlConnect $dbSys
call sqlStmts , , , , 'o'
$| $@. jTalkRdr()
$<=[
set current application compatibility 'V11R1';
with p0 as
( --- ignore packages created in last two weeks ----------------------
select case when timestamp < current timestamp - 15 days
then 0 else 1 end creNew
, 20 win, p.*
from sysibm.syspackage p
)
, p1 as
( --- end of first window before creNew=1
select max(case when creNew = 0 then timestamp end)
over(partition by location, collid, name) cre1
, max(case when creNew = 0 then pcTimestamp end)
over(partition by location, collid, name) pcT1
, max(case when creNew = 0 then lastUsed end)
over(partition by location, collid, name) use1
, p0.*
from p0
)
, p2 as
( --- end of second window before begin of first window ---------------
select max(case when cre1 < '1000-01-01-00.00.00' then null
when creNew = 0 and timestamp < cre1 - win days
then timestamp end)
over(partition by location, collid, name) cre2
, max(case when pcT1 < '1000-01-01-00.00.00' then null
when creNew = 0 and pcTimestamp < pcT1 - win days
then pcTimestamp end)
over(partition by location, collid, name) pcT2
, max(case when use1 < '01.01.1000' then null
when creNew = 0 and lastUsed < use1 - win days
then lastUsed end)
over(partition by location, collid, name) use2
, p1.*
from p1
)
, pS as
( --- seqNumber of each window ----------------------------------------
select
case when creNew = 1 then 0
when cre1 is null or cre1 < '1000-01-01-00.00.00' then 1
when timestamp >= cre1 - win days then 1
when cre2 is null or cre2 < '1000-01-01-00.00.00' then 2
when timestamp >= cre2 - win days then 2
else 9 end creSeq
, case when creNew = 1 then 0
when pcT1 is null or pcT1 < '1000-01-01-00.00.00' then 1
when pcTimestamp >= pcT1 - win days then 1
when pcT2 is null or pcT2 < '1000-01-01-00.00.00' then 2
when pcTimestamp >= pcT2 - win days then 2
else 9 end pcTSeq
, case when creNew = 1 then 0
-- ignore PKGs never used, not after Db2V9, not in 3 years
when lastUsed < current date - 3 years then 9
when use1 is null or use1 < '01.01.1000' then 1
when lastUsed >= use1 - win days then 1
when use2 is null or use2 < '01.01.1000' then 2
when lastUsed >= use2 - win days then 2
else 9 end useSeq
, p2.*
from p2
)
, pR as
( --- reason why to keep or deactivate a package ----------------------
select case
when sysentries > 0 then 'k1sysEnt>0' -- already deact
when sysentries < 0 then 'k2sysEnt<0' -- should not exist
when creNew = 1 then 'k3cre>15d' -- newly created
when pctSeq <= 2 then 'k4pcT<=2' -- last 2 compile
when creSeq <= 2 then 'k5cre<=2' -- last 2 compile
when useSeq <= 2 then 'k6use<=2' -- last 2 lastUsed
when lastUsed > current date - 123 days
then 'k7use<4Mo' -- wait zuegelschub
else 'd0deact' end dsr
, pS.*
from pS
)
, p as
( --- decode dsr ------------------------------------------------------
select left(dsr, 1) deact, substr(dsr, 3) reason, pR.*
from pR
)
select strip(collid) collid, strip(name) pk, strip(version) vers
, valid, operative, creSeq, useSeq, pctSeq, lastUsed
from p
where deact = 'd'
-- fetch first 1000 rows only
with ur
$]
$/selPk/
proc $@/rebindPk/
$arg explNo
say 'rebindPk' $COLLID'.'$PK'('$VERS')' $*+
'cre='$CRESEQ 'pct='$PCTSEQ 'use='$USESEQ $LASTUSED
rb = 'rebind package('$COLLID'.'$PK'.('$VERS'))',
'enable (cics ) cics(loeschen) planmgmt(extended)' ,
|| copies(' explain(no)', $explNo == 1)
say rb
rr = sqlDsn(bo, $dbSys, rb, '*')
cd = ''
sqlExpl = 0
sqlFree = 0
sqlOth = 0
msg = ''
if rr <> 0 then $@[
do bx=1 to m.bo.0
sx = pos('SQLCODE=', m.bo.bx)
if sx > 0 then do
c1 = word(substr(m.bo.bx, sx+8), 1)
if wordPos(c1, $sqlExpl) > 0 then
sqlExpl = sqlExpl + 1
else if wordPos(c1, $sqlFree) > 0 then
sqlFree = sqlFree + 1
else
sqlOth = sqlOth + 1
if wordPos(c1, cd) < 1 then
cd = cd c1
end
if word(m.bo.bx, 1) = 'DSNT219I' then
if $VALID = 'N' then
msg = msg 'warn(DSNT219I: ok, but current invalid)'
else
say ' ##dsnt219I but valid='$VALID
end
$]
doFree = sqlFree > 0 & sqlOth == 0
$= rCnt =- $rCnt + 1
say '#rc='rr 'doFree='doFree msg 'sqlCodes='cd 'rb='rb
if rr = 0 then $@[
$] else if rr=4 & cd = '' & msg <> '' then $@[
$] else $@[
do bx=1 to m.bo.0
say ' ' strip(m.bo.bx, 't')
end
if sqlExpl <> 0 then $@[
if $explNo == 1 then $@[
say 'explain sqlCode but already explain(no)'
$] else $@[
say 'retrying with explain no'
$@% rebindPk 1
$]
$] else if doFree then $@[
fr = 'free package('$COLLID'.'$PK'.('$VERS'))'
say ' trying to' fr
rr = sqlDsn(fo, $dbSys, fr, '*')
say ' rc='rr 'for' fr
if rr <> 0 then do
do fx=1 to m.fo.0
say ' ' strip(m.fo.fx, 't')
end
end
$]
$]
if $rCnt // 10 = 0 then $@[
say 'after' $rCnt 'rebinds sleeping 1 second' time()
call sleep 1, 0
$]
$/rebindPk/