DB2的Blob数据迁移到Informix
package dts;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.InputStream;
import java.sql.PreparedStatement;
public class DTS_Blob {
public DTS_Blob() {
}
public static void main(String[] args) throws Exception {
DTS_Blob dts_blob = new DTS_Blob();
DBConnection connFactory = new DBConnection();
SQLAssemble SQLAssem = new SQLAssemble(args[0],args[1],args[2],args[3],args[4]);
//启动DB2连接
connFactory.setConnPara("127.0.0.1",50000,"DB1","user1","password1");
Connection con2DB2 = connFactory.DB2_Conn();
//查询主键和Blob字段,组装源表查询SQL
String SQLQryDB2 = SQLAssem.assembleQrySQL(true);
System.out.println("SQLQryDB2: " + SQLQryDB2);
//创建到DB2源表的Statement对象,查询Blob字段和主键
Statement statQryDB2 = con2DB2.createStatement();
ResultSet rsDB2 = null;
//启动INFORMIX连接
connFactory.setConnPara("127.0.0.1",20020,"db2","user2","password2","server1");
Connection con2Infor = connFactory.Ifx_Conn();
//根据主键匹配要插入的Blob行,组装目标SQL
String SQLInsInfor = "UPDATE " + args[3] + " SET " + args[4] +
"=? where " + args[0] + "=?";
String SQLQryInfor = SQLAssem.assembleQrySQL(false);
System.out.println("SQLInsInfor: " + SQLInsInfor);
System.out.println("SQLQryInfor: " + SQLQryInfor);
//创建到Informix目的表的PreparedStatement对象,查询主键并更新主键所在Blob字段
PreparedStatement statInsInfor = con2Infor.prepareStatement(SQLInsInfor);
PreparedStatement statQryInfor = con2Infor.prepareStatement(SQLQryInfor);
ResultSet rsInfor = null;
//开始查询DB2数据库
rsDB2 = statQryDB2.executeQuery(SQLQryDB2);
//定义字段
Blob blobDB2 = null;
String idDB2 = null;
//开始DTS服务
InputStream in = null;
int recorders = 0;
while (rsDB2.next()) {
recorders++;
System.out.println("[准备取出第" + recorders + "条记录!]");
//取DB2字段
idDB2 = rsDB2.getString(1);
idDB2 = new String(rsDB2.getString(1).getBytes("ISO8859_1"), "GBK");
// idDB2 = recorders+"";
blobDB2 = rsDB2.getBlob(2);
// blobDB2 = rsDB2.getBlob(1);
int bytes = 0;
//如果DB2主键字段为Null,程序处理下一条。
if (idDB2 == null) {
System.out.println("主键为 [" + idDB2 + "] 的记录,其主键" + args[0] +
"为Null,程序处理下一条!");
System.out.println("******************************第 [" +
recorders + continue; //如果DB2的Blob字段为Null,程序处理下一条。
if (blobDB2 == null) {
System.out.println("主键为 [" + idDB2 + "] 的记录,其Blob字段" + args[2] +
"为Null,程序处理下一条!");
System.out.println("******************************第 [" +
recorders + continue;
} else {
in = blobDB2.getBinaryStream();
bytes = in.available();
}
System.out.println("主键 [" + idDB2 + "] 的Blob字段输入流 [" + in +
"] 被成功取出,并作为第" + recorders +
"条记录!]");
//查询Informix要插入的记录是否存在
System.out.println("查询Informix,查看主键 [" + idDB2 + "] 是否存在");
statQryInfor.setString(1, idDB2);
rsInfor = statQryInfor.executeQuery();
//如果Informix该主键不存在,程序执行下一次循环。
if (!rsInfor.next()) {
System.out.println(args[0] + "=[" + idDB2 + System.out.println("******************************第 [" +
recorders + continue;
} else {
System.out.println(args[0] + "=[" + idDB2 + System.out.println(idDB2 + "," + in + "--[准备写入第" + recorders + //如果Informix该主键存在,程序开始往Informix中写入数据流。
statInsInfor.setBinaryStream(1, in, bytes);
statInsInfor.setString(2, idDB2);
con2Infor.setAutoCommit(true);
statInsInfor.executeUpdate();
System.out.println(idDB2 + "," + in + "--[写入第" + recorders + }
System.out.println("******************************第 [" + recorders + } System.out.println( System.out.println("|从DB2的表 [" + args[1] + "] 列 [" + args[2] +
"] 导入数据到Informix的表 [" + args[3] + "] 列 [" + args[4] + System.out.println( }