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

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

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

I did not suggest db2move because the source and the target table structures are not the same

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.

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.

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.

Did Bill cook that specially for you? We mortals have to choose between server 2003 and server 2008

Nice joke ! keep it up

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