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

RDBMS from IBM and IBM's Hierarchical DBMS and Transaction Manager (IMS DC).
Post Reply
Natvar Singh
New Member
Posts: 6
Joined: Thu Jul 31, 2014 10:12 am

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

Post by 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
Global Moderator
Global Moderator
Posts: 588
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars

Re: What is the difference between insert and load a DB2-tab

Post by 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 :)
zprogrammer
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: What is the difference between insert and load a DB2-tab

Post by 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? :D
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
zprogrammer
Global Moderator
Global Moderator
Posts: 588
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars

Re: What is the difference between insert and load a DB2-tab

Post by zprogrammer »

Also adding on to what Anuj has said : LOAD REPLACE comes along with a reorganisation of data
zprogrammer
Vanshika
New Member
Posts: 8
Joined: Mon Jun 30, 2014 12:21 pm

Re: What is the difference between insert and load a DB2-tab

Post by Vanshika »

Anuj Dhawan wrote: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? :D
He he :mrgreen:
RolfD
New Member
Posts: 7
Joined: Thu Apr 02, 2015 6:36 pm

Re: What is the difference between insert and load a DB2-tab

Post by 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
New Member
Posts: 6
Joined: Thu Jul 31, 2014 10:12 am

Re: What is the difference between insert and load a DB2-tab

Post by Natvar Singh »

Thanks for the replies! :)
William Collins
Global Moderator
Global Moderator
Posts: 490
Joined: Sun Aug 25, 2013 7:24 pm

Re: What is the difference between insert and load a DB2-tab

Post by William Collins »

Hey, everyone can be correct: http://www-03.ibm.com/systems/z/
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: What is the difference between insert and load a DB2-tab

Post by 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!?
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
enrico-sorichetti
Global Moderator
Global Moderator
Posts: 825
Joined: Wed Sep 11, 2013 3:57 pm

Re: What is the difference between insert and load a DB2-tab

Post by 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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort 8-)
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM DB2 and IMS DB/DC”