Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Sacramento, CA

    Cool 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?


  2. #2
    Join Date
    Jan 2003

    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,

  3. #3
    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.

  4. #4
    Join Date
    Jan 2004
    Tallahassee, FL, USA
    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
    Lekharaju Ennam
    Certified Oracle8i & DB UDB DBA
    Florida A&M University

Posting Permissions

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