Informix

 View Only

  • 1.  Reading functional index from systables

    Posted Mon May 11, 2020 01:19 PM
    Hi!

    I´m currently trying to read the information of an functional index from the systables.
    The only way I find out, was to use the indexkeyarray_out(sysindices.indexkeys) - Function.

    But I found no official documentation for this (only errors after upgrade).
    Only this page here: getting functional index info from sysindices.indexkeys - Informix

    My test-example:
    CREATE TABLE Persons (
    LastName varchar(255,30),
    FirstName varchar(128,20),
    Address varchar(255),
    City char(50),
    info lvarchar(500),
    id serial
    );

    create function if not exists my_lower( s varchar(255) ) returning varchar(255) with (not variant )
    return lower(s); end function;
    create function if not exists my_lower( s char(796) ) returning char(796) with (not variant )
    return lower(s); end function;
    create function if not exists my_lower( s lvarchar(796) ) returning lvarchar(796) with (not variant )
    return lower(s); end function;
    create function if not exists multi_lower( s varchar(255), x varchar(255) ) returning varchar(255) with (not variant )
    return lower(s); end function;

    And then i created the following indexes as test (Result of "SELECT indexkeyarray_out(sysindices.indexkeys) FROM sysindices" as comment):
    create index i_persons_idx1 on persons(LastName); -- "1 [1]"
    create index i_persons_idx1desc on persons(LastName DESC); -- "-1 [1]"
    create index i_persons_fu_idx1 on persons(my_lower(LastName));-- "<574>(1) [1]"
    create index i_persons_idxAll1 on persons(city, my_lower(address), id DESC); -- "4 [1], <574>(3) [1], -6 [1]"
    create index i_persons_idxAll2 on persons(my_lower(address) DESC, id DESC); -- "<574>(-3) [1], -6 [1]"
    create index i_persons_idxMultiAll1 on persons(multi_lower(address, FirstName) DESC, id DESC); -- "<576>(-3, 2) [1], -6 [1]"

    Shouldn´t  I use this function? Or is there a better solution for reading out this information (over JDBC)?

    ------------------------------
    Kind Regards
    Stefan
    ------------------------------

    #Informix


  • 2.  RE: Reading functional index from systables

    IBM Champion
    Posted Mon May 11, 2020 02:56 PM
    Stefan:

    That's how I do it in myschema. I don't know any other way. Here's the query that myschema uses:

    SELECT a.tabid, a.partnum, a.nrows, a.tabname, a.owner, 
           indexkeyarray_out(b.indexkeys) AS allparts, 0 , a.statchange 
    FROM "informix".systables a, outer ("informix".sysindices b, outer "informix".sysobjstate s) 
    WHERE a.tabid = b.tabid 
        AND tabtype = 'T'  
        AND a.tabid >= 100  
        AND a.tabid = s.tabid  
        AND s.objtype = 'I'  
        AND b.idxname = s.name  
        AND s.state != 'D'  
        AND a.tabname MATCHES ? 
    ORDER BY 1, 3, 4, 5;

    Where the question mark is replaced at runtime with the name of the current table being processed.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.






    Original Message