---
-- Creation du fichier de sortie pour etude sur les index
---
CL: DSPOBJD OBJ(
Mabib/IX*) OBJTYPE(*FILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/QRYINDEX) ;
---
-- Selection Global sur les index (Stat d'util, date etc ...)
---
select
TABLE_NAME, INDEX_NAME, ODOBNM as Noms, ODOBSZ as Size,
'AAAA-MM-JJ' as Format_Date,
Case ODCDAT when ' ' then ' ' else '20' || substr(ODCDAT, 5, 2) || '-' || substr(ODCDAT, 1, 2) || '-' || substr(ODCDAT, 3, 2) end as date_creation,
Case ODLDAT when ' ' then ' ' else '20' || substr(ODLDAT, 5, 2) || '-' || substr(ODLDAT, 1, 2) || '-' || substr(ODLDAT, 3, 2) end as date_modif,
Case ODUDAT when ' ' then ' ' else '20' || substr(ODUDAT, 5, 2) || '-' || substr(ODUDAT, 1, 2) || '-' || substr(ODUDAT, 3, 2) end as Last_used_date,
Case ODTDAT when ' ' then ' ' else '20' || substr(ODTDAT, 5, 2) || '-' || substr(ODTDAT, 1, 2) || '-' || substr(ODTDAT, 3, 2) end as reset_dat,
ODUCNT as Days_used_count
from qtemp.qryindex,
Mabib.sysindexes where ODOBNM = SYSTEM_INDEX_NAME
order by TABLE_NAME with ur ;
---
-- Selection Global sur les index (Stat d'util, date etc ...) avec nbr utilisation = 0
---
select
TABLE_NAME, INDEX_NAME, ODOBNM as Noms, ODOBSZ as Size,
'AAAA-MM-JJ' as Format_Date,
Case ODCDAT when ' ' then ' ' else '20' || substr(ODCDAT, 5, 2) || '-' || substr(ODCDAT, 1, 2) || '-' || substr(ODCDAT, 3, 2) end as date_creation,
Case ODLDAT when ' ' then ' ' else '20' || substr(ODLDAT, 5, 2) || '-' || substr(ODLDAT, 1, 2) || '-' || substr(ODLDAT, 3, 2) end as date_modif,
Case ODUDAT when ' ' then ' ' else '20' || substr(ODUDAT, 5, 2) || '-' || substr(ODUDAT, 1, 2) || '-' || substr(ODUDAT, 3, 2) end as Last_used_date,
Case ODTDAT when ' ' then ' ' else '20' || substr(ODTDAT, 5, 2) || '-' || substr(ODTDAT, 1, 2) || '-' || substr(ODTDAT, 3, 2) end as reset_dat,
ODUCNT as Days_used_count
from qtemp.qryindex,
Mabib.sysindexes where ODOBNM = SYSTEM_INDEX_NAME and ODUCNT =0
order by Last_used_date desc,TABLE_NAME with ur ;
Partager