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
 

 

Why the load is so slow when there is a unique index

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

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Fri Sep 26, 2008 3:45 am    Post subject: Why the load is so slow when there is a unique index
Reply with quote

hi,
i'd like to know why a load is so slow when there is a unique index for the table.
if i drop the unique index, the load runs much faster.
why?
is there anything i could do to maintain the unique index and improve the load performance?
thanks.
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: Fri Sep 26, 2008 5:06 am    Post subject:
Reply with quote

Hello,

Loading no index should run faster than loading one or more index. . .

Is the load file in sequence? That may help.

If you load without the index and then create the ndex after the load, is the overall time better?
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Fri Sep 26, 2008 5:11 am    Post subject:
Reply with quote

thanks.
it takes 3 and a half hours to build the index.
a lot of time because the table has 3 billion records.
thanks again.
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: Fri Sep 26, 2008 5:55 am    Post subject: Reply to: why the load is so slow?
Reply with quote

Hello,

Quote:
a lot of time because the table has 3 billion records.
Yup, that would make "anything" take a while. . . icon_wink.gif

d
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri Sep 26, 2008 11:39 am    Post subject:
Reply with quote

Hello jctgf,

Load job for db2 table comprises of following Phases:

1.UTILINIT:In this phase initialization & set up been done
2. RELOAD: Records are loaded & written to temp files for indexes & foreign keys . Check constriants are checked for each rows.
3.Sort: Sorting of temp file records before creating indexes & checking refrential constraints, if indexes or foreign key exists. These phase is not required if
° There is not more than one key per table
° All keys are the same type
° The data being loaded or reloaded is in key order
4 BUILD:Creating indexes from temp file for alll the indexes .Detection of duplicate key occures at end of this phase.
5. INDEXVAL: Correction of unique index violations from the information in SYSERR, if any exist.
6.ENFORCE :Checking of referential constraints, and correction of violations.
7. REPORT : Generation of summary report.

So by now you already understood that if you are having a unique index then load will take more time
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sat Sep 27, 2008 2:54 am    Post subject:
Reply with quote

thanks a lot.
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 Can DFSPARM override SORTKEYS paramet... ashek15 DB2 2 Tue Jan 31, 2017 11:36 pm
No new posts CICS transaction slow response time vasanthz All Other Mainframe Topics 3 Thu Jan 19, 2017 1:31 am
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


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