13 Replies Latest reply: Jul 2, 2014 8:05 AM by ljames RSS

    IBM DB2 to Oracle Database Migration Using SQL Developer

    Nag

      Hi,

       

      We are doing migration of the whole database from IBM DB2 8.2 which is running in WINDOWS to Oracle 11g Database in LINUX.

       

      As part of pre-requisites we have installed the Oracle SQL Developer 4.0.1 (4.0.1.14.48) in Linux Server with JDK 1.7. Also Established a connection with Oracle Database.

       

      Questions:

      1) How can we enable the Third Party Database Connectivity in SQL Developer?

      I have copied the files db2jcc.jar and db2jcc_license_cu.jar from the IBM DB2 (Windows) to Oracle (Linux)

      2) Will these JAR files are universal drivers? will these jar files will support in Linux platform?

      3) I got a DB2 full privileged schema name "assistdba", Shall i create a new user with the same name "assistdba" in the Oracle Database & grant DBA Privillege? (This is for Repository Creation)

      4) We have around 35GB of data in DB2, shall i proceed with ONLINE CAPTURE during the migration?

      5) Do you have any approx. estimation of Time to migrate a 35 GB of data?

      6) In-case of any issue during the migration activity, shall i get an support from Oracle Team (We have a Valid Support ID)?

      7) What are all the necessary Test Cases to confirm the status of VALID Migration?


      Request you to share the relevant metalink documents!!!

       

      Kindly guide me in-order to go-ahead with the successful migration.

       

      Thanks in Advance!!!

      Nagu

      nagarajdreams@gmail.com

        • 1. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
          Kgronau-Oracle

          Hi Nagu,

          let me copy/paste your questions from above and answer them inline:

           

          Questions:

          1) How can we enable the Third Party Database Connectivity in SQL Developer?

          => start SQL Dev and click on Tools -> Preferences. In Database you find "Third Party JDBC Drivers" and here add the JDBC JAR files.

          2) Will these JAR files are universal drivers? will these jar files will support in Linux platform?

          => yes.

          3) I got a DB2 full privileged schema name "assistdba", Shall i create a new user with the same name "assistdba" in the Oracle Database & grant DBA Privillege? (This is for Repository Creation)

          No. In Oracle create a dedicated user for example "migration" with the privileges documented in the online help just to store the repository information. That will help you to separate the repository information from the real migration data.

          4) We have around 35GB of data in DB2, shall i proceed with ONLINE CAPTURE during the migration? No. For 35GB use the OFFLINE method.

          5) Do you have any approx. estimation of Time to migrate a 35 GB of data? no as it depends on your SQL*Loader parallelism

          6) In-case of any issue during the migration activity, shall i get an support from Oracle Team (We have a Valid Support ID)? yes you can contact Oracle Support.

          7) What are all the necessary Test Cases to confirm the status of VALID Migration?  Most important check is that you have migrated all the data and that your application is running fine.


          - Klaus

          • 2. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
            Nag

            Dear Klaus,

             

            Thanks for your valuable information.

             

            As mentioned, i have copied the 2 JAR files from IBM DB2 (windows) to client machine where SQL developer is running in Linux. Now, I can able to see the Third Party Database "DB2" Tab in SQL Developer.

             

            Then i have created a user called "MIGRATIONS" in the Oracle Database & allocated a tablespace to this user. Also i have established a connection between IBM DB2 Database & Oracle Database.

             

            Before step forward to Migration Activity, do we need to take a list in DB2 database such as tables, rows, columns, package etc.,?  If Yes, do i need to track the records of each table one by one?. Any simplified query? 

             

            Whether SQL developer will provide a comparison report like what are list of changes during the migration, status before migration & status after the migration?

             

            How can we make sure that all the data has been migrated successfully. Also Client asking to prepare a test cases to valid after the migration.

             

            Kindly help me to proceed further.

             

             

            Many Thanks

            Nagu

            • 3. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
              Kgronau-Oracle

              Nagu,

              when you are able to connect to the DB2 database the SQL Developer Migration tool will collect the source data information for you online. That's at the beginning the easiest way to migrate a Db2 database - it might be necessary later on to move to an Offline Migration (just in case the Online method fails due to any reasons) but right now start with the online migration and then have a look at the migration report. It shows what steps were performed and which objects were migrated successfully or need manual corrections.

              There is no real test utility for SQL Developer if all data has been migrated successfully - you need to check it on your own. If you want to use a more reliable migration method including data comparison you need to purchase GoldenGate and Veridata.

              - Klaus

              • 4. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                Nag

                Hi Klaus,

                 

                We have migrated the IBM DB2 database to Oracle. We got the below status before moving the data to oracle.

                 

                Conversion Status for the project Name showing the below value

                 

                IBM DB2 vs Oracle

                Users Details - 1/1 ---> 100%

                Tables Details - 505/505 ---> 100%

                Views Details - 1/1 ---> 100%

                Triggers Details - 0/7 ---> 0%

                Procedures Details - 0/0 ---> 100%

                 

                What happened to Package? Whether this migration also move the standard packages from DB2 or will not touch it?

                 

                Here the FAILED one is Triggers. In details it is showing the status of 7 Trigger as "Missing". We continued with the next  screen & proceeded with moving of data in to oracle.

                 

                Also tell me, What are all the standard reports required to submit after the migration, This should be an Comparison Report between IBM DB2 & Oracle DB.

                 

                FYI - Apart from Triggers, I have compared the list of table count, sample records in the random table, views, synonyms. Total count is matching.

                 

                Apart from these report do we need to any other for Auditing Purpose.

                 

                Kindly guide me.

                 

                Thanks & Regards

                Nagu

                • 5. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                  Kgronau-Oracle

                  Nagu,

                  just to confirm, you're talking about DB2 packages which contain executable forms of SQL statements and are used to execute SQL statements on the database server. Correct?

                  - Klaus

                  • 6. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                    Nag

                    Dear Klaus,

                     

                    Since there are only 7 failed triggers, We have manually recreated Triggers in Oracle Database.

                     

                    Coming to Packages, Yes you are right.

                     

                    During migration we didn't see any package list/status in the migration wizard, And also in the oracle document it is mentioned as

                    " Package privileges -  Package objects are not migrated from IBM DB2 by SQL Developer"

                     

                    Additional Query

                    1) How to migrate the packages from DB2 to Oracle, if the testing is failed bcz of PACKAGES?

                    2) We have taken the necessary reports in Oracle Database. Similarly, How to fetch the total row count of all the tables in IBM DB2 (Using SQL Developer or Toad for DB2). Can you share the query...

                    3) After the migration, we found that some of column names have been changed for 18 tables.  This is due to that the column names are considered as a "KEYWORDS" for Oracle database.

                         Now the question, how the client application will refer this columns. If it will impact, how we can manage the client?  How to convey the message to client with alternate solution?.

                     

                    Kindly help us.

                     

                    Thanks & Regards

                    Nagu

                    • 7. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                      Kgronau-Oracle

                      Nagu,

                      these sort of packages you are referring to are IBM specific. For example they belong to ODBC, JDBC drivers etc and only used by those products when they need to call SQL statements. Oracle is using a different implementation so there's no need to migrate them.

                      When you have tables with renamed columns because they are reserved words in Oracle, then there's no other way then changing the app. Regarding the DB2 tables IBM also uses catalog tables - have a look at the manual starting here:

                      IBM DB2 9.7 for Linux, UNIX and Windows Information Center

                      - Klaus

                      • 8. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                        Nag

                        Dear Klaus,

                         

                        Yes, I Agreed on your point. Let me convey the same message to Client. Because Application team is taking care by another vendor.

                         

                        ISSUE:

                        We are facing some issue after the DB Migration to Oracle,  As mentioned earlier, we have totally 506 tables in IBM DB2. After the migration on the whole database, we can see the total count of 506 tables in Oracle also.

                         

                        But, Total no. of Rows for 7 tables is not matching with the DB2 Database. What would be the issue here? Is the data is not coming bcz of changes in the datatypes? My technical developer saying that:

                         

                        "During Conversion Phase (Data Type Conversion):

                        CHARACTER FOR BIT DATA & CHAR FOR BIT DATA should be changed to "RAW" instead of "CHAR" for Oracle Data Type"

                         

                        While migrating, we didn't changed anything in data types, we left as it default (CHAR). Now, the issue is related to this Data Type?

                         

                        After taking the backup of 7 tables, will truncate the table in Oracle & then Shall we try with migrating a particular table using SQL developer again? Is it possible to change the datatype @ this stage?.

                        I Mean...How can we change the datatype for a particular table during the Migration using SQL Developer?

                         

                        Can you pls help us. Even if it is more technical pls update it. We are waiting for your valuable reply.

                         

                        Thanks & Regards

                        Nagu

                        • 9. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                          Nag

                          Dear Klaus,

                           

                           

                           

                          We have identified the solution.

                           

                           

                           

                          Out of 7 tables, Total count of rows in 5 tables were not matched. This may happens during the bulk data migration.

                           

                           

                           

                          Solution is, we just truncated that 5 tables & re-migrated the tables (only which is not matched) from source to target using sql developer.

                           

                           

                           

                          And the remaining Total count of rows in 2 tables was "0" which means not even a single record was transferred from DB2 to Oracle. This is bcz of "Improper datatype conversion for Oracle".

                           

                           

                           

                          We have manually altered the table column datatype directly in Oracle Database (From "CHARACTER FOR BIT DATA" to "RAW")

                          Then truncated the table

                          Finally, re-migrated the 2 tables

                           

                           

                           

                          Note:

                          If we have truncated the table, Index related to that table will also be dropped. So, in this case we need to recreate the Index in the Oracle.

                           

                           

                           

                           

                           

                          Now the total count of each table & rows are matching for DB2 & Oracle.

                           

                           

                           

                           

                           

                           

                          Thanks & Regards

                          Nagu

                          • 10. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                            Nag

                            Hi Klaus,

                             

                            Continued with the above posts - Now we are doing another database migration from IBM DB2 to Oracle, which is very less of data (Eg: 20 Tables & 22 Indexes).

                             

                            As like previous database migration, we have done the pre-requirement steps.

                             

                            DB Using SQL Developer

                            Created Migration Repository

                            Connected with the created User in SQL Developer

                            Captured the Source Database

                            Converted Captured Model to Oracle

                            Before Translation Phase we have clicked on the "Proceed Summary"

                            Captured Database Objects & Converted Database Objects has been created under PROJECT section.

                             

                            Here while checking the status of captured & converted database objects, It's showing the below chart as sample:


                            OVERVIEW

                            PHASE               TABLE DETAILS          TABLE PCT

                            CAPTURE               20/20                              100%

                            CONVERT               20/20                              100%

                            COMPILE                 0/20                                   0%

                             

                            TARGET STATUS

                            DESC_OBJECT_NAME

                            SCHEMANAME

                            OBJECTNAME

                            STATUS

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:ARG_I1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H0INDEX01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H1INDEX01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H2INDEX01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H3INDEX01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H4INDEX01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H4INDEX02:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H5INDEX01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H7INDEX01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:H7INDEX02:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:MAPIREP1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:MAPISWIFT1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:MAPITRAN1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:OBJ_I1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:OPR_I1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:PRD_I1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:S1TABLE01:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:STMT_I1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:STM_I1:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                            INDEX

                            TRADEIN1

                            SQLDEV:LINK:&SQLDEVPREF_TARGETCONN:null:TRADEIN1:INDEX:X0IAS39:oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink

                            Missing

                             

                            We have seen only "Missing" in the chart, also we couldn't have any option to trace it in Log file.

                             

                            Only after the status is VALID, we can proceed with the Translation & Migration PHASE.

                             

                            Kindly help us how to approach this issue now.

                             

                             

                            Thanks

                            Nagu

                            • 11. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                              user10604306

                              Hi,

                               

                              We have migrated the database from IBM DB2 to Oracle as mentioned in the above posts.

                               

                              Now after few months, banking application vendor came to this picture for their application upgrade & reported the issue with data migration (i.e Mismatch in Data Type Mapping & Data Type Length for some of the table column's).

                               

                              Technical Analysis:

                              1) Source table from DB2 has junk/special characters

                              2) Each junk characters in source table carries on byte

                              3) During migration the data type char(35 bytes) was mapped as char(35 char)

                              4) In oracle each junk characters carries more than one byte

                              5) So during the application upgradation the data could not be inserted to their temporary table, as the temporary table has the datatype char(35 bytes)

                               

                              In migrated Oracle db, You had created a table with column data type in selected format like this:
                                       for e.g BEN_AMEND2 CHAR (5 CHAR)

                              But in application upgrade script, they have it in different format like this
                                       for e.g BEN_AMEND2 CHAR (5)


                              In DB2 database, column looks like this
                                       for e.g. BEN_AMEND CHAR(5)


                              CHAR (5 CHAR) : It accepts bytes. He can insert like this (UNISTR('\00d6\00d6\00d6\00d6\00d6')) (multiple of 4) 20 characters

                              CHAR (5) : It doesn't accept the above value. He can insert like this (UNISTR('\00d6')). If he try to insert that above statement, it will throw the below issue.

                              Error : ORA-12899: value too large for column "TRADEIN1"."CNVTEMP"."BEN_AMEND2" (actual: 38, maximum: 35)

                               

                              Is there any issue with the Data mapping during the migration part using SQL developer? Because during migration I have not changed anything in sqldeveloper tool.

                               

                              The issue is very critical!!!

                               

                              Kindly some one from the team give me a right direction to solve this issue.

                               

                               

                              Regards

                              Nag

                              • 12. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                                user10604306

                                Hi Klaus,

                                 

                                Can you pls help me on this issue.

                                 

                                Nag

                                • 13. Re: IBM DB2 to Oracle Database Migration Using SQL Developer
                                  ljames

                                  TRADEIN1"."CNVTEMP"."BEN_AMEND2

                                   

                                  alter the above column and increase the width to 40

                                   

                                  alter table <table_name> modify <column_name> varchar2(40);

                                   

                                  Try to insert after this