Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2007
    Posts
    38

    Unanswered: Migrate data from Db2 V8.2 to Oracle 9i

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Maybe asking in an Oracle group would be a good idea? We could cover the unload/extraction part here...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2003
    Posts
    237
    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.
    mota

  4. #4
    Join Date
    Nov 2007
    Posts
    38
    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.

  5. #5
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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.

  6. #6
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A
    Anyone who migrates from DB2 to Oracle will be reincarnated as an ant.
    Shouldn't that be posted in the Oracle forum?

    :-)
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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?

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Southeast Asia? I guess being an ant there is better.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Oct 2007
    Posts
    6
    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

Posting Permissions

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