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!

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

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.

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!

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!!

Yes.

[the stupid forum engine does not like short answers]

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!

Look up error code sql1477n.

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?

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!!

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.

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.

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?