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
 

 

Performance issue with DB2 Index

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Mar 04, 2008 2:02 am    Post subject: Performance issue with DB2 Index
Reply with quote

i have a table with 10 cols of which 1st 5 cols are part of index ..
now there is a requirement where in i need to chk cols 6 and 7 with inputs from a file ..



now this requirement is limited ... and as cheking of cols which are not part of index is taking more time
we have decided to add 6 and 7 as part of the index ..

will adding cols 6 and 7 to the index group deteriorate the
performance of other programs which dont need 6 and 7 as index ??

is there any other catch in this ?

Thanks,
ashish
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 04, 2008 3:06 am    Post subject:
Reply with quote

Hello,

Do you need to check only col 6&7 with the input from the file? If this new source of data needs only col6&7 "checked", adding the 2 additional colums to the existing index will not really to what you need.

If you now need to check all 7 columns, it will perform better adding the new cols to the existing index.

Adding the 2 additonal columns to the existing index will probably not add any substantial overhead, but care must be taken that whatever is done (adding the cols to an existing index or creating a new index for col 6&7), provides direct access to the rows.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Mar 04, 2008 3:11 am    Post subject: Reply to: Performance issue with DB2 Index
Reply with quote

thanks for the response dick ..

i need to chk 6 and 7 along with 1 and 2 ....

now the existing programs are using cols 1 -5 to update the table ie cols 6 - 10 .. so adding index to 6 and 7 will not cause any performance issues right ?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 04, 2008 3:17 am    Post subject:
Reply with quote

Hello,

Please re-read my earlier reply.

If you need a direct check against col 1,2,6&7, merely adding col 6&7 to the existing 1-5 index will not provide direct validation.

Adding a new index of 6&7 will not do what you need either.

If i understand what you need to validate, you probably want a new index of 1,2,6&7.

Quote:
so adding index to 6 and 7 will not cause any performance issues right ?
If i understand your requirement, this will just make the existing index bigger (whic would take a bit more space an a bit more cpu) without providing the direct access you want (which will cause poerformance issues).
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 setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am


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