Hi all,

We are migrating data from DB2 V8.2 to Oracle 9i. Can someone guide me hot to do this? I need to migrate both the schema and the data as well, and this is a one time migration.

Thank you all in advance.

Maybe asking in an Oracle group would be a good idea? We could cover the unload/extraction part here...

You can use db2look to get the table,index,view etc. definitions;after editing they can be used to create tables etc.; use export in del format to get data in csv type files, which can be fed to oracle LOAD.

Thank you.... Any idea on how to load the csv files into Oracle, I mean I know we can use SQL *Loader but any idea whether does it changes the data types, their length.

Which DB2 product family? I was on a project converting from DB2 for z/OS to Oracle 9 a few years ago. Two things gave us headaches. Embedded new line characters in the source CHAR and VARCHAR fields, and VARCHAR column values with the max number of characters for the target VARCHAR column. We eventually utilized a vendor tool that opened a connection to the source and target. The tool performed a target INSERT for each source FETCH to get around the embedded new line characters. If my memory is correct, Oracle utilizes a LONG VARCHAR type. That's the one we were having a problem with when the DB2 source column had data with the max length. The Oracle DB kept complaining that the data exceeded the length of the target column, even though it was equal to the max length. We wound up truncating the last two bytes of the source data when the length was > max length - 2 to resolve it.

Oh. Also. Oracle is friendly with dates/times. I think the function is TO_DATE. You can use any date format and supply a mask for it.

Anyone who migrates from DB2 to Oracle will be reincarnated as an ant. If you had no choice in the decision and are just following orders, you will be reincarnated as a dog, but you will be born in Southeast Asia.

Shouldn't that be posted in the Oracle forum?

:-)

Ok. So I look forward to my next life in southeast Asia as a dog. I was a contractor and fought the conversion, so could I at least be a big fast mean dog?

Southeast Asia? I guess being an ant there is better.

If you are not opposed to using a third party data tool to do a one-time data migration you should check out the Pervasive DataTools. They have an UpLoader to Oracle that can read data out of DB2 and write data to Oracle. The interface is fairly easy to use, and you can write SQL statements to pull data out of multiple tables. I've used some of their tools and think they are worth checking out.

-mygr8r