zOs/SQL/RTSLASTU
--- unbenutzte indexe --------------------------------------------------
--- index nach last used in rts und package und unqique ---------------
set current path oa1p;
with i0 as ( -- last built from rts
select
max(value(date(LOADRLASTTIME ), '01.01.0001')
,value(date(REBUILDLASTTIME), '01.01.0001')
,value(date(REORGLASTTIME ), '01.01.0001')) laBui
, real(totalEntries) entries
, space * real(1024) space
, real(reorgInserts + reorgDeletes) insDel
, lastUsed
, creator cr
, Name ix
from sysibm.sysIndexSpaceStats
-- where Name like 'IMF%' and creator = 'OA1P'
)
, i1 as
( -- partitionen aggregieren
select count(*) parts
, value(max(lastUsed), '01.01.0001') lastUsed
, sum(entries) entries
, sum(space ) space
, sum(real(insDel) / max(1, days(current date)-days(laBui))) updDay
, cr
, ix
from i0
group by cr, ix
)
, p1 as -- package lastUsed joinen
(
select bQualifier cr, bName ix
, max(timestamp(lastUsed), pcTimestamp, timestamp) pLast
, p.*
from SYSIBM.SYSPACKDEP d
join sysibm.sysPackage p
on d.dLocation = p.location
and d.dCollid = p.collid
and d.dName = p.name
and d.dContoken = p.contoken
where d.bType = 'I'
)
, p2 as
(
select row_number() over ( partition by cr, ix
order by pLast desc) rn
, p1.*
from p1
)
, i2 as
( -- package mit neuestem lastUsed joinen
select j.dbName db, i1.cr, i1.ix
--, case when uniqueRule = 'D' then 'D' else 'U' end uniq
, uniqueRule uniq
, i1.lastUsed
, parts
, entries
, i1.space
, updDay
, p2.collid
, p2.name pkg
, p2.version
, p2.timestamp pkgCreated
, p2.pcTimestamp
, p2.lastUsed pkgLastUse
from i1
join sysibm.sysindexes j
on i1.cr = j.creator and i1.ix = j.name
left join p2
on i1.cr = p2.cr and i1.ix = p2.ix and rn = 1
where i1.lastUsed < '01.01.2010'
)
select substr(db, 1, 8) db
, substr(cr, 1, 12) cr
, substr(ix, 1, 40) ix
, uniq
, lastUsed
, parts
, substr(fosFmte7(entries), 1, 7) entries
, substr(fosFmtE7(space), 1, 7) space
, substr(fosFmte7(updDay), 1, 7) "upd/day"
, substr(collid, 1, 8) collid
, substr(pkg , 1, 8) pkg
, substr(version , 1, 40) version
, pkgCreated
, pcTimestamp
, pkgLastUse
from i2
where pkg is null
or ( pkgLastUse < '01.01.2010'
and pkgCreated < '2010-01-01-00.00.00'
and pcTimestamp < '2010-01-01-00.00.00'
)
order by db, cr, ix
;x;
(
select max( value(lastUsed, '01.01.0001')
, value(pLast , '01.01.0001')
) last
, i2.*
from i2
)
, y as
(
select year(last) y
, uniq
, count(*) ix
, sum(parts) parts
, sum(entries) entries
, sum(space) space
, sum(updDay) updDay
from i3
group by year(last), uniq
)
, t1 as
(
select 9998 y
, uniq
, sum(ix) ix
, sum(parts) parts
, sum(entries) entries
, sum(space) space
, sum(updDay) updDay
from y
group by uniq
)
, t2 as
(
select 9999 y
, '*' uniq
, sum(ix) ix
, sum(parts) parts
, sum(entries) entries
, sum(space) space
, sum(updDay) updDay
from t1
)
, u as
(
select * from t2
union all select * from t1
union all select * from y
)
select case when y > 9900 then 'total' else '' || y end lastY
, uniq, ix
, parts
, substr(fosFmtE7(entries), 1, 7) entries
, substr(fosFmtE7(space ), 1, 7) space
, substr(fosFmtE7(updDay ), 1, 7) "upd/Day"
from u
order by Y desc, uniq
;X;
, y as
order by 1 desc, uniq
;x;
;;;;
;;;
select *
from i3
order by creator, name
;X,
select lastUsed, '01.01.0001')
, value(pLast , '01.01.0001')
, value(date(pCre) , '01.01.0001')
) last
, g as
(
select lasty, uniq
, count(*) ix
, sum(parts) parts
, sum(entries) entries
, sum(space ) space
, sum(updDay ) updDay
from i2
group by lastY, uniq
)
, tU as
(
select 9998 lastY, uniq
, sum(ix) ix, sum(parts) parts, sum(entries) entries
, sum(space) space, sum(updDay) updDay
from g
group by uniq
)
, to as
(
select 9999 lastY, '*' uniq
, sum(ix) ix, sum(parts) parts, sum(entries) entries
, sum(space) space, sum(updDay) updDay
from tU
)
, u as
(
select * from to
union all select * from tU
union all select * from g
)
select case when lasty > 9900 then 'total' else '' || lastY end lastY
, uniq, ix
, parts
, substr(fosFmtE7(entries), 1, 7) entries
, substr(fosFmtE7(space ), 1, 7) space
, substr(fosFmtE7(updDay ), 1, 7) "upd/Day"
from u
order by lastY desc, uniq
;y;
--- unbenutzte indexe --------------------------------------------------
--- index nach last used year und unique -------------------------------
set current path = oa1p;
with i0 as (
select
case when lastUsed is Null then 0 else year(lastUsed) end lastY
, max(value(date(LOADRLASTTIME ), '01.01.0001')
,value(date(REBUILDLASTTIME), '01.01.0001')
,value(date(REORGLASTTIME ), '01.01.0001')) laBui
, real(totalEntries) entries
, space * real(1024) space
, real(reorgInserts + reorgDeletes) insDel
, lastUsed
, creator
, Name
from sysibm.sysIndexSpaceStats
-- where Name like 'INI%' and creator = 'OA1P'
)
, i1 as
(
select count(*) parts
, max(lastY) lastY
, sum(entries) entries
, sum(space ) space
, sum(real(insDel) / max(1, days(current date)-days(laBui))) updDay
, creator
, name
from i0
group by creator, name
)
, i2 as
(
select lasty
, case when uniqueRule = 'D' then 'D' else 'U' end uniq
, parts
, entries
, i1.space
, updDay
from i1
join sysibm.sysindexes j
on i1.creator = j.creator and i1.name = j.name
)
, g as
(
select lasty, uniq
, count(*) ix
, sum(parts) parts
, sum(entries) entries
, sum(space ) space
, sum(updDay ) updDay
from i2
group by lastY, uniq
)
, tU as
(
select 9998 lastY, uniq
, sum(ix) ix, sum(parts) parts, sum(entries) entries
, sum(space) space, sum(updDay) updDay
from g
group by uniq
)
, to as
(
select 9999 lastY, '*' uniq
, sum(ix) ix, sum(parts) parts, sum(entries) entries
, sum(space) space, sum(updDay) updDay
from tU
)
, u as
(
select * from to
union all select * from tU
union all select * from g
)
select case when lasty > 9900 then 'total' else '' || lastY end lastY
, uniq, ix
, parts
, substr(fosFmtE7(entries), 1, 7) entries
, substr(fosFmtE7(space ), 1, 7) space
, substr(fosFmtE7(updDay ), 1, 7) "upd/Day"
from u
order by lastY desc, uniq
;
--- indexe ohne RTS ---------------------------------------------------
select strip(creator) || '.' || name, i.*
from sysibm.sysIndexes i
where not exists ( select 1
from sysibm.sysIndexSpaceStats r
where i.creator = r.creator and i.name = r.name )
;;
select * from i
;;; select insDel / max(1, days(current date) - days(laBui))
, i0.*
from i0 i0;;
)
select count(*) ix
, sum(parts) parts
, sum(case when year(lastU) = 2012 then 1 else 0 end) c12
, sum(case when year(lastU) = 2011 then 1 else 0 end) c11
, sum(case when year(lastU) < 2011 then 1 else 0 end) c10sma
, sum(case when lastU is null then 1 else 0 end) cNull
, sum(c12) c12
, sum(c11) c11
, sum(c10sma) c10sma
, sum(cnull) cnull
, min(lastU)
, max(lastU)
from i
;;;
with i as (
select count(*) parts
, sum(case when year(lastUsed) = 2012 then 1 else 0 end) c12
, sum(case when year(lastUsed) = 2011 then 1 else 0 end) c11
, sum(case when year(lastUsed) < 2011 then 1 else 0 end) c10sma
, sum(case when lastUsed is null then 1 else 0 end) cNull
, max(lastUsed) lastU
, Name
from sysibm.sysIndexSpaceStats
-- where Name like 'INI%'
group by Creator, Name
)
select count(*) ix
, sum(parts) parts
, sum(case when year(lastU) = 2012 then 1 else 0 end) c12
, sum(case when year(lastU) = 2011 then 1 else 0 end) c11
, sum(case when year(lastU) < 2011 then 1 else 0 end) c10sma
, sum(case when lastU is null then 1 else 0 end) cNull
, sum(c12) c12
, sum(c11) c11
, sum(c10sma) c10sma
, sum(cnull) cnull
, min(lastU)
, max(lastU)
from i
;;;
-- order by lastUsed desc
;;;
select count(*) parts
, sum(case when year(lastUsed) = 2012 then 1 else 0 end) c12
, sum(case when lastUsed is null then 1 else 0 end) cNull
, max(lastUsed) lastU
, Name
from sysibm.sysIndexSpaceStats
where Name like 'INI%'
group by Creator, Name
-- order by lastUsed desc
;;;
select count(*), lastused
from sysibm.sysIndexSpaceStats
group by lastUsed
order by lastUsed desc
;;;
select count(*), lastused
from sysibm.sysTableSpaceStats
group by lastUsed
order by lastUsed desc
;;