ogg db2 到 db2 同步 db2迁移到oracle

刚好最近做了一次数据迁移，起因是需要导出数据库的表结构和数据，配合WAR包一起发到项目组，但是项目组的数据库是ORACLE，我们测试的时候用的是DB2数据库，需要将数据从DB2迁移一份到ORACLE，并将ORACLE数据导出。

由于表中存在clob大字段，故传统的数据导入insert语句并不能实现。

使用的工具有plsql和dbvisualizer

自己编写的代码有两块

一个是将db2导出的建表语句转换成oracle语法模式

package dataBase;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;

public class DB2toOracle {

	public static String file = "create table&index.sql";

	public static String dest = "D:\\sunds_tb.sql";

	public static BufferedReader br = null;

	public static BufferedWriter bw = null;

	public static void main(String[] args) throws IOException {
		loadFile();
		fileCopy();
	}

	/**
	 * 边读边写，SQL改造
	 * 
	 * @throws IOException
	 */
	public static void fileCopy() throws IOException {
		String line = ""; // 用来保存每行读取的内容
		String tableName = "";
		String pkName = "";
		String pkParams = "";
		
		StringBuffer sb = new StringBuffer();

		String sql = "";
		
		String dropSql = "";
		while ((line = br.readLine()) != null) { // 如果 line 为空说明读完了
			int begin = 13;
			int end = line.indexOf("(");
			tableName = line.substring(begin, end).trim();
			System.out.println("读取到表名【" + tableName + "】");
			
			String[] str_arr = null;
			String pkParam_temp = null;
			if ((str_arr = line.split("PRIMARY KEY")).length == 2 ) {
				pkParam_temp = line.split("PRIMARY KEY")[1];
				pkParams = pkParam_temp.substring(0, pkParam_temp.indexOf(")") + 1);
				System.out.println("主键字段" + pkParams);
				pkName = tableName.substring(0, tableName.length()-2) + "PK";
				System.out.println("读取到主键：" + pkName);
				
				//有主键时，取CONSTRAINT之前的字符串
				sql = line.split("CONSTRAINT")[0] + ")TABLESPACE SUNDS241241_SM;" + "ALTER TABLE SUNDS241241." + tableName + " ADD CONSTRAINT " + pkName + " PRIMARY KEY " + pkParams + ";\n";
			} else {
				//没主键时
				sql = line;
			}
			
			//拼接删表语句
			dropSql = "ALTER TABLE SUNDS241241." + tableName + " DROP PRIMARY KEY CASCADE;" + "\n"
					+ "DROP TABLE SUNDS241241." + tableName + " CASCADE CONSTRAINTS;" + "\n\n";
			sb.append(dropSql);
			
			sql = sql.replaceAll("CHARACTER", "CHAR").replaceAll("CLOB(1048576)", "CLOB");
			
			
			bw.write(sql);
			bw.newLine();
			bw.flush();
		}
		bw.write(sb.toString());
		bw.flush();
		// 将读到 buffer 中的内容写出来
		bw.close();
		br.close();
	}

	/**
	 * 建立文件连接
	 * 
	 * @throws IOException
	 */
	public static void loadFile() throws IOException {
		br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "gbk"));
		bw = new BufferedWriter(new FileWriter(dest));
	}

}

还有一个是将db2的数据跑批到oracle中

package adatabase;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.StringReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

public class CopyData {

	public static Connection db2Conn = null;

	public static Connection oracleConn = null;

	public static void main(String[] args) throws IOException {
		getDB2Conn();
		getOracleConn();

		copy();
	}

	private static void copy() throws IOException {
		String sql = "select * from SM_PAGE_MODEL_TB";

		StringBuffer sb = new StringBuffer();
		sb.append("insert into SM_PAGE_MODEL_TB (");
		PreparedStatement pstmt_db2 = null;
		PreparedStatement pstmt_oracle = null;

		StringReader reader = null;
		BufferedReader br = null;
//		InputStream input = null;
		try {
			pstmt_db2 = (PreparedStatement) db2Conn.prepareStatement(sql);
			ResultSet rs = pstmt_db2.executeQuery();
			int col = rs.getMetaData().getColumnCount();

			List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();

			for (int i = 1; i <= col; i++) {
				Map<String, String> colName = new HashMap<String, String>();
				colName.put(rs.getMetaData().getColumnName(i), rs.getMetaData().getColumnTypeName(i));
				mapList.add(colName);
				if (i != col) {
					sb.append(rs.getMetaData().getColumnName(i));
					sb.append(",");
				} else {
					sb.append(rs.getMetaData().getColumnName(i));
					sb.append(") values (");
					for (int j = 1; j <= col; j++) {
						if (j != col) {
							sb.append("?,");
						} else {
							sb.append("?)");
						}
					}
				}
			}
			System.out.println(sb.toString());

			pstmt_oracle = oracleConn.prepareStatement(sb.toString());

			String paramValue = "";

			Clob clobValue = null;

			String paramName = "";
			String paramType = "";

			System.out.println("mapList.size():" + mapList.size());
			while (rs.next()) {

				for (int i = 1; i <= mapList.size(); i++) {
					Map<String, String> map = mapList.get(i - 1);
					Set<Entry<String, String>> entrySet = map.entrySet();
					for (Entry<String, String> entry : entrySet) {
						paramName = entry.getKey();
						paramType = entry.getValue();
					}

					if ("CLOB".equalsIgnoreCase(paramType)) {
						clobValue = rs.getClob(paramName);
						if (clobValue != null) {
							br = new BufferedReader(clobValue.getCharacterStream());
							StringBuilder stringbuilder = new StringBuilder();
							String line = "";
							while ((line = br.readLine()) != null) {
								stringbuilder.append(line).append("\n");
							}
							paramValue = stringbuilder.toString();
						}

						reader = new StringReader(paramValue);
						pstmt_oracle.setCharacterStream(i, reader, paramValue.length());
					} else if ("VARCHAR".equalsIgnoreCase(paramType) || "VARCHAR2".equalsIgnoreCase(paramType)
							|| "CHAR".equalsIgnoreCase(paramType)) {
						paramValue = rs.getString(paramName);
						pstmt_oracle.setString(i, paramValue);
						System.out.println("瀛楃涓茬被鍨嬪弬鏁板悕锛�" + paramName);
						System.out.println("瀛楃涓茬被鍨嬪弬鏁板�笺��" + paramValue + "銆�");
						if (null != paramValue && paramValue.length() > 50) {
							System.out.println("###############瓒呴暱浜�" + paramValue);
						}
					}
				}
				// pstmt_oracle.addBatch();
				try {
					pstmt_oracle.executeUpdate();
				} catch (java.sql.SQLException e) {
					if (e.toString().indexOf("ORA-00001: 违反唯一约束条件") != -1) {
						System.out.println(e.toString());
						System.out.println("銆愰噸澶嶃�戝瓧绗︿覆绫诲瀷鍙傛暟鍚嶏細" + paramName);
						System.out.println("銆愰噸澶嶃�戝瓧绗︿覆绫诲瀷鍙傛暟鍊笺��" + paramValue + "銆�");
					} else {
						throw e;
					}
				}
			}
			// pstmt_oracle.executeBatch();
			oracleConn.commit();

			pstmt_oracle.close();
			oracleConn.close();
			pstmt_db2.close();
			db2Conn.close();
		} catch (SQLException e) {
			try {
				oracleConn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				pstmt_oracle.close();
				oracleConn.close();
				pstmt_db2.close();
				db2Conn.close();
				br.close();
				reader.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

	private static Connection getOracleConn() {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@172.16.3.206:1521:xe";
		String username = "sunds241241";
		String password = "SUNDS241241";
		try {
			Class.forName(driver); // classLoader,鍔犺浇瀵瑰簲椹卞姩
			oracleConn = (Connection) DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return oracleConn;
	}

	private static Connection getDB2Conn() {
		String driver = "com.ibm.db2.jcc.DB2Driver";
		String url = "jdbc:db2://172.16.3.234:50000/sunds241";
		String username = "sunds";
		String password = "sunds";
		try {
			Class.forName(driver); // classLoader,鍔犺浇瀵瑰簲椹卞姩
			db2Conn = (Connection) DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return db2Conn;
	}
}

整体思路是，先将建表语句准备好，然后再oracle中布置表空间，再导入表结构和索引主键。

最后导入init数据，本次的关键点在于clob数据的处理，经查阅资料，已知可以用如下语句进行clob字段的导入

--/
DECLARE  
  clobValue 表名.字段名%TYPE;  
BEGIN  
  clobValue := 'XXX'; --字段内容  
  UPDATE 表名 T SET T.字段名 = clobValue WHERE 条件;  
  COMMIT;  
END;
--/

update语句可以换成insert语句

如果不带"--/"无法执行的话，就把头尾的--/加上。

但考虑到如此做法在java代码中不便于实现，因为每张表的clob字段不确定性。故将这个导入方法作为备选排漏使用，跑批个别失败的可用该语句补齐。

之后为避免数据库导入导出clob字段处理麻烦，oracle数据统一导出dmp文件。