I have two DB2 Servers. One is a Windows 2003 server and the other is p-series running RedHat Enterprise Linux 3. Both servers are running DB2 UDB v8.1 fixpak 6 (windows 2003) fixpak 7 (linux).

I would like to copy the entire database (tables, Stored Procedures, etc) from the Windows 2003 server to the Linux server.

How do I go about doing this?

Thanks,
David

Hi,

Look at the following thread
Useful DB2 Stuff. Search for the topic "Can the database on one platform be restored on another platform?"

Hope this helps,
Grofaty

I guess this document helps.


DB2LOOK:

This process involves the following steps:

Migration of all the DDL statements (without data)

1) Running command DB2LOOK –d <dbname> -e –x –o <filename>, on the sources machine extracts the table definitions of the database and stores it to the specified <filename>.
2) The file when created by the process above can be copied to the target machine.
3) After copying the file, the file just needs to be run as a simple .DB2 file.
4) This would run all the DDL statements and create the tables in the target database.

Populating the data in the tables:

1) Running command EXPORT TO <filename.csv> OF DEL MODIFIED BY COLDEL SELECT * FROM <tablename>, exports all the data in comma separated values and stores it in a flat file.
2) The above command has to be run for each and every table.
3) After that all the .CSV files needs to be copied to the target system.
4) At the target machine, running command IMPORT FROM <filename.csv> OF DEL MODIFIED BY COLDEL SELECT * FROM <tablename>, populates the data in the table.
5) All the .CSV files need to be in the same fashion to populate the data.


Advantages:
i) All the table definition DDL statements are stored in a single file.

Disadvantages:
i) For populating the data the table-name should be known as for every table an export statement needs to be fired.
ii) The application objects (procedures, functions etc) are not converted.
iii) There may be lots of file to transfer manually since every table consumes one .CSV file.

For migrating procedures and functions etc. in both the above cases

1) If the source version is 7, the use of Stored Procedure Converter extracts the DDL and gets all the CREATE PROCEDURE statements in one file.
2) If the source version is 8, instead of using Stored Procedure Converter, the same work can be done through the use of Development Center.
3) After that the create procedure statements can be perfectly run on the target machines.

move DB from different OS use db2look , runstats, export & load

from source
get DDL ( for Tablespaces, table index,views etc ..)
db2look -d dbname -l -e -x -o DBname

if don't want to create tablespaces
db2look -d dbname -e -x -o DBname

connect to sourceDB

run RUNSTATS , to export only data existing tables

db2 -x " select ' runstats on table '||rtrim(creator)||'.'||ltrim(name)||' with distribution and detailed indexes all shrlevel change ;' from sysibm.systables where creator = schemaowner and type = 'T' " > runstats.sql

db2 -tvf runstats.sql -z runstats.log

then export only data exist table

db2 -x " select ' export to '||rtrim(name)||'.ixf of ixf select * from '||rtrim(creator)||'.'||ltrim(name)||';' from sysibm.systables where creator = schemaowner and type = 'T' and card > 0 " > export.sql

db2 -x " select ' load from '||rtrim(name)||'.ixf of ixf replace into '||rtrim(creator)||'.'||ltrim(name) ||';' from sysibm.systables where creator = schemaowner and type = 'T' and card > 0 " > load.sql

db2 -tvf export.sql -z export.log

ftp DBname.sql , load.sql ,*.ixf ( export files) to target .

if DB exist , BPs, tablespaces already created, schem owner is same, if schem onwer is different , change schema owner, then

db2 connect to targetDB
db2 -tvf DBname.sql -z DBname.log
db2 -tvf load.sql -z load.log