import db2 data into sql server

Question

Post reply

import db2 data into sql server

sahana

SSCrazy

Points: 2530
More actions
January 7, 2005 at 9:56 am

#61514

Hi I have a database in DB2 and i would like to import this data into sql server 2000. How can i do that?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

There are 2 scenarious for this.

DB2 in Mainframes

1. Create a COBOL program to out the data to a text or dat file

2. BCP in the data to SQL Server 2000

DB2 in Windows

1. Pretty straight forward, use the Data Import Wizard to zap across the data

Thanks,

Ganesh

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Export the data, (with headers) to a text file. I would recommend using something like a pipe | as the delimiter (if text is in the file, comma's might be there and rarely is a pipe used).

You can use the DTS to pump the data into SQL Server table. You may need to move that data into another table as SQL Server does not always designate the best datatype selections.

Since it is your original data, you should be able to determine the proper datatypes.

I wasn't born stupid - I had to study.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 3

I would like to share few things

The data file coming out from a db2 database from a mainframe environment

1. There is no concept NULL, it is treated a s hex code there. So the the NULL has to be handled in such a way when you create the data file. The data file you get will have blank in it.

2. Don't associate the datetimestamp with the time stamp column with sql server. In DB2, there are date,time,datetime column and everything talks about the datetime.

The format of the datetime has to be formatted to SQLServer datetime column. Please have a look at the decimal places there

Altogether, when you do a data migration from db2 to sqlserver you have to be careful designing the table for the datatype as Farell said.

Thanks,

Ganesh

sahana SSCrazy Points: 2530 More actions · Answer 4

what should be the data source when we import data from db2 using dts.

thanks.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 5

If you have a db2 instance on your box, it should have a Microsoft OLE DB Provider for DB2.

Else follow this link

http://www-306.ibm.com/software/data/db2/udb/ad/v7/oledb.html

Thanks,

Ganesh