Multi-row Insert vs DB2 Load Utility



IBM's flagship relational database management system

Multi-row Insert vs DB2 Load Utility

Postby thisak » Mon Aug 26, 2013 12:33 pm

In my project, I have to design a Job which should read a file, validate and load into DB2 tables. There are 2 different file volumes i.e. one comes with 5 - 10 million records (of LRECL 500 approx.) and other file with 20K to 100,000 records (of LRECL 400 approx.). I have to decide on either having single program which does all validation and load data directly into Table thro Multi-row Insert, Or having a program which prepares load dataset and then thro BMCLOAD load utility inserting into the Table. Can you please suggest me which approach will be best (if possible with data facts)?
thisak
 
Posts: 2
Joined: Mon Aug 26, 2013 12:28 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Multi-row Insert vs DB2 Load Utility

 

Re: Multi-row Insert vs DB2 Load Utility

Postby dick scherrer » Mon Aug 26, 2013 6:51 pm

Hello and welcome to the forum,

Suggest you edit the values in one process and use the utility to load the data after validation.

Individual inserets cost much more than the utility to load the data.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Multi-row Insert vs DB2 Load Utility

Postby thisak » Mon Aug 26, 2013 6:56 pm

How about using Multi-row insert (Cursor) concept.
thisak
 
Posts: 2
Joined: Mon Aug 26, 2013 12:28 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Multi-row Insert vs DB2 Load Utility

Postby dick scherrer » Mon Aug 26, 2013 7:03 pm

Hello,

Still uses a Lot more resources.

Even if multi-row is used, the insert still has to deal with one row (and the associated index entries) at a time.
Which means lots of overhead.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Multi-row Insert vs DB2 Load Utility

Postby prino » Mon Aug 26, 2013 10:02 pm

If you have one program that ensures your data is valid, you can load the data with a utility and tell it not to log anything, which will save, been there done that, vast amounts of time!
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 536
Joined: Wed Mar 11, 2009 12:22 am
Location: Oostende, Belgium
Has thanked: 3 times
Been thanked: 21 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post