IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Performance issue with DB2 Index


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Moderator Emeritus


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

PostPosted: Tue Mar 04, 2008 3:06 am
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
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

Moderator Emeritus


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

PostPosted: Tue Mar 04, 2008 3:17 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts exploiting Z16 performance PL/I & Assembler 2
No new posts Cobol file using index COBOL Programming 2
No new posts Issue after ISPF copy to Linklist Lib... TSO/ISPF 1
No new posts Facing ABM3 issue! CICS 3
Search our Forums:

Back to Top