My sysindexes has the following data:

select count(distinct(si.index_name))
from qsys2.sysindexes si, qsys2.syskeys sk
where si.table_schema like '%ABC%' -- for all schema's that contain ABC
and si.index_schema = sk.index_schema
and si.index_name = sk.index_name
;
This gives me the count fine. when i remove the count and make the query just ::
select distinct(si.index_name)
from qsys2.sysindexes si, qsys2.syskeys sk
where si.table_schema like '%ABC%' -- for all schema's that contain ABC
and si.index_schema = sk.index_schema
and si.index_name = sk.index_name
;
The query never executes. and then gives me a message " Timeout Disconnected " . I am querying a remote server.
Please help to alter query if there is a better way to do it.


I want a query to return only distinct indexes in all schemas.
The syskeys table has columns on which indexes are defined.
So i actually want to join both these tables and generate a list
of distinct indexes in all schemas.


Tables are huge and are on a remote network so applying only the
distinct clause is not what i am looking out for.
The task i am doing because old indexes in a schema A may be based on column(1) and in new schema B the same index may be based on column(1) & column(2). So i want to actually restore the schema A with the latest definition from schema B.
Please help.

Following this link and you'll find the answer on your question:

Show all indexes on the table as report