Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2002
    Posts
    6

    Question Unanswered: Extracting DB2 data to Oracle

    We are beginning to look at integrating data from an IBM DB2 database running under VM on a mainframe into Oracle applications. Currently, we have Oracle 8i, but will migrate to 9i within the year.

    Does anyone have any experience with extracting data directly from a DB2 database into an Oracle application? If so, what tools are you using?

    Thanks!

  2. #2
    Join Date
    Jun 2002
    Posts
    12
    If you have Universal Database installed on your machine, this will give you an IBM DB2 ODBC Driver.
    You should also install UDB Connect which should enable you to communicate directly with your mainframe.
    You should know the name of your server and the port and the you can configure your mainframe connection.
    Let me know how far you have gone.

  3. #3
    Join Date
    Jun 2002
    Posts
    6

    Post

    Sorry, I guess I should've been more explicit.

    We would like to be able to export full table definitions as well as the data. A comma-delimited file won't work, because much of the data is text. We also have null data and variable character data; the import needs to recognize what the export writes for those fields.

    We'd also like to be able to import it with minimal changes, just changing from DB2 datatypes to Oracle datatypes where the type names are not comparible. Other than that, we just want to import the definitions and the data.

    Eventually, there will be over 150 tables ported over, so it's not a trivial task. If it were only a dozen or so, we'd just edit the stuff.

    We do have the connect installed. We can extract data; we just don't know an easy way to get table definitions plus data into a format that Oracle can deal with, without doing major hand-editing of everything.

    Originally posted by ssonko
    If you have Universal Database installed on your machine, this will give you an IBM DB2 ODBC Driver.
    You should also install UDB Connect which should enable you to communicate directly with your mainframe.
    You should know the name of your server and the port and the you can configure your mainframe connection.
    Let me know how far you have gone.

  4. #4
    Join Date
    Jun 2002
    Posts
    12
    There's a tool called Datapump in Borland C++ that can pump data from any database to any other database.
    This will automatically create the names of the tables as they exist on the previous database into the new database, all the indexes will be recreated as well.

    The other option is to Use Access2000.
    Import all the tables that u wish to migrate into Oracle from DB2 into an empty Access database using the mainframe DB2 Odbc link.

    Once that has been done, then go to your Oracle Home folder, locate a folder called OMWB within which you will find the msaccess_exporter folder. Open up the corresponding version of the access database that u are using.
    Convert the Access database that you imported the DB2 tables into to XML format.
    Then using the Oracle Migration Workbench you will be sorted out.

    Tell me the progress.

  5. #5
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by ssonko
    There's a tool called Datapump in Borland C++ that can pump data from any database to any other database.
    This will automatically create the names of the tables as they exist on the previous database into the new database, all the indexes will be recreated as well.

    The other option is to Use Access2000.
    Import all the tables that u wish to migrate into Oracle from DB2 into an empty Access database using the mainframe DB2 Odbc link.

    Once that has been done, then go to your Oracle Home folder, locate a folder called OMWB within which you will find the msaccess_exporter folder. Open up the corresponding version of the access database that u are using.
    Convert the Access database that you imported the DB2 tables into to XML format.
    Then using the Oracle Migration Workbench you will be sorted out.

    Tell me the progress.
    Also there is a little program call CHYFO that does exactly what you want, It uses ODBC to connect to the database of your choice and
    extracts the complete schema, including:
    Table definitions,
    data
    and gives you import scripts onto the database target

    maor71

  6. #6
    Join Date
    Jun 2002
    Posts
    12
    Where can I find a sample of that program CHYFO.
    I would love to give it a try as Am having trouble migrating from Access to Db2.

  7. #7
    Join Date
    Jul 2002
    Location
    India
    Posts
    11
    why dont u use an in international format for exporting the data .. it stores the schema as well as the data details & can b used to rebuild the same on any db.
    this was incorporated since db2 v 7.x
    Cheers
    Saka
    :-)

  8. #8
    Join Date
    Jul 2002
    Posts
    19
    Hi
    There is a simple and easy way to do this. SQL Server 7.0 has an import and export wizard which is capable of exporting table from any database to a Target Database (It can also create target tables)
    In the create DSNs to the source and destination machines and invoke Import Export Wizard start transferring the tables by just selecting them from the Source.
    Let us know the result

    hope this helps

  9. #9
    Join Date
    May 2002
    Posts
    27
    What we use is Ascential's Datastage, it is an extraction tool where you extract data from a certain database and dumps it into another database.

    For your case i think oracle has one of its own, i'm not familiar with the product but i thinks its Oracle SQL Loader or something. its the same purpose with Datastage, as it also serves as an extraction/load tool.

Posting Permissions

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