Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    31

    Question Unanswered: Sysindexes & syskeys

    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.
    Last edited by ajh; 05-04-11 at 03:47. Reason: Additional Info

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Following this link and you'll find the answer on your question:

    http://www.dbforums.com/db2/1645408-...le-report.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •