CODEWITHSUNDEEP
db2
Krishna
Krishna

Reputation: 49

Transferring Data From one DB2 database to other DB2 database

I have DB2 database installed on one server which contains all the data. Now I am trying to transfer all that data to another DB2 database which is on my local host. I have tried the following steps for this procedure.

  1. On the server- 

                  db2 move DB_NAME export

  2. Copied the .lst and all files into my local host.

  3. On my local host - 

                  db2 create database DB_NAME
              db2 -tvf DB_NAME.DDL
              db2 move DB_NAME import

    I have succeeded in moving the tables to my local host.

But somehow data was missing in my local host. I am using DB2 version 10.5.1. Any one please tell me the correct procedure for transferring the data.

Thanks in advance.

Upvotes: 0

Views: 12667

Answers (3)

Zeeshan Ahmed
Zeeshan Ahmed

Reputation: 15

For Single Table to Move

db2move dbname export -tn tablename -u username -p password

For all tables in DB to Move

db2move dbname export -sn schema -u username -p password

tar , gzip and sftp to target directory

unzip , untar

for loading data to another database

db2move database load -io replace

Upvotes: 0

Seb.B.
Seb.B.

Reputation: 151

from your initial post, I missed an important point. You are trying to move the data from version 8.x to version 10.5. I've done this in the past and the correct procedure to move the data is to do a migration of the data. This is also what IBM recommends.

To achieve the migration of the database data, you will have to do this:

  1. Generate DDL for the database (use db2look utility)

  2. If you have sequences (export sequence current values, use select on syscat.sequnces)

  3. On version 8.x Server export your data (use db2 export command. here I use allways IXF format)

  4. Create new database on the version 10.5 server (I will also look on new Features or Settings and try to improve the database configuration ... etc. tablespaces, bufferpool can now organize themself automatically)

  5. Copy and import your data on the new database (use db2 load command. use reorg / runstats after load to reorganize your data and update table statistics)

  6. This step is maybe optional, depends on your database model. If you have referenced keys, the you'll have to do a integrity check (SET INTEGRITY FOR <<>> IMMEDIATE CHECKED)

  7. Update sequences values

I've wrote a script for all these steps. This is not quite easy, but it's also the only way when migrating to another OS.

Upvotes: 1

mavimassi
mavimassi

Reputation: 31

There are few different ways to move data in db2, here there is a good short overview of the different utilities:

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.dm.doc/doc/r0024482.html

If using db2move, should generate the DDL with db2look, please give some more info of what kind of data was missing.

If moving to a different server like in this case, then should do a backup + redirect restore operation, it is explained here in the Info Center (link may be slow to load) with some good examples:

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0006249.html

Upvotes: 0

Related Questions