Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Question Unanswered: db2 database dumpping

    Hi friends,
    I am vijay. I am new to the db2. I have a short requirement in db2 databse. I just need to migrate the database from db2 to ms sql.

    here I have problem dump the data along with the structure of the database. I have tried with db2look commond. but I can able to get the structure of the database like ddl commonds of the database.

    So please help me out to sort out this problem.
    Thanks in advance.
    regards
    vijay

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use db2look to get the DDL of the database. Use DB2 export to copy the data out to flat files. Use whatever SQL Server uses to import the data into tables created from the script that db2look produced.

    Andy

  3. #3
    Join Date
    Dec 2006
    Posts
    4
    can u explain in detail? could you please provide the Code synopsis if you don't mind?
    Thans in advance
    vijay

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I cannot give you exactly what you need because I do not know you system. In general this is what you would do:

    ON DB2:
    db2look -d MyDB -e -o scriptfile.ddl -l -x
    (look in the "Command Reference" for all the options you might be interested in using)

    Then for each table:

    export to MySchema_MyTable1.csv of del select * from MySchema.MyTable1
    export to MySchema_MyTable2.csv of del select * from MySchema.MyTable2
    export to MySchema_MyTable3.csv of del select * from MySchema.MyTable3
    export to MySchema_MyTable4.csv of del select * from MySchema.MyTable4
    ...


    On SQL Server:
    Edit the file (scriptfile.ddl) so that it conforms to SQL Server standards. I do not know if this is necessary or what specifically what to change.

    Run the scriptfile.ddl to create the objects

    Import the data from step 2 into SQL Server (Again I do not know SQL Server so I do not know how this is accomplished).

    Andy

  5. #5
    Join Date
    Dec 2006
    Posts
    4
    I think, info given is more than sufficient for me.Thank you very much. But you have hinted that about the environment on which i am running my server.Ms-sql server2005 running on windowsXP. And my db2 is on linux.

    i think this ifo will give better idea to give a better solution for me.
    regards & thanks.
    vijay

  6. #6
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78
    Just a friendly reminder, after creating the DDL statements using DB2LOOK you'll have to make some adjustments to the script to conform to MS SQLServer syntaxes.

    Mostly, what you'll have to look for are different data types declaration and table storage options.

    And another information, just appending to Andy's solution: To import the extracted data into MS SQL you'll have to use the DTS (correct me if I'm wrong). Once I heard that DTS was able to connect to data sources and extract data directly from them using ODBC. Never tried it, but I think it's worth looking for.

    HTH,
    Fernando

  7. #7
    Join Date
    Dec 2006
    Posts
    4

    Thumbs up Thanks andy..

    Hi andy, Thank you for replay. It's working fine. My client is very happy with my work.
    regards & thanks.
    vijay

Posting Permissions

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