What is the difference between insert and load a DB2-table?

[Natvar Singh]

Hi,

What is the difference between loading a table and inserting values in to a table? Are they not same? If not, which one is better?

[zprogrammer]

Let me give a brief background

Structured Query Language ie. Sql is "kind of" or "speacial purpose" programming language to process or manage data in RDBMS.DB2 is a type of RDBMS.

Structured Query Language has three sub categories
DDL (Data Definition Language) - Which has statements like CREATE,DROP,ALTER...
DML (Data Manipulation Language) - Which has statements like INSERT,DELETE,SELECT,UPDATE,MERGE
DCL (Data Control Language) - Which has statements like GRANT,REVOKE

So To answer your question INSERT is a DML statement which can be executed via SPUFI or Using Application program

Where as LOAD is a Utility

Check here for LOAD utility

Hence they are not same.

Which one is better ? - It varies from case to case of requirement though for Mass data LOAD utility is usually preferrable but having said that INSERT also an option these days MULTI Row insert

Also check the admin guide here
for chapter 2 Loading data into DB2 tables

If you have any more questions on this regard feel free to ask here

[Anuj Dhawan]

Just my .02$...

For a single row or couple of rows (rowset) - insert and load will do the same thing actually at the 'face value of it'. But there are differences.

INSERT is always a programmatic approach while LOAD is a stand alone entity and here lies in the first difference, among many others. As INSERT is a programming approach - you can work on the 'data' before you insert it in to the tables. However, when large amounts of data must be inserted, the LOAD utility has advantages over application program INSERT statements. LOAD has significant advantages for improved performance and in other areas as well. The LOAD utility communicates directly with the DM component of DB2. While insert processing must go through the application program interface and the relational data system (RDS) before going to DM. Also, LOAD does not have to check for broken pages as do INSERT and DELETE statements.

LOAD leaves the specified percent free for data and index pages, leaving room for future updates. INSERT does not.


P.S.: This is an interesting question, actually. It's like asking what's the difference between petrol and diesel - they just both run the car and as the end result is same - that car is running - what's the difference between the two?

[zprogrammer]

Also adding on to what Anuj has said : LOAD REPLACE comes along with a reorganisation of data

[Vanshika]

P.S.: This is an interesting question, actually. It's like asking what's the difference between petrol and diesel - they just both run the car and as the end result is same - that car is running - what's the difference between the two?

He he

[RolfD]

Perhaps a little late: In general, utilities are tablespace-based, except Load which is table-based. Utilities usually perform better, but have lower functionality. Over the years Load has get some options to run more like Insert (shrlevel change) and Insert (or SQL) has got Rowset-processing, what is more like a utility. But SQL has always to go thru RDS, which is a big difference.

[Natvar Singh]

Thanks for the replies!

[William Collins]

Hey, everyone can be correct: http://www-03.ibm.com/systems/z/

[Anuj Dhawan]

That's an interesting link but I don't seem to understand the relevance of it for this topic - possibly I missed something!?

[enrico-sorichetti]

...I don't seem to understand the relevance of it for this topic ...

it probably belongs here
http://www.zmainframes.com/viewtopic.php?f=35&t=1320