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
 

 

DB2 Load and Unload performance

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

New User


Joined: 01 Feb 2006
Posts: 4

PostPosted: Tue Jul 18, 2006 4:14 am    Post subject: DB2 Load and Unload performance
Reply with quote

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.
Back to top
View user's profile Send private message

anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Tue Jul 18, 2006 9:49 pm    Post subject:
Reply with quote

try out with Sharelevel reference. enforce constraints no and log no option.
Back to top
View user's profile Send private message
cmbb

New User


Joined: 01 Feb 2006
Posts: 4

PostPosted: Tue Jul 18, 2006 9:59 pm    Post subject:
Reply with quote

I have tried with log no and nocopypend option.
Is there any DB2 parameter that i must try?
Thanks
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Wed Jul 19, 2006 2:07 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Jul 19, 2006 5:07 pm    Post subject: Re: DB2 Load and Unload performance
Reply with quote

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...
Back to top
View user's profile Send private message
cmbb

New User


Joined: 01 Feb 2006
Posts: 4

PostPosted: Wed Jul 19, 2006 7:22 pm    Post subject:
Reply with quote

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

Thanks
Back to top
View user's profile Send private message
bestinj

New User


Joined: 02 Mar 2007
Posts: 2
Location: Chennai

PostPosted: Wed Jun 25, 2008 12:56 pm    Post subject: Optimize time taken Load Jobs for partitioned table Spaces
Reply with quote

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.
Back to top
View user's profile Send private message
plkanth_526

New User


Joined: 01 Jun 2008
Posts: 7
Location: Bangalore

PostPosted: Thu Jun 26, 2008 2:41 pm    Post subject: Reply to: DB2 Load and Unload performance
Reply with quote

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.
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 Can DFSPARM override SORTKEYS paramet... ashek15 DB2 2 Tue Jan 31, 2017 11:36 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm


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