How to create Index using SCALAR functions on columns in UDB

[Kranthi k Bandaru]

Hello ,

I am trying to create an Index in UDB on a column with SCALAR functions. Can someone help me to get the correct syntax. I tried some statements, but it didnot helpme.

Requirement: Table ABCD.USER_HIST has User_Id (Varchar 10) and T_STM_UPD (Timestamp) columns.

Need an index like this : CREATE INDEX ABCD.USER_HISTORY ON ABCD.USER_HIST (USER_ID,DATE(T_STMP_UPD));

The reason for creating the above index is the following query .

SELECT * FROM ABCD.USER_HIST WHERE USER_ID= 'KXXB123' AND DATE(T_STMP_UPD) = CURRENT_DATE - 1 DAYS ORDER BY T_STMP_UPD DESC;

Any suggestions are welcome.

Thanks
Kranthi

[Wendy Gibbons]

after reading this page:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafycrtindx.htm

I am wondering why you are doing the date(timestamp_coloum) bit?

[Kranthi k Bandaru]

Hi Wendy ,

I read in some forums that the indexes are created on the exact column names or along with the functions associated with columns..

Example: Create Index XXX on Table ABCD (FIRST_NAME) is different from Create Index XXX on Table ABCD (UPPER(FIRST_NAME))..

So we have to create a index specific to what select query we are going to use.

Hope it clarifies your question. ANy suggestions ?

Thanks
Kranthi

[Wendy Gibbons]

What version of DB2 are you on? as I only found mention of indexes on functions on oracle

[Kranthi k Bandaru]

Iam using the UDB DB2 9.5.4 version.

[Wendy Gibbons]

and you have evidence to show that it supports this functionality?