zOs/SQL/PLANCLEA
$#:
dbSys = DP4G
cre = cmnBatch
keyTb = tPlanClea
keyCr = A540769
$@ call sqlConnect $dbSys
$@creKeyTb
$@cleanPlan
$*************** clean plan table
$@proc $@/cleanPlan/
$*( stCol = sql2One('select col from' $-keyCr'.'$-keyTb' ,
order by col fetch first 1 row only', , ,,'-')
say 'start collection=' stCol
call sql2St 'select collid, count(*) cnt from' $-cre'.plan_table' ,
"where collid >= '"stCol"'",
'group by collid order by collid', cc
say m.cc.0 'collections in' $-cre".plan_table >= '"stCol"'"
$do cx=1 to m.cc.0 $@/doCol/
say 'col' m.cc.cx.collid m.cc.cx.cnt
$$- 'col' m.cc.cx.collid m.cc.cx.cnt '*********' time()
$@% insKeys - m.cc.cx.collid, m.cc.cx.cnt
$@% delPlan - m.cc.cx.collid, m.cc.cx.cnt
$<>
$<=[
delete from $keyCr.$keyTb ;
commit ;
$]
call sqlStmts
$<>
$/doCol/
$*) $@% delDsn DSN_PREDICAT_TABLE
$/cleanPlan/
$*************** delete rows from dsn_*, that are not in plan_table
$@proc $@/delDsn/
parse arg , tb
$= crTb =- $cre'.'tb
say 'delDsn' tb 'crTb='$crTb
call sql2St 'select queryNo, count(*) cnt from' $crTb ,
'group by queryNo', qq
say 'delDsn' $crTb':' m.qq.0 'queryNo'
$do qx=1 to m.qq.0 $@[
$=qNo =- m.qq.qx.queryNo
$=cnt=- m.qq.qx.cnt
say 'queryNo' $qNo 'count' $cnt
$<>
$<=[
select 'toDelete ' || count(*) || ' from $cnt: '
|| count(distinct explain_time)
|| value('*' || char(min(explain_time))
|| '-' || char(max(explain_time)), '---')
from $crTb d
where queryNo = $qNo
and explain_time < current timestamp - 20 days
and not exists (select 1
from $cre.plan_table p
where p.queryNo = d.queryNo
and p.explain_time = d.explain_time
)
$]
call sqlStmts
$]
call err 'tstEnd'
$= col =- col
$= cnt =- cnt
$<>
$<=/delSql/
select 'toDelete ' || count(*) || ' from $cnt: '
|| count(distinct explain_time)
|| value('*' || char(min(explain_time))
|| '-' || char(max(explain_time)), '---')
from $cre.plan_table t
where collid = '$col'
and explain_time < current timestamp - 20 days
and not exists (select 1
from $keyCr.$keyTb k
where t.collid = k.col
and t.progName = k.pkg
and t.version = k.vers
and t.explain_Time = k.exTi
)
$/delSql/
call sqlStmts
$/delDsn/
$*************** delete rows from plan table, that are not in keys
$@proc $@/delPlan/
parse arg , col, cnt
say 'del' col 'cnt' cnt
$= col =- col
$= cnt =- cnt
$<>
$<=/delSql/
select 'toDelete ' || count(*) || ' from $cnt: '
|| count(distinct explain_time)
|| value('*' || char(min(explain_time))
|| '-' || char(max(explain_time)), '---')
from $cre.plan_table t
where collid = '$col'
and explain_time < current timestamp - 20 days
and not exists (select 1
from $keyCr.$keyTb k
where t.collid = k.col
and t.progName = k.pkg
and t.version = k.vers
and t.explain_Time = k.exTi
)
$/delSql/
call sqlStmts
$/delPlan/
$*************** insert keys for explains to keep
$@proc $@/insKeys/
parse arg , col, cnt
$= col =- col
say 'ins' $-col 'cnt' cnt
$<>
$<=/insSql/
insert into $keyCr.$keyTb
with p as
(
select collid col, name pkg, version vers, timestamp cre
, max(timestamp(lastUsed)
, value(( select min(n.timestamp)
from sysibm.sysPackage n
where n.location = p.location
and n.collid = p.collid
and n.name = p.name
and n.timestamp > p.timestamp
), current timestamp)) pNxt
from sysibm.sysPackage p
where collid = '$col'
)
, e (col, pkg, vers, cre, pNxt, exTi, exCnt, optHi, exNxt
, keep, keLa, l) as
(
select col, pkg, vers, cre, pNxt
, ( select min(e.explain_time)
from $cre.plan_table e
where p.col = e.collid and p.pkg = e.progName
and p.vers = e.version )
, cast( null as int)
, cast( '' as char(8))
, cast( null as timestamp)
, -9
, cast( null as timestamp)
, 0
from p
union all select col, pkg, vers, cre, pNxt, exTi
, ( select count(*)
from $cre.plan_table t
where e.col = t.collid and e.pkg = t.progName
and e.vers = t.version and e.exTi = t.explain_time )
, ( select max(case when optHint is not null and optHint<> ''
then optHint
when hint_Used is not null and hint_used <> ''
and hint_used <> 'APREUSE' then hint_used
else '' end)
from $cre.plan_table t
where e.col = t.collid and e.pkg = t.progName
and e.vers = t.version and e.exTi = t.explain_time )
, ( select min(explain_time)
from $cre.plan_table t
where e.col = t.collid and e.pkg = t.progName
and e.vers = t.version and e.exTi < t.explain_time )
, -8
, keLa
, l+1
from e
where e.l < 9999 and keep = -9
union all select col, pkg, vers, cre, pNxt, exTi, exCnt, optHi
, exNxt
, case when keLa is null and pNxt <= exNxt then -1
when pNxt <= exTi then -2
when optHi <> '' then -1
when exNxt is null or exNxt > exTi + 10 days then -1
when keLa is null and cre < exTi - 10 days then -1
when keLa < exTi - 20 days then -1
else -2 end
, keLa
, l+1
from e
where l < 9999 and keep = -8
union all select col, pkg, vers, cre, pNxt, exTi, exCnt, optHi
, exNxt
, keep + 2
, case when keep = -2 then keLa
when optHi <> '' then keLa
else exTi end
, l+1
from e
where e.l < 9999 and keep in (-1, -2)
union all select col, pkg, vers, cre, pNxt, exNxt
, cast(null as int), ''
, cast(null as timestamp)
, -9
, keLa
, l+1
from e
where l < 9999 and keep in (1, 0) and exNxt < pNxt
)
select col, pkg, vers, exTi
from e
where keep = 1 and exTi is not null
$*(
select substr(strip(col) || '.' || strip(pkg) || '#' || vers, 1, 30)
, exTi, keep, l, exCnt, optHi, exNxt, keLa, cre, pNxt
from e
where keep >= 0
order by col, pkg, cre, l
$*)
$/insSql/
call sqlStmts
$/insKeys/
$*************** create keyTb, if it does not exist
$@proc $@/creKeyTb/
if catTbLastCol($-keyCr, $-keyTb) == '4 EXTI' then
return
$<>
$<=/ddl/
drop TABLESPACE dA540769.PLANCLEA;
commit;
CREATE TABLESPACE PLANCLEA
IN DA540769
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 64
COMPRESS YES CLOSE YES
CCSID UNICODE;
CREATE TABLE $keyCr.$keyTb
( COL VARCHAR(128) NOT NULL WITH DEFAULT
, pkg VARCHAR(128) NOT NULL with default
, VERS VARCHAR(122) NOT NULL WITH DEFAULT
, exTi TIMESTAMP NOT NULL WITH DEFAULT
) IN DA540769.PLANCLEA
VOLATILE
DATA CAPTURE CHANGES
CCSID UNICODE;
CREATE INDEX $keyCr.$-[overlay('i', $keyTb, 1)$]
ON $keyCr.$keyTb
( COL
, pkg
, VERS
, exTi
) -- no other fields allowed ||||
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
NOT PADDED
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
commit;
$/ddl/
call sqlStmts
$/creKeyTb/
$#out 20150109 15:05:42
COL1
toDelete 0 from 4: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 4: ' || count(dis...
COL1
toDelete 0 from 4: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 4: ' || count(dis...
COL1
toDelete 0 from 20: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 20: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 264: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 264: ' || count(d...
COL1
toDelete 0 from 20: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 20: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 264: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 264: ' || count(d...
COL1
toDelete 0 from 44: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 44: ' || count(di...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 264: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 264: ' || count(d...
COL1
toDelete 0 from 49: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 49: ' || count(di...
COL1
toDelete 0 from 5: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 5: ' || count(dis...
COL1
toDelete 0 from 24: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 24: ' || count(di...
COL1
toDelete 0 from 21: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 21: ' || count(di...
COL1
toDelete 0 from 45: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 45: ' || count(di...
COL1
toDelete 0 from 7: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 7: ' || count(dis...
COL1
toDelete 0 from 56: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 56: ' || count(di...
COL1
toDelete 0 from 35: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 35: ' || count(di...
COL1
toDelete 0 from 120: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 120: ' || count(d...
COL1
toDelete 0 from 134: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 134: ' || count(d...
COL1
toDelete 0 from 35: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 35: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 35: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 35: ' || count(di...
COL1
toDelete 0 from 50: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 50: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 7: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 7: ' || count(dis...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 49: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 49: ' || count(di...
COL1
toDelete 0 from 40: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 40: ' || count(di...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 25: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 25: ' || count(di...
COL1
toDelete 0 from 6: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 6: ' || count(dis...
COL1
toDelete 0 from 30: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 30: ' || count(di...
COL1
toDelete 0 from 5: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 5: ' || count(dis...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 6: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 6: ' || count(dis...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 18: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 18: ' || count(di...
COL1
toDelete 0 from 21: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 21: ' || count(di...
COL1
toDelete 0 from 102: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 102: ' || count(d...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 5: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 5: ' || count(dis...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 21: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 21: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 21: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 21: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 21: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 21: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 21: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 21: ' || count(di...
COL1
toDelete 0 from 64: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 64: ' || count(di...
COL1
toDelete 0 from 20: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 20: ' || count(di...
COL1
toDelete 0 from 18: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 18: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 48: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 48: ' || count(di...
COL1
toDelete 0 from 6: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 6: ' || count(dis...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 35: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 35: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 16: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 16: ' || count(di...
COL1
toDelete 0 from 28: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 28: ' || count(di...
COL1
toDelete 0 from 36: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 36: ' || count(di...
COL1
toDelete 0 from 20: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 20: ' || count(di...
COL1
toDelete 0 from 35: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 35: ' || count(di...
COL1
toDelete 0 from 5: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 5: ' || count(dis...
COL1
toDelete 0 from 64: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 64: ' || count(di...
COL1
toDelete 0 from 18: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 18: ' || count(di...
COL1
toDelete 0 from 22: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 22: ' || count(di...
COL1
toDelete 0 from 18: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 18: ' || count(di...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 12: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 12: ' || count(di...
COL1
toDelete 0 from 15: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 15: ' || count(di...
COL1
toDelete 0 from 30: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 30: ' || count(di...
COL1
toDelete 0 from 30: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 30: ' || count(di...
COL1
toDelete 0 from 6: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 6: ' || count(dis...
COL1
toDelete 0 from 64: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 64: ' || count(di...
COL1
toDelete 0 from 25: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 25: ' || count(di...
COL1
toDelete 0 from 20: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 20: ' || count(di...
COL1
toDelete 0 from 6: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 6: ' || count(dis...
COL1
toDelete 0 from 15: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 15: ' || count(di...
COL1
toDelete 0 from 18: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 18: ' || count(di...
COL1
toDelete 0 from 6: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 6: ' || count(dis...
COL1
toDelete 0 from 6: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 6: ' || count(dis...
COL1
toDelete 0 from 24: 0---
1 rows fetched: select 'toDelete ' || count(*) || ' from 24: ' || count(di...
*** run error ***
tstEnd
$#out 20150109 15:04:49