DB2 Database migration data to Oracle database

Just to the company, the leader gave me a task: The DB2 database data into the Oracle database, I try the following methods, very fast. Also import the Db2jcc.jar, Db2jcc_license_cu.jar, ojdbc14.jar three corresponding jar packages.

Data migration:
1, in the DB2 database through the following table query the structure of the table
SELECT
TabName TAB,--table English name
ColName COL,--column name
Case
When Typename= ' VARCHAR ' Then ' VARCHAR2 '
When TYPENAME like ' SMALLINT ' OR TYPENAME like ' BIGINT ' then ' INTEGER '
When TYPENAME = ' CHARACTER ' Then ' CHAR '
When Typename= ' decfloat ' OR TYPENAME = ' DECIMAL ' Then ' number '
ELSE TYPENAME
END TY
,--Data type
Length LEN,--Column lengths
Scale S,--precision
Case
When nulls= ' N ' then ' no '
When nulls= ' Y ' Then ' is '
END N--whether it is empty
--ty| | ' (' | | len| | ') '
From
SYSCAT. COLUMNS S
WHERE
Left (Tabschema, 3) <> ' SYS '
/* and S.scale <>0*/
ORDER by
S.tabname,
S.colno

2, according to the structure of the table in the Oracle database to build a table (script see "Create_tab_onoracle.sql")

3. Import data from DB2 into an Oracle database by writing Java programs (the following code ideas are for reference only)
3.1, import Db2jcc_license_cu.jar, Db2jcc.jar, Ojdbc14.jar three jar package can be
3.2. Write three tool classes

This type of link DB2 the Database Tools class
Package com.util;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;
public class ConnectionDB2 {
private static final String url= "Jdbc:db2://192.168.0.98:50000/sem"; DB2 Database URL
private static final String user= "db2admin";//DB2 Database Account
private static final String password= "dnhc9988"; DB2 Database Password
static{
try {
Class.forName ("Com.ibm.db2.jcc.DB2Driver"). newinstance (); Register Drive with Reflection
} catch (Instantiationexception e) {
E.printstacktrace ();
} catch (Illegalaccessexception e) {
E.printstacktrace ();
} catch (ClassNotFoundException e) {
E.printstacktrace ();
}
}

Public Connection getconnection () {
Connection Conn=null;
try {
Conn=drivermanager.getconnection (URL, USER, PASSWORD);
} catch (SQLException e) {
E.printstacktrace ();
}
Return conn;
}
}

This type of link Oracle Database Tools class
Package com.util;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;
public class ConnectionDB2 {
private static final String url= "Jdbc:db2://192.168.0.98:50000/sem";//DB2 database URL
private static final string user= "Db2admin";//db2 Database account
private static final string password= "dnhc9988"; DB2 Database Password
static{
try {
Class.forName ("Com.ibm.db2.jcc.DB2Driver"). newinstance ();//Use reflection to register driver
} catch ( Instantiationexception e) {
E.printstacktrace ();
} catch (Illegalaccessexception e) {
E.printstacktrace ();
} catch (ClassNotFoundException e) {
E.printstacktrace ();
}
}

Public Connection getconnection () {
Connection conn=null;
try {
Conn=drivermanager.getconnection (URL, USER, PASSWORD);
} catch (SQLException e) {
E.printstacktrace ();
}
Return conn;
}
}

This class is used to close the database connection tool class
Package com.util;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;

public class Closefunction {
Close the connection method to free resources
public static void CloseConnection (Connection conn) {
if (conn!=null) {
try {
Conn.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}

Close execution SQL, freeing resources
public static void Closeexecutesql (Statement preparedstatement) {
if (PreparedStatement!=null) {
try {
Preparedstatement.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}

Close query SQL results, freeing resources
public static void Closeresultset (ResultSet ResultSet) {
if (ResultSet!=null) {
try {
Resultset.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}
}

The key is in this class: you need to place the DB2 database query statement in the DB2SQL variable, and the statement that inserts the data into Oracle is placed in the Oraclesql variable. Note that the order of the fields in the query and the inserted statements is consistent, the DateTime is processed (with setdate) in the while loop, and then the JUnit test class is executed.
Package Com.dao;
Import Java.security.interfaces.RSAKey;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import Org.junit.Test;
Import com.util.CloseFunction;
Import com.util.ConnectionDB2;
Import com.util.ConnectionOracle;

public class Fromdb2tooracle {
@Test
public void Test1 () throws sqlexception{
Long Starttime=system.currenttimemillis (); Record start time
Connection Conndb2=null; Link DB2 Database
Connection Connoracle=null; Link Oracle Database
Statement Statement=null;
ResultSet Resultset=null;
PreparedStatement Preparedstatement=null;
try {
Connectionoracle connectionoracle=new connectionoracle ();
System.out.println (connectionoracle+ "Link Oracle success! ");
ConnectionDB2 connectiondb2=new ConnectionDB2 ();
System.out.println (connectiondb2+ "-->>> link DB2 database Success! ");
Conndb2=connectiondb2.getconnection ();
Execute DB2 Database SQL statement, not fixed code here
String db2sql= "Select Id,sendno,sendtype,brnumber, Sendtime,alarmtime,sendcontent,issend, Elealarmid from DB2ADMIN. Et_sendmessageinfo ";
Execute Oracle Database SQL statement, not fixed code here
String oraclesql= "INSERT into EPC. Sys_log (Id,remark) VALUES (?,?) ";
String oraclesql= "INSERT into Epc.et_sendmessageinfo (Id,sendno,sendtype,brnumber, Sendtime,alarmtime,sendcontent, Issend, Elealarmid) "
+ "VALUES (?,?,?,?,?,?,?,?,?)";
Statement=conndb2.createstatement ();
Statement.execute (Db2sql); Execute SQL statement
Resultset=statement.getresultset (); Get the result set for the DB2 database
Connoracle=connectionoracle.getconnection (); Get Oracle Database connection
Connoracle.setautocommit (FALSE); Reduce efficiency by setting Oracle database to non-auto-commit to avoid committing every time in the loop
Preparedstatement=connoracle.preparestatement (Oraclesql); Execute Oracle Statement, precompiled
int num=0;
while (Resultset.next ()) {
num + +;
Preparedstatement.setstring (1,resultset.getstring ("ID"));
Preparedstatement.setstring (2, resultset.getstring ("Sendno"));
Preparedstatement.setstring (3, resultset.getstring ("Sendtype"));
Preparedstatement.setstring (4, resultset.getstring ("Brnumber"));
Preparedstatement.setdate (5, Resultset.getdate ("Sendtime"));
Preparedstatement.setdate (6, Resultset.getdate ("AlarmTime"));
Preparedstatement.setstring (7, resultset.getstring ("sendcontent"));
Preparedstatement.setstring (8, resultset.getstring ("Issend"));
Preparedstatement.setstring (9, resultset.getstring ("Elealarmid"));/*
Preparedstatement.setstring (resultset.getstring ("Consumetime"));
Preparedstatement.setstring (One, resultset.getstring ("TASKID"));
Preparedstatement.setstring (resultset.getstring ("Issynchro"));
Preparedstatement.setstring (13,resultset.getstring ("LogType"));
Preparedstatement.setstring (resultset.getstring ("Iscache"));
Preparedstatement.setstring (resultset.getstring ("Last_rport_time"));
Preparedstatement.setstring (resultset.getstring ("Auto_remember"));
Preparedstatement.setstring (resultset.getstring ("REMARK")); * *
Preparedstatement.addbatch ();
Commit transactions every 10,000 times in the Oracle database
if (num>10000) {
Preparedstatement.executebatch ();
Connoracle.commit ();
num=0;
}
}
Preparedstatement.executebatch ();
Connoracle.commit ();
} catch (Exception e) {
Connoracle.rollback (); Oracle database Transaction Rollback
E.printstacktrace ();
}finally{
New Closefunction (). CloseConnection (connoracle); Shut down Oracle database and release resources
New Closefunction (). CloseConnection (connDB2); Close the DB2 database and release resources
Long Endtime=system.currenttimemillis (); Log program End time
SYSTEM.OUT.PRINTLN ("Total Time:" + (Endtime-starttime)/1000+ "seconds");
}
}
}

DB2 Database migration data to Oracle database