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

Thread: Loading Data.

  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: Loading Data.

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2008
    Posts
    51
    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
    db2topgun.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by SuperKuper
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2008
    Posts
    51
    Sorry, forgot to ask about your polatform. This solution is for DB2 LUW. Where are you trying to do this?
    db2topgun.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by SuperKuper
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2008
    Posts
    51
    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.
    db2topgun.com

  8. #8
    Join Date
    Apr 2008
    Posts
    4
    Thanks for your response,

    Platform is DB2 z/OS ..

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

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by SuperKuper
    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
    Maybe hours, not minutes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sathish_ix
    Thanks for your response,

    Platform is DB2 z/OS ..

    May i Knw why we cant do that in DB2 z/OS ..
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Apr 2008
    Posts
    51
    Quote Originally Posted by Marcus_A
    Maybe hours, not minutes.
    Yes - this may take hours or days on Windows. Or in Oracle.
    db2topgun.com

  12. #12
    Join Date
    Nov 2007
    Posts
    6
    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.........

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    RENAME TABLE has no influence whatsoever on indexes or RIs so you don't need to rebuild them.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by stolze
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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