Convert data from SQL Server to DB2 using Java JDBC

Here is a sample program Java program that reads data from Microsoft SQL Server and maps them to a iSeries Db2 table. You need to make JDBC connection to both databases using their drivers.

You can modify this program a little and then it can map data from DB2 to SQL Server database also.

Download JDBC driver for Microsoft SQL Server JDBC Driver

You can extract a copy of the iSeries DB2 JDBC driver from the following IFS directory:
/QIBM/ProdData/HTTP/Public/jt400/lib/jt400.jar

package com.as400samplecode;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;

public class SQLServerToDB2 {
   
    static Properties props;
   
    public static void main(String[] args) {

        String propertiesFileName = "";
       
        if (args.length < 1)
        {
            SQLServerToDB2 sqlServerToDB2 = new SQLServerToDB2();
            System.err.println("Usage: java "+ sqlServerToDB2.getClass().getName()+
            " Properties_File_Name");
            System.exit(1);
        }

        propertiesFileName = args[0].trim();
        mapSqlServerDb2Data(propertiesFileName);    
       
    }
   
    private static void mapSqlServerDb2Data(String propertiesFileName){

        // Declare the JDBC objects.
        Connection SqlConn = null;
        PreparedStatement SqlStmt = null;
        ResultSet SqlRs = null;
        Connection Db2Conn = null;
        PreparedStatement Db2Stmt = null;
        String selectionCriteria = "";

        try {

            props = new Properties();
            props.load(new FileInputStream("properties/" + propertiesFileName.trim()));

            // SQL Server connection string.
            String connectionUrl = props.getProperty("SQLServerUrl").trim() + ";" +
            "databaseName=" + props.getProperty("SQLdatabaseName").trim() + ";" +
            "user=" + props.getProperty("SQLuserId") + ";" +
            "password=" + props.getProperty("SQLpassword");

            // Establish the connection to SQL Server.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            SqlConn = DriverManager.getConnection(connectionUrl);

            // iSeries connection string.
            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("iSeries").trim() + ";naming=system;errors=full";

            // Connect to iSeries                                         
            Class.forName(DRIVER);                                       
            Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());   

            // Create and execute an SQL statement that returns data from SQL Server
            String sql = "SELECT sql_column1,sql_column2,..... from sql_server_table where sql_server_column = ?";
            SqlStmt = SqlConn.prepareStatement(sql);
            SqlStmt.setString(1, selectionCriteria);
            SqlRs = SqlStmt.executeQuery();

            // Db2 SQL statement to insert data 
            sql = "INSERT into DB2_table (db2_column1,db2_column2,.....) VALUES(?,?,.....)";
            Db2Stmt = Db2Conn.prepareStatement(sql);

            // Get MetaData for SQL table
            ResultSetMetaData metaData = SqlRs.getMetaData();
            int colCount = metaData.getColumnCount();

            // Iterate through the SQL server data in the result set and update Db2 table
            while (SqlRs.next()) {
                System.out.println("Start New Row ------>");
                for (int i = 0; i < colCount; i++) {
                    System.out.println(metaData.getColumnName(i + 1) + ": " + SqlRs.getString(i+1));
                    System.out.println(metaData.getColumnTypeName(i + 1));
                }   
                Db2Stmt.setString(1,SqlRs.getString("sql_column1"));
                Db2Stmt.setString(2,SqlRs.getString("sql_column2"));
                // Map all your database columns here
                Db2Stmt.executeUpdate();

               
            }
        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (SqlRs != null) try { SqlRs.close(); } catch(Exception e) {}
            if (SqlStmt != null) try { SqlStmt.close(); } catch(Exception e) {}
            if (SqlConn != null) try { SqlConn.close(); } catch(Exception e) {}
            if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
            if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
        } 

   
    }
   
}

Sample properties file for the Above Java Program

#Properties File for Mapping Microsoft SQL Server data to DB2 

SQLServerUrl=jdbc:sqlserver://{www.yourSQLServer.com}:1433
SQLdatabaseName={SQLdatabase}
SQLuserId={userId}
SQLpassword={password}

iSeries={www.yourDB2Server.com}
userId={userId}    
password={password}