Insert v/s Load

vyombagda

We have Batch program which insert around 1 million rows in a partitioned table.

A suggestion has been given to write the records to a file and then load them to the table using IBMLOAD or BMC LOAD.

What is general guideline in such cases where application is processing large volume of data and inserting in the DB2 table ?
Mass Inserts with Restart Capability or Loads using file compromising on Restartability ?

How will the decision get affected if the same database is concurrently being accessed by Business Objects as well ?

Jul 27 '07 #1

FritzLeblanc

I think the answer depends a bit on how large the million rows insert activity compares to the number of rows in the table already. If it's a relatively small percentage, and you have adequate freespace, the nthe inserts should perform acceptably. Be careful to commit on a regular basis, though.
It might also depend on whether the rows would normally be spread throughout the table or added at the end. A load utility would add the rows at the end, possibly compromising clustering. If you do decide to use a load utility, you should also backup the table as soon as possible.
If you concern leans toward concurrency, I would opt for inserting the rows with an appropriate commit frequency. After all, a million rows is not really a large number any more.
Good luck.

Jul 27 '07 #2

docdiesel

Load has its pro's and contra's, but when filling an empty table or replacing its whole content I prefer the load method. If you have to add data to an existing table, you could load the data into a 2nd table of same construct in a frist step, and after that append it to the destination table loading it by cursor.

Nevertheless, you shouldn't forget to do a backup.

Aug 15 '07 #3