If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Import vs bulk insert

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 11-16-11, 05:09
db2champ db2champ is offline
Registered User
 
Join Date: Jul 2011
Posts: 46
Import vs bulk insert

I am inserting data in DB2 database table from CSV file through IMPORT command but it is taking long time, Is BULK INSERT is faster then IMPORT command .
Please suggest some solution to fast insert from CSV to table.
Reply With Quote
  #2 (permalink)  
Old 11-16-11, 06:54
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
LOAD is much faster than IMPORT. However, the tablespace will be left in backup pending mode unless you use NONRECOVERABLE or COPY YES options. You may also need to do a SET INTEGRITY after the LOAD.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old 11-16-11, 07:27
stolze stolze is offline
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
IMPORT is using the SQL statement INSERT under the covers. That's why you'll have triggers being fired, logging being applied etc. LOAD bypasses those steps. Now, it depends on what you mean with "bulk insert". If it is just regular INSERT SQL statements or LOAD?
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #4 (permalink)  
Old 11-17-11, 01:03
db2champ db2champ is offline
Registered User
 
Join Date: Jul 2011
Posts: 46
I am ok with LOAD but I have still confusion on that.
(1) I know about LOAD that i will no log the transaction but do we have to rebuild/reorag after load operation.
(2) What is backup pending mode which is said by Mr. Feldman?
(3) what is the mean of SET INTEGRITY after the LOAD as said by Mr. Feldman.

I am using db2 9.5 Version.
Reply With Quote
  #5 (permalink)  
Old 11-17-11, 01:26
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
Quote:
Originally Posted by db2champ View Post
I am ok with LOAD but I have still confusion on that.
(1) I know about LOAD that i will no log the transaction but do we have to rebuild/reorag after load operation.
(2) What is backup pending mode which is said by Mr. Feldman?
(3) what is the mean of SET INTEGRITY after the LOAD as said by Mr. Feldman.

I am using db2 9.5 Version.
(1). No.
(2). It means that if you do not use NONRECOVERABLE or COPY YES options with the LOAD, you must do a backup (of at least the tablespace) after the LOAD, otherwise the table will not be accessable.
(3). It means that constraints such as foreign keys may need to be verified after the LOAD (DB2 does not check these contraints during the LOAD) so that the table can be made accessable. Primary Keys and Unique Indexes are checked during the LOAD, so no SET INTEGRITY is required to verify these.

Please refer to the DB2 documentation for more informaiton on LOAD, BACKUP, and SET INTEGRITY.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #6 (permalink)  
Old 11-17-11, 01:52
db2champ db2champ is offline
Registered User
 
Join Date: Jul 2011
Posts: 46
As per mine requirement I do not want to log the Load operation and even I am not worried of load data in case of loss so I do not want to set copy=yes other wise on LOAD it will create one copy of load data for recovery.
Reply With Quote
  #7 (permalink)  
Old 11-17-11, 08:23
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
Quote:
Originally Posted by db2champ View Post
As per mine requirement I do not want to log the Load operation and even I am not worried of load data in case of loss so I do not want to set copy=yes other wise on LOAD it will create one copy of load data for recovery.
As stated above, use NONRECOVERABLE. Please refere to Command Reference manual for LOAD command to understand NONRECOVERABLE option.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #8 (permalink)  
Old 11-23-11, 02:15
db2champ db2champ is offline
Registered User
 
Join Date: Jul 2011
Posts: 46
Forward Recovery

Quote:
Originally Posted by Marcus_A View Post
As stated above, use NONRECOVERABLE. Please refere to Command Reference manual for LOAD command to understand NONRECOVERABLE option.
I am not setting NONRECOVERABLE option on LOAD operation so table space should be in backup pending state but this is not truth I am able to do insert process after load without any backup.

I checked load log file it is populating given below content--

SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

Could you please give me some information about forward recovery.
Reply With Quote
  #9 (permalink)  
Old 11-23-11, 05:32
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
Roll forward recovery requires that you use archive logging and not the default circular logging. If you have a data warehouse where all data is loaded in batch mode, you may not need roll forward recovery, and if you do LOAD's with NONRECOVERABLE you cannot use roll forward recovery anyway (if you do, any tables loaded with NONRECOERABLE will be marked bad and will need to be dropped).
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On