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

RDBMS from IBM.
Previous topicNext topic

Topic Author
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 » Wed Jan 07, 2015 4:26 pm

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: 605
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars
Zodiac: Libra

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

Post by zprogrammer » Wed Jan 07, 2015 7:26 pm

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: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

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

Post by Anuj Dhawan » Thu Jan 08, 2015 12:23 pm

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: 605
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars
Zodiac: Libra

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

Post by zprogrammer » Thu Jan 08, 2015 1:05 pm

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 » Thu Jan 08, 2015 5:27 pm

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 » Sun Apr 05, 2015 2:48 pm

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.




Topic Author
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 » Mon Apr 06, 2015 12:43 pm

Thanks for the replies! :)




William Collins
Global Moderator
Global Moderator
Posts: 499
Joined: Sun Aug 25, 2013 7:24 pm

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

Post by William Collins » Mon Apr 06, 2015 8:03 pm

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



User avatar

Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

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

Post by Anuj Dhawan » Mon Apr 06, 2015 8:43 pm

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: 892
Joined: Wed Sep 11, 2013 3:57 pm

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

Post by enrico-sorichetti » Fri Aug 05, 2016 2:54 pm

...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-)

Previous topicNext topic

Return to “IBM DB2.”