DB2 Load and Unload performance

cmbb · New User Joined: 01 Feb 2006 Posts: 4

I'm trying to load and unload about 1 million records into a table and it takes about 45 minutes each action (load/unload).
Could anyone help me giving some tips about how to improve the performance? Is there any DB2 parameter that helps this?
Thanks.

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

try out with Sharelevel reference. enforce constraints no and log no option.

cmbb · New User Joined: 01 Feb 2006 Posts: 4

I have tried with log no and nocopypend option.
Is there any DB2 parameter that i must try?
Thanks

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

Try using DISPLAY command and find out which phase is taking longet time.

or, for load u can drop the indexes and try loading. you can then rebuild indexes later.

anamika

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

Hi,

1.You can use SORTKEYS option for the Load utility if it is V.7
V8 sort keys are the default.SORTKEYS eliminates the SYSUT1 and SORTOUT temporary datasets for sorting the Key/RID pairs....

2.You can also PREFORMAT the pages in the Load utility.SInce it is an high inserting process of 1 million records Preformating the tablespace improves Perfromance
EX: LOAD PREFORMAT....

3.If the table space your loading and unloading is an Partioned Table Space try to load and Unload Different partition in an singel Job.(Parallelism)

Please let me know if you have any questions,

Thanks,
Ravi...

cmbb · New User Joined: 01 Feb 2006 Posts: 4

I'm going to try. Does anyone knows if parameters like bufferpools and looks as any influence in the performance?

Thanks

bestinj · New User Joined: 02 Mar 2007 Posts: 2 Location: Chennai

To Optimize the time taken for Execution of Platinum Load Jobs for partitioned table Spaces. Use the below parameter :==>

LOCKPART Indicator specifying whether selective partition locking is used.
Valid values are as follows:
Y - LOCKPART YES - lock only the Loaded partition
blank - LOCKPART NO (or not partitioned) - lock the Entire Table

i.e., It will not lock the Entire Table while loading(only the Loaded partition is locked) and Parallel Loading of all the partitions simultaneusly can be done.

plkanth_526 · New User Joined: 01 Jun 2008 Posts: 7 Location: Bangalore

Load you are performing is doing index build or update.Default is index build which will build entire index.

Try using index update option so that load will build only the records loaded but not entire table.

Index update is for load resume jobs.