zOs/SQL/PLANCNT
with c as
(
select collid, count(*) cnt
from cmnbatch.plan_table
group by collid
)
select count(*), sum(cnt), max(cnt), avg(cnt)
from c
with ur
;x;
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 name = 'NZERR90' and collid = 'NZ'
where name like '%' and collid = 'MF'
)
, 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 cmnBatch.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 cmnBatch.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 cmnBatch.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 cmnBatch.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 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
;x;
;x;
with p as
(
select collid col, name pkg, version vers, timestamp cre, max(lastUsed
, value(( select min(date(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 date)) pNxt
from sysibm.sysPackage p
where name = 'NZERR90' and collid = 'NZ'
)
, e (col, pkg, vers, cre, pNxt, exTi, exCnt, optHi, l) as
(
select col, pkg, vers, cre, pNxt
, select(min(e.explain_time) exTi
, count(*) exCnt
, 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) optHint
from p join cmnBatch.plan_table e
on p.col = e.collid and p.pkg = e.progName
and p.vers = e.version and e.explain_time < timestamp(pNxt)
group by col, pkg, vers, cre, pNxt, e.explain_time
)
select * from e
;x;
from cmnbatch.plan_table n
where
n.collid = e.collid
and n.progname = e.progname
and n.version = e.version
and n.explain_time > e.explain_time
), current date) eNxt
from cmnBatch.plan_table e
where progName = 'NZERR90' and collid = 'NZ'
group by collid, progName, version, explain_time
)
select value(p.col, e.col) col
, value(p.pkg, e.pkg) pkg
, value(p.vers, e.vers) vers
, case when
, cre, pNxt
, exTi, exCnt, optHint, eNxt
from p
full outer join e
on p.col = e.col and p.pkg = e.pkg and p.vers = e.vers
order by 1, 2, value (cre, exTi) desc, exTi desc
;x;
with p as
(
select collid col, name pkg, version vers, timestamp cre, max(lastUsed
, value(( select min(date(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 date)) pNxt
from sysibm.sysPackage p
where name = 'NZERR90' and collid = 'NZ'
)
, e as
(
select collid col, progName pkg, version vers
, explain_time exTi
, count(*) exCnt
, max(strip(optHint)) optHint
, value(( select min(date(n.explain_time))
from cmnbatch.plan_table n
where
n.collid = e.collid
and n.progname = e.progname
and n.version = e.version
and n.explain_time > e.explain_time
), current date) eNxt
from cmnBatch.plan_table e
where progName = 'NZERR90' and collid = 'NZ'
group by collid, progName, version, explain_time
)
select value(p.col, e.col) col
, value(p.pkg, e.pkg) pkg
, value(p.vers, e.vers) vers
, case when
, cre, pNxt
, exTi, exCnt, optHint, eNxt
from p
full outer join e
on p.col = e.col and p.pkg = e.pkg and p.vers = e.vers
order by 1, 2, value (cre, exTi) desc, exTi desc
;x;
select progname, collid, version, explain_time, count(*) cnt
, count(distinct explain_time) exTiS
from cmnBatch.plan_table
where progName = 'NZERR90' and collid = 'NZ'
and version = 'NZV0000216'
group by progname, collid, version, explain_time
with d as
(
select days(timestamp) - days(pcTimestamp) d
from sysibm.sysPackage
)
select count(*), d
from d
group by d
order by 1 desc
;x;
-- search duplicate explain times per program/version
with e as
(
select explain_time,
strip(collid) || '.' || strip(progName) || '.'
|| strip(version) cpv
from cmnBatch.plan_table
)
select explain_time, count(distinct cpv) cntCpv
, substr(min(cpv), 1, 30) cpvF
, substr(max(cpv), 1, 30) cpvT
from e
group by explain_time
order by 2 desc
fetch first 1000 rows only
with ur
;x;
with e as
(
select progname, collid, version, explain_time, count(*) cnt
, count(distinct explain_time) exTiS
from cmnBatch.plan_table
where progName = 'NZERR90' and collid = 'NZ'
and version = 'NZV0000216'
group by progname, collid, version, explain_time
)
select e.*, p.pcTimestamp
from e
left join sysibm.sysPackage p
on e.collid = p.collid and e.progName = p.name
and e.version = p.version
order by p.pcTimestamp desc, explain_time desc
with ur
;x;
select
with c (prg, col, cnt, cPr) as
(
select progname, collid, count(*), 1
from cmnBatch.plan_table
group by progname, collid
)
, t (prg, col, cnt, cPr) as
(
select '*', '*', sum(cnt), count(*) progs
from c
)
select * from t
union all select c.* from c
order by cnt desc
fetch first 10 rows only
with ur
select count(*), max(p.bind_time)
, substr(strip(p.collid) || '.' || p.progname, 1, 20)
, (select k.pcTimestamp
from sysibm.sysPackage k
where k.collid = p.collid
and k.name = p.progname
and k.version = p.version
and k.location = '' )
, p.version
from DB2XML.plan_table p
-- where bind_time >= '2011-01-01-00.00.00'
group by collid, progname, version
order by 4 desc, collid, progname, version desc
with ur
;;;
select count(*), max(pctimestamp) "maxPcTimestamp", owner
from sysibm.sysPackage
group by owner
order by 2 desc
with ur
;;
select year(bind_time), count(*) cnt
, sum(case when opthint <> '' then 1 else 0 end) cntHint
, sum(case when hint_used <> '' then 1 else 0 end) cntUseHi
from S100447.plan_table
group by year(bind_time)
order by 1 desc
with ur
;;;
select *
from S100447.plan_table
where opthint <> '' or hint_used <> ''
;;;;
insert into s100447.tOptHint
select * from cmnbatch.plan_table where opthint <> ''
union select * from cmnbatch.plan_table where opthint <> ''
union select * from S100006.plan_table where opthint <> ''
union select * from S100447.plan_table where opthint <> ''
union select * from S103931.plan_table where opthint <> ''