Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Import vs Load

  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Import vs Load

    Hi,

    I have a table with around 779,156,890 rows and want to move data from this table to another table. I was wondering if anyone has an idea of the speed difference between import and load. I understand there are drawbacks to both methods -- the COPY YES and NONRECOVERABLE options with the load command have their own cons.. although I think COPY YES seems to be a safer bet, do you agree? Is it worth using the load command for what I need to do from a speed perspective or should I just use the import for chunks of data at a time. Also, thought I would add that I can't afford to lose any of the data from the original table. Thanks..any thoughts are appreciated!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If moving to a new table, use LOAD NONRECOVERABLE, otherwise use LOAD COPY YES. If the tables are in the same database, use LOAD from a cursor (Moving data using the CURSOR file type - IBM DB2 9.7 for Linux, UNIX, and Windows).

  3. #3
    Join Date
    Aug 2011
    Posts
    33
    I think LOAD FROM CURSOR is a better option.You are just laying a bridge between source and target table ,eliminating the export step.However,load from cursor is CPU intensive.So keep an eye on CPU usage.

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    Thanks, forgot to mention that we have db2 udb 8.2 fp 11.. will load from cursor still work for that? and yes, tables are in the same database.


    Also, I should add that this is one of most useful forums I have ever used.. lots of smart ppl here!
    Last edited by db2user24; 08-11-11 at 17:05.

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    If moving to a new table, use LOAD NONRECOVERABLE, otherwise use LOAD COPY YES. If the tables are in the same database, use LOAD from a cursor (Moving data using the CURSOR file type - IBM DB2 9.7 for Linux, UNIX, and Windows).

    Thank you, did you say to use NONRECOVERABLE since the data is there in the original table anyways and thus, there won't really be any loss of data if the copy doesn't work as expected? Or for some other reason? Thanks!!

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    did you say to use NONRECOVERABLE since the data is there in the original table anyways and thus, there won't really be any loss of data if the copy doesn't work as expected?
    Yes.

    [the stupid forum engine does not like short answers]

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    If this is your production env, I'd take a backup after nonrecoverable load (or use copy yes) in case you need to restore from a previous image and rollforward past this load operation.

  8. #8
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by db2girl View Post
    If this is your production env, I'd take a backup after nonrecoverable load (or use copy yes) in case you need to restore from a previous image and rollforward past this load operation.

    I'm not sure I understand.. can you please explain? btw, in what way does 'copy yes' affect performance... do queries take longer? thanks!

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Look up error code sql1477n.

  10. #10
    Join Date
    Nov 2007
    Posts
    265
    Ok, I think I understand... the backup image taken after the nonrecoverable load is really for a restore to work correctly on our backup server.. correct? Basically, if I don't use a backup image after the load is done, the restore on our backup server won't let me access the new table. Please correct me if I'm wrong, thanks!

    Typically, on our backup server.. we restore a full backup image ( taken once a month) and the latest incremental image ( automatic). Hypothetically, if I use the full backup image before the nonrecoverable load and an incremental taken after the nonrecoverable load, it won't let me access the tablespace.. right?

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    If moving to a new table, use LOAD NONRECOVERABLE, otherwise use LOAD COPY YES. If the tables are in the same database, use LOAD from a cursor (Moving data using the CURSOR file type - IBM DB2 9.7 for Linux, UNIX, and Windows).

    Another question, I still need the table I'm moving data from but I only need the current year's worth of data in it which means deleting millions of records.. don't think I want to drop and recreate it since I have procedures / functions that reference it and don't really want to create all of them again.. is there a simple way for me to actually 'move' data ( not copy) of the table or delete tons and tons of records? thanks!!

  12. #12
    Join Date
    Oct 2007
    Posts
    246
    ok for moving current year's data create a temp table same structure as ur source table using load cursor load current years data in that once done rename the temp table to your source table, and as you have already moved other data in the new table you can drop of keep the table but still i not sure ur procedures/functions may be inactive if u do this way simple to just recreate tht too.

    regds
    Paul

  13. #13
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by Mathew_paul View Post
    ok for moving current year's data create a temp table same structure as ur source table using load cursor load current years data in that once done rename the temp table to your source table, and as you have already moved other data in the new table you can drop of keep the table but still i not sure ur procedures/functions may be inactive if u do this way simple to just recreate tht too.

    regds
    Paul
    Thanks for the reply. I guess it brings up a good question whether renaming tables requires dropping / recreating of functions and stored procedures.

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    a good question whether renaming tables requires dropping / recreating of functions and stored procedures.
    No. Routines will be revalidated when called. However, you will have to re-create constraints on the table.

    As an alternative, you could extract (export or load into a temporary table) the records you want to keep, then load replace them back into the original table.

  15. #15
    Join Date
    Nov 2007
    Posts
    265
    thanks, honestly I would prefer not to rename or drop any tables... I like the idea of exporting into a temp table and then loading back into the original table... does load replace actually 'replace' all of the data in the original table with the data from the temp?

Posting Permissions

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