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<Object> {private String procId;private String srcDb;private String destDir;private String srcSql;private String fileNm;private String statCycleId;/** * <p>Title: 构造函数</p> * <p>Description: </p> * @param procId * @param srcDb * @param destDir * @param srcSql * @param fileNm * @param statCycleId */public Db2FileSync(String procId, String srcDb, String destDir,String srcSql, String fileNm, String statCycleId) {super();this.procId = procId;this.srcDb = srcDb;this.destDir = destDir;this.srcSql = srcSql;this.fileNm = fileNm;this.statCycleId = statCycleId;}/** * 数据表以文件的方式下发 * @author lubinsu * @date 2015年7月12日 * @param procId * @throws UnsupportedEncodingException  */public void db2FileExtractByProcInfo(String srcDb, String descDir, String srcSql, String fileNm) throws UnsupportedEncodingException {//获取当前时间SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");Date vStartTime = new Date();System.out.println("Start sending ... " + sdf.format(vStartTime));String[] cmd = new String[5];//环境变量String[] env = {"lubinsu_dir=/int_file/lubinsu","AUTHSTATE=compat","TERM=vt100","SHELL=/usr/bin/bash","NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK","SSH_CLIENT=132.228.78.73 49796 22","SSH_TTY=/dev/pts/4","LOCPATH=/usr/lib/nls/loc","USER=css_int","ODMDIR=/etc/objrepos","ORACLE_BASE=/oracle/app/oracle","TMOUT=0","MAIL=/usr/spool/mail/css_int","PATH=:/usr/java6_64/bin:/opt/freeware/bin:/usr/local/bin:/bin:/usr/bin:/usr/sbin:/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/home/css_app/bin:/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/urs/ccs/bin:/bin:/usr/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java5/jre/bin:/usr/java5/bin:.","css_int_HOME=/int_file","LOGIN=css_int","PWD=/int_file/lubinsu/scripts/etl_dispatch/css_etl","JAVA_HOME=:/usr/java6_64","EDITOR=vi","JAVA_COMPILER=NONE","LANG=AMERICAN_AMERICA.ZHS16GBK","etl_dir=/int_file/etl_dir","TZ=Asia/Shanghai","LIB_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib:/usr/lib:/lib","SHLVL=1","HOME=/int_file","etl_dispatch=/int_file/lubinsu/scripts/etl_dispatch/css_etl","LC__FASTMSG=true","MAILMSG=[YOU HAVE NEW MAIL]","LOGNAME=css_int","CLASSPATH=::/usr/java6_64/lib","SSH_CONNECTION=132.228.78.73 49796 132.228.27.132 22","CLCMD_PASSTHRU=1","SHLIB_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib","ODS_HOME=/int_file/ODS_ETL/","ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_2","_=/bin/env","OLDPWD=/int_file/lubinsu/etl_data","NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat","LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/oracle/app/oracle/product/11.2.0/dbhome_2/lib:/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/lib:/usr/lib:/lib"};Process prc = null;int exitVal = 0;cmd[0] = "/int_file/lubinsu/scripts/etl_dispatch/css_etl/java_etl/db2File.sh";cmd[1] = srcDb;cmd[2] = descDir;cmd[3] = srcSql;cmd[4] = fileNm;try {prc = Runtime.getRuntime().exec(cmd, env);} catch (IOException e) {e.printStackTrace();}InputStream stderr = prc.getErrorStream();InputStreamReader isr = new InputStreamReader(stderr);BufferedReader br = new BufferedReader(isr);String line = null;//打印返回信息try {while ((line = br.readLine()) != null)System.out.println(line);} catch (IOException e) {e.printStackTrace();}try {exitVal = prc.waitFor();} catch (InterruptedException e) {e.printStackTrace();}//打印返回号if (exitVal > 0) {System.out.println("Process exitValue: " + exitVal);}//获取结束时间Date vEndTime = new Date();System.out.println("End sending ... " + sdf.format(vEndTime) + ",elapsed time: " + (vEndTime.getTime() - vStartTime.getTime())/1000 + " seconds.");}/** *  * @return * @throws Exception * @see java.util.concurrent.Callable#call() */@Overridepublic Object call() throws Exception {SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");Date vStartDt = new Date();GenericDaoImpl.update("INSERT INTO shell_proc_db_2_db_log(proc_id, run_msg, status, stat_cycle_id) VALUES(?, ?, ?, ?)", new String[]{procId, sdf.format(vStartDt) + " 文件开始导出", "2", statCycleId});db2FileExtractByProcInfo(srcDb, destDir, srcSql, fileNm.toUpperCase() + "_" + statCycleId + ".DEL");GenericDaoImpl.update("UPDATE shell_proc_db_2_db_log o SET o.status = 0, modify_dt = SYSDATE, run_msg = o.run_msg || CHR(13) || to_char(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') || ' 文件生成结束' WHERE proc_id = ? and stat_cycle_id = ?", new String[] { procId, statCycleId});GenericDaoImpl.update("UPDATE shell_proc_db_2_db_cfg o SET o.last_succ_cycle = ? WHERE o.proc_id = ?", new String[] { statCycleId, procId});return null;}}

获取配置的流程信息：

public static List<Map<String, Object>> 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<Map<String, Object>> arr = null;try {arr = (List<Map<String, Object>>) 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<Future> list = new ArrayList<Future>();/*List<Map<String, Object>> procs = DataSynchronizer.getProcIdForDb2Db();for (Map<String, Object> map : procs) {Callable<Object> db2Db = new Db2DbSync(map.get("PROC_ID").toString(), map.get("PROC_NAME").toString());pool.submit(db2Db);}*/List<Map<String, Object>> db2Files = DataSynchronizer.getProcIdForDb2File();for (Map<String, Object> map : db2Files) {Callable<Object> db2File = new Db2FileSync(map.get("PROC_ID").toString(), map.get("SRC_DB").toString(), map.get("DEST_DIR").toString(), map.get("SRC_SQL").toString(), map.get("FILE_NM").toString(), map.get("STAT_CYCLE_ID").toString());pool.submit(db2File);}pool.shutdown();}