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
 
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 Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm

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