Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004

    Unanswered: getting functional index info from sysindices.indexkeys

    I am trying to write some SQL to retrieve index information from sysindices so that I can re-create the schema of a table. Normal indexes are OK, I can just use the sysindexes view, and link to the syscolumns table, which will give me the columns that the index is on.

    The problem comes with functional indexes. I appreciate that the procid exists in the indexkeys columns, but how can I extract this ??? is there an equivalent to ikeyextractcolno for the procid ???

    Thanks in advance


  2. #2
    Join Date
    Nov 2004

    You can extract the complete schema of the table using dbschema which will include the indexes on the table also.

    dbschema -d db_name -t table_name

    I hope it helps.

  3. #3
    Join Date
    Dec 2004
    Yes, I appreciate you can do this with DBSchema, but I have written an app that goes throught all stored procs, views, and tabls, and extracts them to individual files in relevant directories (e.g. c:\DBSchema\Tables c:\DBSChema\Views etc). It all works fine for all exept functional indexes.

    Thanks Anyway


  4. #4
    Join Date
    Apr 2004
    Malang, Indonesia

    Question the new user of informix

    I am want to try informix. but I do not have the informix sortware. can you send me that software,please ?

  5. #5
    Join Date
    Feb 2005
    if you are still interested, this might be an answer:

    select ikeyextractcolno(indexkeys,<nr>) from sysindices
    where nr is a contor from 1 to ncols

  6. #6
    Join Date
    Feb 2005
    Hi Jason,

    to extract the id of the function you could select
    indexkeyarray_out(indexkeys) as allparts from sysindices
    Then you will get something like "-3 [1], <526>(5) [1], 4 [1]".

    The "<526>" is the ID of the function (procid in sysprocedures).

    The "[1]" seems to be the operator class (opclassid in sysopclasses)
    which you only have to specify in your create statement when its not the default value
    (e.g. btree_ops for default access method btree).

    The "-3", "5" and "4" are the column numbers (colno in syscolumns),
    when less than zero then in DESC order.

    So this Index would be something like "(colno3 DESC, ud_function(colno5), colno4)".

    If you have one or more user defined functions in your index,
    all 16 parts in sysindexes are 0.

    I just checked out these things by trying. So no guarantee.
    When somebody has exact information, please let me know.
    Last edited by ifx; 02-17-05 at 12:43.

Posting Permissions

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