Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    20

    Unanswered: moving data between tables in different databases

    I have a windows server with an instance of DB2. I have a database holding data in tables in non normalised form.

    I need to copy and insert data from these tables to a database with tables in a normalised form.

    Some tables will copy one for one. Can i use the select/insert query form to load these tables.

    Some tables will require some manipulation, joining reference tables to create data in the new tables.

    can this be done between tables in different databases on the same instance of DB2.

    My problem as i see it is - when you sign on in windows you need to access the database using a connection spcefic to the database.

    Is it possible to connect on the same instance to 2 different databases using the same sign on.

    or

    do i need to setup a federated database environment to join the databases together.

    any answer or suggestion gratefully appreciated

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I can think of a few options :
    a) export from the source (using the target's table defn) and load into the target

    b) define federation on the target to point to the source database and use Load from cursor.

    c) Backup the source db and restore. Define target tables in it, 'load from cursor' the data to the target tables and drop the source tables.

    d) ....

    e) ....

    Which one you adopt, depends on a number of factors - is the source heavily used, is the target currently in use, is it one-off or a regular one, is this incremental or full etc etc

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    you did not mention version of DB2 you are using.

    you might want to read a document called Data Movement Utilities Guide for your DB2 version. It has all the info there (db2move, etc.)

    from my experience i'd prefer option c) suggested by Sathyaram
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I did not suggest db2move because the source and the target table structures are not the same

    Quote Originally Posted by MarkhamDBA View Post
    you did not mention version of DB2 you are using.

    you might want to read a document called Data Movement Utilities Guide for your DB2 version. It has all the info there (db2move, etc.)

    from my experience i'd prefer option c) suggested by Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2009
    Posts
    20
    I will be using Db2 9.5 on windows server 2005.

    My question is - can i join two tables in 2 different databases on the same instance of DB2 without having to setup Federation on the server.

    Can I insert data from one table in 1 database to another table in another database using SQL without using Federation on the same instance of DB2.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Two tables from different databases can be accessed using Federation only.

    The federation can be setup on one of these databases to access the other one
    or
    a separate database can be used to setup federation to access both the databases.





    Quote Originally Posted by Howardw View Post
    I will be using Db2 9.5 on windows server 2005.

    My question is - can i join two tables in 2 different databases on the same instance of DB2 without having to setup Federation on the server.

    Can I insert data from one table in 1 database to another table in another database using SQL without using Federation on the same instance of DB2.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Nov 2009
    Posts
    20
    thanks Sathyaram

    this answer is what i needed to know.

    I can now plan what I need to do and how much work will be involved.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Howardw View Post
    I will be using Db2 9.5 on windows server 2005.
    Did Bill cook that specially for you? We mortals have to choose between server 2003 and server 2008

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by dr_te_z View Post
    Did Bill cook that specially for you? We mortals have to choose between server 2003 and server 2008
    Nice joke ! keep it up

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Howardw View Post
    Is it possible to connect on the same instance to 2 different databases using the same sign on.

    or

    do i need to setup a federated database environment to join the databases together.

    any answer or suggestion gratefully appreciated
    If the issue is simply transfer/compare data, simplest is export and Load.
    Also I used java program to connect to two DBs for comparing same structure table between two DBs.

    DBFinder

Posting Permissions

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