Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Indexing in db2 with expressions

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
knn9413

New User


Joined: 23 Jul 2009
Posts: 17
Location: US

PostPosted: Tue Sep 20, 2011 2:42 am    Post subject: Indexing in db2 with expressions
Reply with quote

Hi,

Here is my problem. I have a column defined as CHAR(10) and it has an index defined on it. I need to fetch the max value in this column and add 1 to it. The contents of the column is present as 129, 92, 356.. (Whoever designed the database wasn't thinking).
I need to order them as 92,129,356 and add 1 to the highest number (356 in this case). CAST coupled with the ORDER BY can achieve this, however I believe the performance will be impacted. (The table is pretty large with around 15 million rows)

My question is:

Is it possible to define another index with the CAST function on the same column? If yes, how do I use that given the fact that I have 2 different indices on the same column

An replies or suggestions will be highly appreciated
Thanks in advance
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Sep 20, 2011 4:07 pm    Post subject:
Reply with quote

Which DB2 version?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 20, 2011 4:17 pm    Post subject:
Reply with quote

why don't you just add an integer column,
populate it with a one-time = int(char(column))
and put an index on new int-column
modify existing logic:
and when you increment the counter, also set char-column to the newly incremented int-column

that way you have this idiot char-column still available for what ever-use
except for incrementing, thus you would not suffer the impact of search for a numeric value in a char column.
Back to top
View user's profile Send private message
knn9413

New User


Joined: 23 Jul 2009
Posts: 17
Location: US

PostPosted: Thu Sep 22, 2011 6:18 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
why don't you just add an integer column,
populate it with a one-time = int(char(column))
and put an index on new int-column
modify existing logic:
and when you increment the counter, also set char-column to the newly incremented int-column

that way you have this idiot char-column still available for what ever-use
except for incrementing, thus you would not suffer the impact of search for a numeric value in a char column.



Thanks Dick. That was the last option that I had on my plate..

gylbharat, we are using db2 v 9
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu Sep 22, 2011 9:04 pm    Post subject:
Reply with quote

Code:
create index testindex on testtable (int(col1))

select strip(char(max(int(col1))+1)) from testtable

should work & use testindex index-only as accesspath
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 indexing Rohit Umarjikar DB2 5 Thu Jun 21, 2012 2:08 pm
No new posts Indexing issue kanha DB2 1 Mon Jul 25, 2011 1:04 pm
No new posts COBOL performance for Complex arithme... Bill Woodger COBOL Programming 17 Thu Apr 07, 2011 5:42 pm
No new posts Regular Expressions in COBOL purusothaman COBOL Programming 4 Thu Feb 03, 2011 6:40 pm
No new posts FIELD(1:4): compilation is indexing t... Ensign COBOL Programming 7 Mon Jun 22, 2009 5:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us