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

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

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.

dick scherrer · Posted: Fri Sep 26, 2008 5:06 am

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?

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

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.

dick scherrer · Posted: Fri Sep 26, 2008 5:55 am

Hello,

guptae · Posted: Fri Sep 26, 2008 11:39 am

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

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

thanks a lot.