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

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

can u explain in detail? could you please provide the Code synopsis if you don't mind?
Thans in advance
vijay

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

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

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

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