Hi ,

Is there any way to replace the contents of Table A to Table B without export/import option.

Both Table A and Table B already exists in the database.
Table A contian ( 80 million records) and Table b contains (250 million )
Both Tabel A and Table B has the same structure.
Contents of Table A must be replaced into Table B.


Is there any replace command available in DB2.
This is possible in single shot.

Is there any way this can be done in 24 hrs.

Please help me on this?

Thanks,
Sathish.

What platform are you on?

In DB2 OS/390 I would unload the data, then load replace

But why would you want 2 copies of the same data in 2 different tables

declare c1 cursor for select * from tableA with ur
load from c1 of cursor replace into tableB nonrecoverable
.
80M rows should take minutes - not 24 hours

What platform do you do that on?

Sorry, forgot to ask about your polatform. This solution is for DB2 LUW. Where are you trying to do this?

What ever, that's pretty cool though

Can you do that on os/390 z/os?

I don't think you can do it in DB2 z/OS (looking at v7 books). But you can use this method to copy tables from DB2 z/OS and many other platforms to DB2 LUW by using federated references to source tables on those platforms.

Thanks for your response,

Platform is DB2 z/OS ..

May i Knw why we cant do that in DB2 z/OS ..

Maybe hours, not minutes.

There is no IMPORT command (utility) on DB2 for z/OS. DB2 for z/OS and DB2 for LUW are different products.

If you do an UNLOAD and then a LOAD with LOG NO NOCOPYPEND option on DB2 z/OS, it should be able to be done within 24 hours on most systems. You can run the UNLOAD utility against the live table, or against an image copy of the tablespace.

Yes - this may take hours or days on Windows. Or in Oracle.

This can be done using LOAD REPLACE utility from a cursor...
This method is called DB2 croos loader function.....
If you wneed I can give the syntax.........

It just hit me. If both tables are the same. Then it can be literaly be done in 10 seconds or less. RENAME TABLE command

Yes, you might have to rebuild views and such, but as long as you have no RI it is not a big deal and should be faster then any form of unload/reload. Even with RI in place it should still be faster to find and rebuild them.

RENAME TABLE has no influence whatsoever on indexes or RIs so you don't need to rebuild them.

That is not correct. Look up SQL Ref2 Rules section for the rename.