Oracle快速匯出資料檔案，oracle匯出資料

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<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();}

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。