Informix

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

That should work, but this may be more reliable:

select idxname from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Tue January 05, 2021 12:09 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

Original Message:
Sent: 1/5/2021 5:45:00 AM
From: Art Kagel
Subject: RE: How to find functional index in sysindexes?

That should work, but this may be more reliable:

select idxname from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Tue January 05, 2021 12:09 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

Wow, it's amazing that there is a way to check like this. Thank you.​

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Original Message:
Sent: Tue January 05, 2021 05:44 AM
From: Art Kagel
Subject: How to find functional index in sysindexes?

That should work, but this may be more reliable:

select idxname from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue January 05, 2021 12:09 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

Original Message:
Sent: 1/5/2021 12:10:00 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

Original Message:
Sent: 1/5/2021 9:17:00 AM
From: Paul Watson
Subject: RE: How to find functional index in sysindexes?

Look in sysindices and indexkeys. Something like ....

select idxname

from sysindices 

where indexkeys matches '*<[0-9]*>*'

Cheers

Paul

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

Original Message:
Sent: 1/5/2021 12:10:00 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

Great minds Paul. Note that you have to explicitely cast the indexkeys column to LVARCHAR or CHAR(1024) in order to compare it to a string. It is defined as a UDT, indexkeyarray(1024), that doesn't have an implicit cast to any string type defined.


------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Tue January 05, 2021 09:16 AM
From: Paul Watson
Subject: How to find functional index in sysindexes?

Look in sysindices and indexkeys. Something like ....

select idxname

from sysindices 

where indexkeys matches '*<[0-9]*>*'

Cheers

Paul

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

Original Message:
Sent: 1/5/2021 12:10:00 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

Original Message:
Sent: 1/5/2021 9:26:00 AM
From: Art Kagel
Subject: RE: How to find functional index in sysindexes?

Great minds Paul. Note that you have to explicitely cast the indexkeys column to LVARCHAR or CHAR(1024) in order to compare it to a string. It is defined as a UDT, indexkeyarray(1024), that doesn't have an implicit cast to any string type defined.


------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Tue January 05, 2021 09:16 AM
From: Paul Watson
Subject: How to find functional index in sysindexes?

Look in sysindices and indexkeys. Something like ....

select idxname

from sysindices 

where indexkeys matches '*<[0-9]*>*'

Cheers

Paul

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

Original Message:
Sent: 1/5/2021 12:10:00 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix

@Paul Watson
Thanks for the advice!​

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Original Message:
Sent: Tue January 05, 2021 09:16 AM
From: Paul Watson
Subject: How to find functional index in sysindexes?

Look in sysindices and indexkeys. Something like ....

select idxname

from sysindices 

where indexkeys matches '*<[0-9]*>*'

Cheers

Paul

Paul Watson

Oninit LLC

+1-913-387-7529

www.oninit.com

Oninit®️ is a registered trademark of Oninit LLC

Original Message:
Sent: 1/5/2021 12:10:00 AM
From: SangGyu Jeong
Subject: How to find functional index in sysindexes?

Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?


---- test step

$ dbaccess stores_demo -

Database selected.

> CREATE TABLE circles ( radius FLOAT );

Table created.

> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);
> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>

Routine created.

> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

Index created.

>
>
>
> select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;



owner informix
idxname cust_idx1

owner informix
idxname ts_data_location_spix

owner informix
idxname areaofcircleindex

3 row(s) retrieved.

>

Thanks for reading.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

#Informix