Informix/informix reference
How to identify 'BAD' indices in a database
@ipajama
2011. 11. 22. 23:46
728x90
Problem(Abstract)
A quick method is needed to identify all indices in a database that have been marked 'bad' by IBM® Informix Dynamic Server™ (IDS) database server.
Resolving the problem
You may use this SQL query to identify all indices that have been marked as "bad" in your IDS instance:
- (SELECT st.tabname, si.idxname, hex(spt.flags) flags
FROM sysindexes si, systables st, sysmaster:sysptnkey spt
WHERE st.tabid = si.tabid AND
st.partnum = spt.partnum AND
sysmaster:bitval(spt.flags, 64) = 1 AND
st.partnum !=0 AND
si.idxname NOT IN (
SELECT sfs.indexname
FROM sysfragments sfs
WHERE sfs.tabid = st.tabid)
GROUP BY 1,2,3
)
UNION ALL
(SELECT st.tabname, sf.indexname idxname , hex(spt.flags) flags
FROM systables st, sysmaster:sysptnkey spt, sysfragments sf
WHERE st.tabid = sf.tabid
AND sf.fragtype = 'I'
AND sf.partn = spt.partnum
AND sysmaster:bitval(spt.flags, 64) = 1
GROUP BY 1,2,3
)
ORDER BY 1
728x90