Reading functional index from systables
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
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,index
keyarray_out(b.
index
keys)
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
www.askdbmgt.com
Blog:
http://informix-myview.blogspot.com/
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------
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