Am trying to get duplicate indexes by using sysindexes and syskeys , the remaining problem is that the sql does not filter out the order of columns yet :
So am able to find indexes with the same columns on the same table (indexes created by a different user) but the sql does not distinguish the order of columns .. so it will still log the indexes with the same columns on the same table with a different order of columns .

SELECT
SUBSTR(TBNAME,1,12) AS TBNAME,SUBSTR(IXNAME,1,12) AS IXNAME,
SUBSTR(COLNAME,1,12) AS COLNAME,COLSEQ,SUBSTR(IXCREATOR,1,10) AS
IXCREATOR,COUNT(*) AS COUNT
FROM SYSIBM.SYSINDEXES
JOIN SYSIBM.SYSKEYS ON NAME=IXNAME
WHERE DBNAME = 'DATABASE'
GROUP BY TBNAME,IXNAME,COLNAME,COLSEQ,IXCREATOR
HAVING COUNT(*) > 1
WITH UR;

How can I get rid of the indexes on the same table with the same columns but a different order ? Thanks .

I found no SYSIBM.SYSKEYS on my system, what DB2 version on what platform are you using?

How about this?:
First I select indexes that are defined on the same table, then I exclude those cases where the indexes are in fact one and the same index, then I take the index with the lowest number of columns and/or the "smallest" name.
With I wanted to see if INDEX2 contains the same sequence of columns as INDEX1, possibly preceded by or followed by other columns. I received an error on that line, but I stopped debugging it because I wonder if such duplicate indexes can really occur in a DB2 database. AFAIK DB2 gives an error message when one tries to create an index with the same description as one already present.

So I wonder if this whole thing has any merit.

We're using DB2 ver 8 on z/os

Try this query:

SELECT A.* FROM SYSIBM.SYSINDEXES A INNER JOIN SYSIBM.SYSINDEXES B
ON A.TBNAME = B.TBNAME
AND A.TBCREATOR = B.TBCREATOR
AND A.COLCOUNT = B.COLCOUNT
AND NOT ( A.NAME = B.NAME AND A.CREATOR = B.CREATOR )
WHERE A.COLCOUNT =
( SELECT COUNT(*) FROM
SYSIBM.SYSKEYS C
WHERE C.IXNAME = B.NAME
AND C.IXCREATOR = B.CREATOR
AND ( COLNAME , COLSEQ, ORDERING )IN
( SELECT COLNAME,COLSEQ, ORDERING
FROM SYSIBM.SYSKEYS D
WHERE D.IXNAME = A.NAME
AND D.IXCREATOR = A.CREATOR ))

this last sql works fine ... Now I've got another question regarding DB2 ver 8 :

Suppose TABLE X with 2 indexes :
Index 1
COL A
COL B

and Index 2
COL A
COL B
COL C
COL D

is this Index 1 in DB2 ver 8 obsolete like in Oracle version 9,10 as Oracle has the ability to choose only some cols of index 2 .

of course DB2 can use Index 2 even if only COL A and COL B are part of the WHERE-clause.
But the use of Index 1 might be more efficient in that case.

Thanks a lot for your help umayer!

to eliminate duplicates (because of the multiple rows in SYSKEYS) and for clarity, its usefull to add a group by/order by
GROUP BY A.DBNAME
,A.CREATOR
,A.TBNAME
,A.NAME
ORDER BY A.DBNAME