aspose file tools*
The moose likes JDBC and the fly likes How to create Index using SCALAR functions on columns in UDB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to create Index using SCALAR functions on columns in UDB" Watch "How to create Index using SCALAR functions on columns in UDB" New topic
Author

How to create Index using SCALAR functions on columns in UDB

Kranthi k Bandaru
Greenhorn

Joined: Feb 28, 2012
Posts: 7

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

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Feb 28, 2012
Posts: 7

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

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Feb 28, 2012
Posts: 7

Iam using the UDB DB2 9.5.4 version.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

and you have evidence to show that it supports this functionality?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to create Index using SCALAR functions on columns in UDB