can i create a index using substric in DB2 9 ?

[Venkat Avinash]

Hi,

Is below Create Index DDL Valid?

Create index index_name on table_name (substr(col1,2, 3) ASC);

[vijay2082]

Hi,

I think it should give an error as column not found. How can you apply substr on the column name itself ? WHat are you trying to achieve, by the way ?

I will try it on a database, once I am free and let you know exactly.

Cheers, Vijay

[Venkat Avinash]

Hi Vijay,

To answer the actual need for this..
We have a column which is made up of different fields like
1. a indicator
2. Record number
along with them are date and time.

so, I was thinking If it is possible to create index only on 1 and 2 instead of all the fields in the column..

Also, I have checked a DB2 blog related to this..below is a link...

http://catterallconsulting.blogspot.com/2009/05/db2-9-for-zos-rx-for-database-design.html

Just need some help in validating it:)

[vijay2082]

Hi Venkat,

Unfortunately db2 v9.7 for LUW still doesn't support index on expressions. (Although it does exist on DB2 V9.1 Z OS).

Suggest you raise a call with IBM and check more on this one. Also check if you can try altering the source table structure so that it can be benefited from the index access and simplify our world.

Cheers, Vijay