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 42 43 44 45
| -- Cette requete extrait les informations liées aux index d'une table
-- PUD = P (primary) soit unique et not null, U (unique), D (duplicate)
-- Cls = Y (index cluster), N (non cluster)
-- Ratio = cluster ratio si c'est un index cluster (>=95%, c'est optimal)
-- Kseq = ordre de la colonne dans l'index
-- Col = nom de la colonne
-- Typ = type de la colonne
-- Lng = longueur de la colonne
-- Paramêtres à modifier en fonction du besoin :
-- IX.TBCREATOR
-- IX.TBNAME
set CURRENT SCHEMA = 'SYSIBM'
;
select substr(IX.TBCREATOR, 01, 04) as "Schm"
, substr(IX.TBNAME, 01, 08) as "Table"
, substr(IX.NAME , 01, 08) as "Index"
, IX.UNIQUERULE as "PUD"
, IX.CLUSTERING as "Cls"
, case when IX.CLUSTERING = 'Y'
then substr(digits(IX.CLUSTERRATIO), 3, 3)
else ' - '
end as "Ratio"
, KY.COLSEQ as "KSeq"
, substr(KY.COLNAME, 01, 30) as "Col"
, KY.ORDERING as "Ord"
, CO.COLTYPE as "Typ"
, CO.LENGTH as "Lng"
from SYSINDEXES as IX
left join SYSKEYS as KY
on KY.IXCREATOR = IX.TBCREATOR
and KY.IXNAME = IX.NAME
left join SYSCOLUMNS as CO
on CO.TBCREATOR = IX.TBCREATOR
and CO.TBNAME = IX.TBNAME
and CO.NAME = KY.COLNAME
where IX.TBCREATOR = 'SCH1'
and IX.TBNAME = 'TABX'
order by "Schm"
, "Table"
, case when "PUD" = 'P' then 0
when "PUD" = 'U' then 1
else 2
end
, "Index"
; |