This discussion is archived
4 Replies Latest reply: Nov 9, 2012 8:18 AM by 972512 RSS

Migrating DB2 to Oracle

972512 Newbie
Currently Being Moderated
Hi,
I have a requirement to migrate DB2 v9.5.0.5 to Oracle Database 11g Enterprise Edition 11.2.0.2.0.

I noticed that the migration tool supported by Oracle SQL Developer helps in migrating a Database from db2. In my case, the database in DB2 has multiple schemas and I want to migrate only one schema to Oracle. Is there some way to restrict the migration to only one schema? The quick migrate option is working only at a table level but not on a schema level.

Thanks a lot in advance and Best Regards
Moorthy
  • 1. Re: Migrating DB2 to Oracle
    wkobargs Journeyer
    Currently Being Moderated
    Hello,

    I am pretty sure that this works fine when you do an offline migration. Please read this from the SQL Developer documentation:


    IBM DB2 Offline Capture Notes

    Script files and the db2_x.ocp file are generated in the target folder. The main script is startDump.xxx, which you must execute to produce the schema dump. The script files prompt you for the database name, user name, and password, and they use this information to connect to the local DB2 database. The scripts generate the schema dump for database objects within object-specific folders.

    To capture the schema information in offline file format, use a command in the following format (with the db2 executable in the run path):
    db2 -x o -r <file name> <schema query>+

    To export the schema data in offline file format, use a command in the following format (with the db2 executable in the run path):

    For DB2 version 9 data export:
    db2 export to <file name> of DEL modified by lobsinsepfiles coldel"#" timestampformat=\"YYYY/MM/DD HH.mm.ss\" datesiso nochardel <select query>

    For DB2 version 8 data export:
    db2 export to <file name> of DEL modified by coldel"#" timestampformat=\"YYYY/MM/DD HH.mm.ss\" datesiso nochardel <select query>

    DB2 version 9 supports LOB data in separate files, which is better for migrating large data sizes. With version 8, to support large LOB data, you must modify the oracle ctl file command and db2 command in unload_script.bat or unload_script.sh.

    The table data is exported to files with names in the format <catalog>.<schema>.<table>.dat. The format of file is as follows: data1#<COL_DEL> #data2#<COL_DEL>…<ROW_DEL> where COL_DEL and ROW_DEL come from migration offline preference settings.

    Before you execute the DB2 data dump script, you must log in by entering a command in the following format:
    db2 connect to <catalog> user <user name> using <password>

    You can then execute the script using the logged connection session.

    Regards
    Wolfgang
  • 2. Re: Migrating DB2 to Oracle
    972512 Newbie
    Currently Being Moderated
    Hi,
    Thanks a lot for your reply. Yes, now I could create .bat and .ocp files .i.e. I could capture the relevant schema offline.

    But I am facing issues while converting this captured model to converted model using the migration wizard.
    Now, I have a .ocp file which was created by clicking Tools, then Migration, then Create Database Capture Scripts. The Capture page of the wizard does not show any database in the Available Databases section. I could not figure out any issue with the .ocp file. The contents of the .ocp file is only a single line:

    OfflinePluginIdentifier=oracle.dbtools.migration.workbench.plugin.db2v9.DB2v9Plugin

    Could you please assist me in selecting the database in the Capture page of the wizard?

    Thanks and Regards
    Moorthy
  • 3. Re: Migrating DB2 to Oracle
    wkobargs Journeyer
    Currently Being Moderated
    Have you run the startDump.bat command procedure against your DB2 database and did you got all the .dat files like schemas.dat, tables.dat, etc.? These .dat files are needed for the offline capture. Did you look into the startDump.bat file to understand what it does?

    Wolfgang
  • 4. Re: Migrating DB2 to Oracle
    972512 Newbie
    Currently Being Moderated
    Hi,
    I have achieved this by using a combination of Sqllldr (for uploading into Oracle) and batch scripts (for capturing the data). Thanks a lot for your help.
    \Regards
    Moorthy

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points