Using GoldenGate to do initial load from IBM i / AS 400 to Oracle database

Introduction

I don't know whether IBM is increasing their prices to maintain AS400 machines operational, but the fact is that i am getting more and more requests to migrate workloads from IBM DB2 databases to Oracle.

In this case the customer has requested not only a bidirectional data flow between DB2 and Oracle (next article upcoming :) ) but also they need do bring all data from DB2 database to Oracle database.

As Oracle GoldenGate Microservices architecture for IBM i its not there *yet*, i have to pull out the techniques from (g)old days and used the classic GoldenGate version which still i am passionate about. Command line wins :D

Here is the step by step:

The Environment

Source: IBM DB2 for i 7.4 running on IBM AS400 - Power 8

Target: Oracle Database 19c running on a EC2 AWS host

OGG Hub VM configuration:

• Red Hat Enterprise Linux release 8.8 (Ootpa)
• Java: OpenJDK Runtime Environment (build 1.8.0_382-b05)
• IBM DS Driver v11.5 for ODBC CLI

 *Check OGG compatibility matrix to make sure to download the supported versions and required drivers

The Architecture

As a customer requirement no software was installed on source or target database servers, instead I have used the so called GoldenGate Hub. A simple VM with the following products installed to do Remote Capture and Delivery of data to the databases:

-      An Oracle GoldenGate for non-Oracle databases (classic version): where transactions are captured from IBM DB2 database using an extract process. The transactions are stored in trail files and save to the local host that will be read by Oracle GoldenGate for Oracle databases 21c .

-      An Oracle GoldenGate for Oracle databases 21c (Microservices version): where the trail files will be opened and replicate the data to the tables using a replicat process.

Objective

Bring all data from a DB2 schema and insert into Oracle database. We need to create the tables beforehand empty in Oracle DB.

Implementation

1. create extract parameter file:

cd /app/oracle/time/product/version/21_3ogg_DB2_iSeries/dirprm/

[oracle@etime1-test dirprm]$ cat init_ORDERS.prm

SOURCEISTABLE

SOURCEDB B99555V, USERIDALIAS OGGEXT_B99555V

RMTHOST etime1-test.hostname.de, MGRPORT 9050

RMTFILE /app/oracle/time/product/version/21_3ogg_depl_ORA_SM/var/lib/data/init/ORDERS/a0

TABLE SCHEMA1.ORDERS;

1. run the extract process:

       cd /app/oracle/time/product/version/21_3ogg_DB2_iSeries/        

       ./extract paramfile dirprm/init_ORDERS.prm reportfile dirrpt/init_ORDERS.rpt        

1. check if trail file is created on target[oracle@etime1-test ORDERS]$ ls -ltr

total 398400-rw-r----- 1 oracle dba 203980189 Aug 23 15:29 a0000000[oracle@etime1-test ORDERS]$
        

1. check the report file for the number of rows exported[oraetime@etime-test 21_3ogg_DB2_iSeries]$ cat dirrpt/ORDERS.rptReport at 2023-08-23 15:29:23 (activity since 2023-08-23 15:29:10)

Output to /app/oracle/time/product/version/21_3ogg_depl_ORA_SM/var/lib/data/init/ORDERS/a0:From table SCHEMA1.ORDERS:      #                   inserts:    166843        
#                   updates:         0        
#                   deletes:         0        
#                   upserts:         0        
#                  discards:         0 
        

1. Access OGG Microservices for Oracle databases and prepare the replicat

-       click on the + button to Add Replicat

-       Select “non integrated replicat”, then click on Next

-       Fill out the basic information to configure the replicat, then click on Next

-       Add the mapping rules, then click on Create

-       A new replicat process will appear in a yellow status:

-       Click Action, then click start

You can monitor the progress of the initial load by doing a select count(*) target_table_name OR by checking the statistics of the replicat process.

Conclusion

In this article, we described how to do initial load from IBM DB2 for i into a relational database using a mix of classic and Microservices modes of GoldenGate. Please note that the classic architecture usage here is exceptional/valid only this database type. The OGG Microservices usage is highly recommended for all the others sources and targets as since GoldenGate 21c the classic Architecture has been deprecated.

Hope it was helpful. :)