How to identify 'BAD' indices in a database

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  

Repeat the query in each database in your instance. To repair any bad indices, drop and recreate them.

http://www-01.ibm.com/support/docview.wss?uid=swg21193229