• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to create Index using SCALAR functions on columns in UDB

 
Kranthi k Bandaru
Greenhorn
Posts: 7
IBM DB2 Eclipse IDE MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 7
IBM DB2 Eclipse IDE MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What version of DB2 are you on? as I only found mention of indexes on functions on oracle
 
Kranthi k Bandaru
Greenhorn
Posts: 7
IBM DB2 Eclipse IDE MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Iam using the UDB DB2 9.5.4 version.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
and you have evidence to show that it supports this functionality?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic