CODEWITHSUNDEEP
javaoracle-databasedb2
Abhishek
Abhishek

Reputation: 297

Data Transfer From Oracle to DB2 Database

In my project, we are developing a tool to transfer data from Oracle to DB2 database. My approach is to fetch table by table data from Oracle and insert into table by table in DB2. My implementation is -

package com.rolta.ntps.service;

import java.sql.ResultSet;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.rolta.ntpc.Util.JdbcDB2Connection;
import com.rolta.ntpc.Util.JdbcOracleConnection;

public class PRJCT_TYPE_MST {
    static Logger logger = Logger.getLogger(BOI_ITEM_DTL.class);

    public void getAndInsertData(String tableName) {
        logger.info("**************Fetching Data From Table "+tableName+"*************");

        try {
            JdbcOracleConnection oracleStem = new JdbcOracleConnection(); 
            Statement oracleSmt = oracleStem.oracleStatement();

            ArrayList<Integer> prjct_type_key = new ArrayList<Integer>();
            ArrayList<Integer> prjct_drtn = new ArrayList<Integer>();

            ArrayList<String> prjct_type_code = new ArrayList<String>();
            ArrayList<String> prjct_type_desc = new ArrayList<String>();
            ArrayList<String> crt_by = new ArrayList<String>();
            ArrayList<String> upd_by = new ArrayList<String>();

            ArrayList<String> audit_crt_date = new ArrayList<String>();
            ArrayList<String> audit_upd_date = new ArrayList<String>();

            String query = "select * from NTPC2." + tableName;
            System.out.println("Query is : " + query);
            logger.info("Oracle Query is : " + query);

            ResultSet oracleRS = oracleSmt.executeQuery(query);
            int rowCountInOracle = 0;

            System.out.println("*************Data From Oracle Table************");

            while(oracleRS.next()) {

                prjct_type_key.add(oracleRS.getInt("PRJCT_TYPE_KEY"));
                prjct_drtn.add(oracleRS.getInt("PRJCT_DRTN"));

                prjct_type_code.add(oracleRS.getString("PRJCT_TYPE_CODE"));
                prjct_type_desc.add(oracleRS.getString("PRJCT_TYPE_DESC"));
                crt_by.add(oracleRS.getString("CRT_BY"));
                upd_by.add(oracleRS.getString("UPD_BY"));

                audit_crt_date.add(oracleRS.getString("AUDIT_CRT_DATE"));
                audit_upd_date.add(oracleRS.getString("AUDIT_UPD_DATE"));

                ++rowCountInOracle;
            }

            System.out.println("Row count is : "+ rowCountInOracle);
            logger.info("Row count is : "+ rowCountInOracle);


            logger.info("Inserting Data To DB2");
            JdbcDB2Connection db2Object = new JdbcDB2Connection();
            Statement db2Smt = db2Object.dB2Statement();
            String db2Query = "SELECT * from DRAWING."+tableName;
            ResultSet db2RS = db2Smt.executeQuery(db2Query);

            for(int i = 0; i < rowCountInOracle; i++) {
                db2RS.moveToInsertRow();

                db2RS.updateInt("PRJCT_TYPE_KEY", prjct_type_key.get(i));
                db2RS.updateInt("PRJCT_DRTN", prjct_drtn.get(i));

                db2RS.updateString("PRJCT_TYPE_CODE", prjct_type_code.get(i));
                db2RS.updateString("PRJCT_TYPE_DESC", prjct_type_desc.get(i));
                db2RS.updateString("CRT_BY", crt_by.get(i));
                db2RS.updateString("UPD_BY", upd_by.get(i));

                if(audit_crt_date.get(i) != null) {
                    db2RS.updateDate("AUDIT_CRT_DATE", stringToDate(audit_crt_date.get(i)));
                } else {
                    System.out.println("audit_crt_date date is null");
                    logger.info("audit_crt_date date is null");
                }

                if(audit_upd_date.get(i) != null) {
                    db2RS.updateDate("AUDIT_UPD_DATE", stringToDate(audit_upd_date.get(i)));
                } else {
                    System.out.println("audit_upd_date date is null");
                    logger.info("audit_upd_date date is null");
                }


            }


        } catch(Exception ex) {
            ex.printStackTrace();
        }
    }

    public static java.sql.Date stringToDate(String date) {
        logger.info("Accepted Date Format is - MM-DD-YYYY");

        logger.info("Converting String : "+ date +" into Date Format");

        SimpleDateFormat sdf = new SimpleDateFormat("MM/DD/YYYY");
        java.util.Date utilDate;
        java.sql.Date sqlDate = null;
        try {
            utilDate = sdf.parse(date);
            sqlDate = new java.sql.Date(utilDate.getTime());
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        logger.info("Date is : "+ sqlDate);

        return sqlDate;

    }

}

This approach is working but I am is not satisfy with performance. I understand, It is not performance optimize code because I am reading row by row from table from Oracle and inserting row by row in DB2.

Could anyone help me in finding any better approach?

Upvotes: 0

Views: 2277

Answers (3)

Paul Vernon
Paul Vernon

Reputation: 3901

There are a number of avenues you can go down to optimize insert performance in Db2. As you suggest, inserting row by row is not optimal.

This article by Steve Rees covers the options very nicely https://www.idug.org/p/bl/et/blogid=2&blogaid=602

Note that another option would be to use EXTERNAL TABLEs. https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r_create_ext_table.html However these are only supported on Db2 Warehouse at the time of writing

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

You can federate from Db2 LUW to Oracle data sources. This page lists all supported Federation sourced fro Db2 11.1 http://www-01.ibm.com/support/docview.wss?uid=swg27049211

For Oracle you can connect via either the IBM Branded ODBC driver package, or Oracle NET client12. If you user the latter it is required to be installed on the Db2 server. The IBM branded ODBC driver package is included in Db2 LUW starting from V11.1 fixpack 1 (V11.1.1.1) on x86_64 Linux and AIX.

Once you have configured Federation to your Oracle server - (e.g. use the "DSN-less" connection instructions for 11.1.1.1 here http://www-01.ibm.com/support/docview.wss?uid=swg21996919 ) you can simply INSERT INTO target_db2_table SELECT * FROM source_oracle_nickname.

Note that Federation with non-IBM data servers (e.g. Oracle and other), is included only in the Advanced and Developer Editions of Db2. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.licensing.doc/doc/r0053238.html

Upvotes: 0

Bruno Rivier
Bruno Rivier

Reputation: 86

Many existing tools can be good candidates to migrate your DDL (structures) and DATA from ORACLE to DB2. One example from IBM is IBM® Database Conversion Workbench (DCW) which may successfully support all migration phases.

If you need to build your own, like for a training task, you still have many options: -DB2 allows to directly map an ORACLE table through ODBC using a DB2 WRAPPER; then you are just issuing classic INSERT SELECT statements -At table level, both DBMS has export/import capabilities (ORACLE exp utility ; DB2 LOAD command) -At row level, both DBMS has bulk capabilities through SQL apis

Upvotes: 1

Related Questions