08-26-04, 20:40 #1Registered User
- Join Date
- Sep 2003
- Sacramento, CA
Unanswered: Copying Database from Linux to Windows Server
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?
08-27-04, 02:18 #2Registered User
- Join Date
- Jan 2003
08-27-04, 02:53 #3Registered User
- Join Date
- Jun 2004
I guess this document helps.
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.
i) All the table definition DDL statements are stored in a single file.
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.
08-28-04, 01:47 #4Registered User
- Join Date
- Jan 2004
- Tallahassee, FL, USA
move DB from different OS use db2look , runstats, export & load
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.logLekharaju Ennam
Certified Oracle8i & DB UDB DBA
Florida A&M University