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: 1190
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 IDCAMS RENAMING VSAM FILE ALONG WITH ... upendrasri JCL & VSAM 11 Tue Apr 17, 2018 10:42 pm
No new posts Unique IMS DB identifier ashek15 IMS DB/DC 1 Mon Mar 12, 2018 11:45 pm
No new posts can we hardcode a value in db2 load c... ram_vizag DB2 2 Wed Feb 28, 2018 11:04 pm
No new posts load and delete table through JCL wit... ram_vizag JCL & VSAM 9 Thu Feb 01, 2018 12:47 am
No new posts KSDS and Index file in cobol deepak14450 COBOL Programming 9 Fri Dec 22, 2017 9:36 am

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