Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    54

    Unanswered: 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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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

  4. #4
    Join Date
    Jul 2011
    Posts
    54
    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.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  6. #6
    Join Date
    Jul 2011
    Posts
    54
    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  8. #8
    Join Date
    Jul 2011
    Posts
    54

    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.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •