Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
V9 - Expression indices

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

New User


Joined: 21 Mar 2011
Posts: 13
Location: usa

PostPosted: Fri Aug 05, 2011 4:04 am    Post subject: V9 - Expression indices
Reply with quote

Finally, we are converting to V9 NFM (instead of skip-release to V10 due to our cowardly DBAs).
My question is:
We have a lot of predicates like SUBSTR(COLNAME, 226,8).
It performs poorly. We have two choices. We can split COLNAME into several columns or we can create expression indices on SUBSTR(COLNAME, 226,8) - we have many substrings.
Will we get dissent improvements with the expression indices? What should we take into account? Please share your experience with us.

Thank you
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Aug 05, 2011 5:26 pm    Post subject:
Reply with quote

Hi Vadim,

Quote:
Finally, we are converting to V9 NFM (instead of skip-release to V10 due to our cowardly DBAs).
Skip's can done in one of the development environments, if there are a lot. No DBA want's to wake up @ mid-night, seeing an error message saying "SURPRISE!, you didn't expect me, did you!, now suffer my wrath".

Index on Expressions - Its a good thing, but test how much of impact it makes on INSERT and UPDATE statements in batch. If the table is being loaded rebuild would take care of it.
SUBSTR(COLNAME, 226,8) <-- This seems to be a simple sub-string, things can get complicated, if you start doing sub-string on a sub-string and so on...

Thanks,
Sushanth
Back to top
View user's profile Send private message
vadim vashchenko

New User


Joined: 21 Mar 2011
Posts: 13
Location: usa

PostPosted: Fri Aug 05, 2011 7:26 pm    Post subject: Thank you.
Reply with quote

Thank you!

That is what I expected: Insert/update might take more resources as well as a build-index phase of the utilities. Again, I appreciate your time.
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 Clustered Index with UCASE Key Expres... Auryn DB2 2 Mon Feb 02, 2015 8:00 pm
No new posts CASE Expression with Multiple Entries dbzTHEdinosauer DB2 8 Thu Jun 30, 2011 1:00 pm
No new posts CASE Expression krithikaj DB2 5 Wed Dec 22, 2010 4:40 am
No new posts COL op expression tamal_babu DB2 5 Mon Sep 13, 2010 7:05 pm
No new posts Problem in SQL query : CASE expression Rindia DB2 2 Fri Aug 07, 2009 10:30 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us