1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
|
set schema = SYSIBM ;
-- analyse du cluster ratio des index cluster
select substr(TB.CREATOR, 01, 08) as SCHEMA
, substr(TB.NAME, 01, 08) as TABLE
, substr(IX.NAME, 01, 10) as INDEX
, IX.UNIQUERULE as UNIQ
, IX.CLUSTERING as CLSG
, IX.CLUSTERED as CLSD
, IX.CLUSTERRATIO as RATIO
, TB.CARDF as CARDF
, TB.NPAGESF as NPAGESF
, subq.RGISR as REORGINSERTS
, subq.RGDEL as REORGDELETES
, subq.RGUPD as REORGUPDATES
from SYSTABLES as TB
inner join SYSTABLESPACE as TS
on TS.DBID = TB.DBID
and TS.NAME = TB.TSNAME
inner join SYSINDEXES as IX
on IX.TBCREATOR = TB.CREATOR
and IX.TBNAME = TB.NAME
inner join
(select DBID
, PSID
, cast(sum(REORGINSERTS) as bigint) as RGISR
, cast(sum(REORGDELETES) as bigint) as RGDEL
, cast(sum(REORGUPDATES) as bigint) as RGUPD
from SYSTABLESPACESTATS
group by DBID
, PSID) subq
on subq.DBID = TB.DBID
and subq.PSID = TS.PSID
where IX.CLUSTERING = 'Y' -- index cluster
and IX.CLUSTERED = 'N' -- mal organisé
and TB.CREATOR = 'XXXX'
and TB.TYPE = 'T' -- type table
and (TB.CARDF = -1 or TB.CARDF >0) -- sauf tables vides
and TB.NAME like 'YYYY%' |
Partager