Replication between IBM DB2 LUW and Oracle using Oracle GoldenGate

Version 2

    Introduction:

    Nowadays it's a typical scenario to have several RBDMS systems from different vendors such as Oracle, IBM DB2, MS SQL, Postgres, MySQL or others in the same organization. In some cases, a decision to unify everything under one system or couple the systems may be recommended. In that case it will require that data be migrated or replicated between systems-- which in most of the cases could be a daunting task without having the proper tool and proper plan for the job. This article presents an approach to this scenario: executing an initial data load followed by subsequent replication from IBM DB2 LUW to Oracle Database 12c using Oracle GoldenGate.

     

    Environment Setup

     

    Source Database:

    Operating System:           Oracle Virtual Box with SUSE Enterprise Linux 12.3

    Database:                         IBM DB2 is 10.5.7.1

    Oracle GoldenGate:         Oracle GoldenGate v12.2.0.1.1

    Hostname:                        db2linux

    IP address:                       192.168.56.111

     

    Target Database:

    Operating System:           Oracle Virtual Box with Oracle Enterprise Linux 6.5

    Oracle Database:             Oracle Database 12c v12.1.0.2.0

    Oracle GoldenGate:         Oracle GoldenGate v12.2.0.1.11.2.0.0

    Hostname:                        orcl

    IP address:                       192.168.56.101

     

    In these series, we will cover the initial load.  In the second part, unidirectional and bidirectional replication will be covered.

     

    Setting up for initial object migration

    To perform the initial migration of objects I will use the migration capability provided by SQL Developer. The first step is to establish a connection to the IBM DB2 Sample database. You can use SQL Developer to connect and navigate to non-Oracle databases by installing the afferent JDBC drivers.

     

    Tools menu->Preferences->Database->Third party JDBC Drivers

     

    Replicate IBM and Oracle Fig1.jpg

     

    To obtain the IBM DB2 JDBC driver files, download them from IBM DB2 support page or copy them from your DB2 installation directory which in my case is located in /opt/ibm/db2/V10.5/java. Deploy db2jcc4.jar and db2jcc_license_cu.jar in your local directory and add them to SQL Developer using the Add Entry… button.

     

    Next, we should create a connection to IBM DB2 SAMPLE database. The SAMPLE database is used mostly in DB2 tutorials and can be created with db2sampl command. Create a connection to DB2 database and name it SAMPLE using hostname db2linux, user db2inst1, port 50001, and password in our case, test1234.

     

     
    Replicate IBM and Oracle Fig2.jpg

     

    Test the connection to SAMPLE database and list the tables. All these tables will be migrated as we will demonstrate in the rest of the article.

     

    Replicate IBM and Oracle Fig3.jpg

     

     

    SQL Developer is using a repository for performing migrations. Next, we have created a user that will be the repository owner, called SQLMIGR as follows:

     

    SQL> create user sqlmigr identified by test1234;

    User created.

     

    SQL> grant connect,resource,create view to sqlmigr;

    Grant succeeded.

     

    SQL> alter user sqlmirg quota unlimited on users ;

    User altered.

     

    As root user create a directory for migration files and make oracle owner as follows:

     

    [root@orcl sqldeveloper]# mkdir /migrfiles

     

    [root@orcl sqldeveloper]# chown -R oracle:oinstall /migrfiles/

     

    Proceed to install the repository. Tools menu->Migration…->Migrate. The migration wizard will be launched, create a connection named SQLMIGR using SQLMIGR user and test1234 password, next click on the Next button and the migration repository will be created.

     

     

    Replicate IBM and Oracle Fig4.jpg

     

    Once the repository is created the wizard will go to the next step to define a migration project. Name it db2tooracleproj and choose the location for the migration scripts as follows:

     

    Replicate IBM and Oracle Fig5.jpg

     

    Next step is to choose the source database, in our case will be SAMPLE. Let the mode as it is by default (Online).

     

    Replicate IBM and Oracle Fig6.jpg

     

    Push next, in the selected databases list will have SAMPLE. This is what we want so push next.

     

    Replicate IBM and Oracle Fig7.jpg

     

     

    Next, we will reach the wizard’s convert step. This grid contains the standard type conversion list from DB2 to Oracle. At this step, customizations can be made if it is desired. Push next.

     

    Replicate IBM and Oracle Fig8.jpg

     

     

    The next page is related to source translations from SQL PL to PL/SQL (procedures, functions, and triggers) and constraints, views translation.

     

    Replicate IBM and Oracle Fig9.jpg

     

     

    Next chose the target DB. Here we will choose the offline mode, all the migration code will be written to files. We have no interest in data migration so we will check also Proceed to Summary Page.

     

     

    Replicate IBM and Oracle Fig10.jpg

     

     

    Press Finish. A pop-up migration window will launch.

     

    Replicate IBM and Oracle Fig11.jpg

     

     

    Once the migration scripts are created, navigate to the destination directory (/migrfiles/db2tooracleproj/generated/2016-05-15_11-58-06) and execute the master.sql script connected as a dba user. By default the tables will be placed under the user used for connecting to DB2, namely DB2INST1, users and table names can be customized inside the script if it is desired.

     

    We preferred to execute the script without any modification.

     

    Ignore any errors related to views, stored procedures, functions, the point is to have the tables, indexes, and constraints converted correctly.

     

    Being an initial load, disable all the foreign constraints otherwise the replication will fail. We will enable them after the initial load.

     

    As db2inst1 user execute:

     

    SQL>

      declare

        v_str varchar2(300);

        cursor t_cur is select table_name,constraint_name from user_constraints where  

    constraint_type='R';

        begin

        for r in t_cur

        loop

        execute immediate

        'ALTER TABLE ' || r.table_name ||' DISABLE CONSTRAINT ' || r.constraint_name ;

       end loop;

       end;

    SQL> PL/SQL procedure successfully completed.

    Initial load from DB2 to Oracle

     

    Configure Oracle GoldenGate for IBM DB2 LUW

     

    For the rest of the article series, we will use ggs_admin user for Goldengate operations.

     

    IBM DB2 is using external user management, there is nothing like exclusive database users, this is a little bit different than what we have in Oracle by default. In Oracle, this type of authentication is called external authentication. Therefore, the user must be created first as operating system user then the privileges must be granted from DB2 directly.

    As root user we will create ggs_admin user, then from DB2, grant database administrator authority (DBADM) as follows:

     

    db2inst1@db2linux:~> db2 'connect to sample'

    Database Connection Information

    Database server        = DB2/LINUXX8664 10.5.7

    SQL authorization ID   = DB2INST1

    Local database alias   = SAMPLE

    db2inst1@db2linux:~> db2 'grant dbadm on database to user ggs_admin'

    DB20000I  The SQL command completed successfully.

    db2inst1@db2linux:~> 

     

    We extracted the Oracle Goldengate for DB2 LUW archive under /u01/goldengate12c with db2inst1 user as owner and created the subdirs as follows:

     

    GGSCI (db2linux) 4> create subdirs

     

    Creating subdirectories under current directory /u01/goldengate12c

     

    Parameter files                                    /u01/goldengate12c/dirprm: created

    Report files                                          /u01/goldengate12c/dirrpt: created

    Checkpoint files                                  /u01/goldengate12c/dirchk: created

    Process status files                            /u01/goldengate12c/dirpcs: created

    SQL script files                                   /u01/goldengate12c/dirsql: created

    Database definitions files                   /u01/goldengate12c/dirdef: created

    Extract data files                                /u01/goldengate12c/dirdat: created

    Temporary files                                   /u01/goldengate12c/dirtmp: created

    Credential store files                          /u01/goldengate12c/dircrd: created

    Masterkey wallet files                        /u01/goldengate12c/dirwlt: created

    Dump files                                          /u01/goldengate12c/dirdmp: created

     

    Configure and start the manager. Wwe will use port 7890 on IBM DB2 side.

     

    db2inst1@db2linux:/u01/goldengate12c> ./ggsci

    Oracle GoldenGate Command Interpreter for DB2

    Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

    Linux, x64, 64bit (optimized), DB2 10.5 on Dec 11 2015 22:49:17

    Operating system character set identified as UTF-8.

    Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

     

    GGSCI (db2linux) 1> edit params mgr

    PORT 7890

    GGSCI (db2linux) 1> start mgr

     

    Next create an extract parameter file called DB2ORALD and add the parameters as follows:

     

    GGSCI (db2linux) 1> edit params DB2ORALD

    EXTRACT DB2ORALD

    SOURCEDB SAMPLE

    USERIDALIAS ggs_admin

    rmthost orcl, mgrport 7809

    rmttask replicat, group lddb2

    table DB2INST1.PROJECT;

    table DB2INST1.PROJACT ;

    table DB2INST1.ACT ;

    table DB2INST1.IN_TRAY;

    table DB2INST1.ORG;

    table DB2INST1.STAFF;

    table DB2INST1.SALES;

    table DB2INST1.STAFFG;

    table DB2INST1.EMPMDC;

    table DB2INST1.PRODUCT;

    table DB2INST1.INVENTORY;

    table DB2INST1.CUSTOMER;

    table DB2INST1.PURCHASEORDER;

    table DB2INST1.CATALOG;

    table DB2INST1.CL_SCHED;

    table DB2INST1.DEPARTMENT;

    table DB2INST1.EMPPROJACT;

    table DB2INST1.EMPLOYEE;

    table DB2INST1.EMP_PHOTO;

    table DB2INST1.EMP_RESUME;

    table DB2INST1.SUPPLIERS;

    table DB2INST1.PRODUCTSUPPLIER;

     

    Define the extract for initial load as follows:

     

    GGSCI (db2linux) 8> add extract db2orald, sourceistable

    EXTRACT added.

     

    GoldentGate configuration on Oracle Database side

     

    Configure the manager, assign port 7809 and allow access from db2linux as follows:

     

    GGSCI (orcl) 1> edit params mgr

    PORT 7809

    ACCESSRULE, PROG *, IPADDR 192.168.56.111, ALLOW

    GGSCI (orcl) 1> start mgr

     

     

    Configure the initial load replicat, name it LDDB2 and define the parameters as follows:

     

    GGSCI (orcl) 1> edit params LDDB2

    replicat LDDB2

    SETENV (ORACLE_SID=ORCL)

    userid ggs_admin, password ggs_admin

    Handlecollisions

    Assumetargetdefs

    MAP DB2INST1.*,   TARGET  DB2INST1.*       

     

    Define the replicat as specialrun as follows:

     

    GGSCI (orcl) 71> add replicat LDDB2, specialrun

    REPLICAT added.

     

    The initial load

     

    From the db2 side start the extract as follows:

     

    GGSCI (db2linux) 13> start db2orald

    Sending START request to MANAGER ...

    EXTRACT DB2ORALD starting

     

    If you execute a tail  command against ggserr.log you will see that the processing is initiated :

     

    2016-05-15 11:25:05  INFO    OGG-00993 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    EXTRACT DB2ORALD started.

    2016-05-15 11:25:11  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.PROJECT.

    2016-05-15 11:25:16  INFO    OGG-02911  Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.PROJACT.

    2016-05-15 11:25:18  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.ACT.

    2016-05-15 11:25:20  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 
    Processing table DB2INST1.IN_TRAY.

    2016-05-15 11:25:20  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.ORG.

    2016-05-15 11:25:20  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.STAFF.

    2016-05-15 11:25:21  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.SALES.

    2016-05-15 11:25:24  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.STAFFG.

    2016-05-15 11:25:25  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.EMPMDC.

    2016-05-15 11:25:28  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.PRODUCT.

    2016-05-15 11:25:32  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.INVENTORY.

    2016-05-15 11:25:38  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.CUSTOMER.

    2016-05-15 11:25:44  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.PURCHASEORDER.

    2016-05-15 11:25:50  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.CATALOG.

    2016-05-15 11:25:51  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.CL_SCHED.

    2016-05-15 11:25:59  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.DEPARTMENT.

    2016-05-15 11:26:08  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.EMPPROJACT.

    2016-05-15 11:26:13  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.EMPLOYEE.

    2016-05-15 11:26:19  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.EMP_PHOTO.

    2016-05-15 11:26:27  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.EMP_RESUME.

    2016-05-15 11:26:39  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.SUPPLIERS.

    2016-05-15 11:26:44  INFO    OGG-02911 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    Processing table DB2INST1.PRODUCTSUPPLIER.

    2016-05-15 11:26:50  INFO    OGG-00991 Oracle GoldenGate Capture for DB2, db2orald.prm: 

    EXTRACT DB2ORALD stopped normally.

     

    Pay attention to field types such as XMLType and DECFLOAT, these are not supported by Goldengate. Therefore, a different method must be implemented to migrate these fields to Oracle, convert XMLType fields into BLOB or CLOB types, DECFLOAT can be converted to DECIMAL or bring them after migration programmatically. Here is a warning message from ggserr.log from the DB2 side involving XMLType:

     

    2016-05-15 11:25:05  WARNING OGG-05506  Oracle GoldenGate Capture for DB2, db2orald.prm:  

    Table DB2INST1.SUPPLIERS, column ADDR, contains an unsupported data type: XML.The column

    will be excluded.

     

    Finally, enable back the foreign constraints as follows:

     

    As db2inst user  execute:

     

    declare

    v_str varchar2(300);

    cursor t_cur is select table_name,constraint_name from user_constraints where constraint_type='R';

    begin

    for r in t_cur

    loop                               

    execute immediate 'ALTER TABLE ' || r.table_name ||' ENABLE CONSTRAINT ' || r.constraint_name ;

    end loop;

    end;

     

    PL/SQL procedure successfully completed.

     

    Summary

    In this article, we demonstrated an initial load from IBM DB2 LUW to Oracle 12c using Oracle GoldenGate 12c. We used the DB2 SAMPLE database, converted and created the tables to Oracle syntax and executed them against and ORACLE database named ORCL, followed by the initial load. The scenario can be used for initial data migration or to prepare an environment for further replications which will be demonstrated in part II of the series. Briefly, the steps are the following :

     

    • - Create a connection to DB2 database
    • - Create a migration repository or use an existent one.
    • - Create a migration project and generate the DDL scripts.
    • - Execute the DDL scripts on Oracle database.
    • - Install Oracle GoldenGate at source and destination.
    • - Create additional database users for maintaining Golden Gate.
    • - Configure managers, extract and replicat on source and destination.
    • - Initiate the initial load
    • - Troubleshoot the problems