Oracle快速导出数据文件

Oracle快速导出数据文件

数据文件生成方式有多种，但是大多效率低下，对于大型数据仓库来说，高效导出文件是迫切需求：

这里通过shell+java的方式集成老熊写的数据文件生成工具，效率比普通的导出方法高70%：

shell文件生成脚本：

Java调用脚本：这里implement Callable接口，实现了多线程的调用方式：

/**   
 * @Title: Db2FileSync.java
 * @Package etl.etlUtils
 * @Description: TODO
 * @author Lubin Su
 * @date 2015年7月15日 下午12:43:09
 * @version V1.0   
 */
package etl.etlUtils;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.Callable;

import etl.dao.impl.GenericDaoImpl;

/**
 * 
 * @author Lubin Su
 * @date 2015年7月15日 下午12:43:09
 * @version V1.0
 */
public class Db2FileSync implements Callable

获取配置的流程信息：

 

 

public static List> getProcIdForDb2File() {
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
		Date vNowDate = new Date();
		String vNowDateStr = sdf.format(vNowDate);
		ResultSetHandler rsh = new BeanListHandler();
		String vRunSql = "SELECT proc_id, src_db, src_sql, dest_dir, file_nm, stat_cycle_id\n" +
						"  FROM (SELECT o.run_type,\n" + 
						"               o.proc_id,\n" + 
						"               o.src_db,\n" + 
						"               o.src_sql,\n" + 
						"               o.dest_dir,\n" + 
						"               o.file_nm,\n" + 
						"               o.last_succ_cycle,\n" + 
						"               CASE\n" + 
						"                 WHEN o.run_type = 2 THEN\n" + 
						"                  to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - 1, 'yyyymmdd')\n" + 
						"                 WHEN o.run_type = 3 THEN\n" + 
						"                  to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - 1, 'yyyymmdd')\n" + 
						"                 WHEN o.run_type = 4 THEN\n" + 
						"                  to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss') - o.run_hour, 'yyyymmdd')\n" + 
						"                 WHEN o.run_type = 1 THEN\n" + 
						"                  to_char(add_months(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), -1), 'yyyymm')\n" + 
						"               END stat_cycle_id\n" + 
						"          FROM shell_proc_db_2_db_cfg o\n" + 
						"         WHERE o.etl_type = 'Db2File'\n" + 
						"           AND o.state = 'A'\n" + 
						"           AND CASE\n" + 
						"                 WHEN o.run_type = 2 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')\n" + 
						"                 WHEN o.run_type = 3 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')\n" + 
						"                 WHEN o.run_type = 4 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymmdd') || to_char(o.run_hour || o.run_minute), 'yyyymmddhh24mi')\n" + 
						"                 WHEN o.run_type = 1 THEN\n" + 
						"                  to_date(to_char(to_date('" + vNowDateStr + "', 'yyyymmdd hh24:mi:ss'), 'yyyymm') || o.run_day || o.run_hour || o.run_minute, 'yyyymmddhh24mi')\n" + 
						"               END <= SYSDATE) v\n" + 
						" WHERE v.last_succ_cycle < v.stat_cycle_id\n" + 
						"   AND NOT EXISTS (SELECT 1\n" + 
						"          FROM shell_proc_db_2_db_log i\n" + 
						"         WHERE i.proc_id = v.proc_id\n" + 
						"           AND i.stat_cycle_id = v.stat_cycle_id)";
		
		List> arr = null;
		try {
			arr = (List>) GenericDaoImpl.query(vRunSql, null, rsh);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arr;
	}

调用测试：

 

 

public static void main(String[] args) throws IOException, InterruptedException {
		int taskSize = 13;
		// 创建一个线程池
		ExecutorService pool = Executors.newFixedThreadPool(taskSize);
		// 创建多个有返回值的任务
		//List list = new ArrayList();
		
		/*List> procs = DataSynchronizer.getProcIdForDb2Db();
		for (Map map : procs) {
			Callable