Oracle quick export of data files, oracle export of data

Oracle quick export of data files, oracle export of data

There are many data file generation methods, but most of them are inefficient. For large data warehouses, efficient file export is an urgent requirement:

Here, the shell + java method is used to integrate the data file generation tool written by the old bear, which is 70% more efficient than the normal export method:

Shell file generation script:

Java call Script: The implement Callable interface implements a multi-threaded call method:

/*** @ Title: Db2FileSync. java * @ Package etl. etlUtils * @ Description: TODO * @ author Lubin Su * @ date 12:43:09 on January 1, July 15, 2015 * @ 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. con Current. callable; import etl. dao. impl. genericDaoImpl;/***** @ author Lubin Su * @ date 12:43:09 on January 1, July 15, 2015 * @ 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: constructor </p> * <p> Description: </p> * @ param procId * @ param srcDb * @ pa Ram 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;}/*** the data table is issued as a file * @ author lubinsu * @ date July 15, July 12, 2015 * @ param procId *@ Throws UnsupportedEncodingException */public void db2FileExtractByProcInfo (String srcDb, String descDir, String srcSql, String fileNm) throws UnsupportedEncodingException {// obtain the current time 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]; // environment variable String [] env = {"lubins U_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:/usr/sbi N:/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:/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_A MERICA. 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", "lc1_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 "," cl1__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/prod Uct/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(cmd.exe c (cmd, env);} catch (IOException e) {e. printStac KTrace ();} InputStream stderr = prc. getErrorStream (); InputStreamReader isr = new InputStreamReader (stderr); BufferedReader br = new BufferedReader (isr); String line = null; // print the returned information try {while (line = br. readLine ())! = Null) System. out. println (line);} catch (IOException e) {e. printStackTrace ();} try {exitVal = prc. waitFor ();} catch (InterruptedException e) {e. printStackTrace ();} // print the return number if (exitVal> 0) {System. out. println ("Process exitValue:" + exitVal);} // obtain the end time 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) + "file start Export", "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') | 'end of file generation '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 ;}}

Obtain the configuration process information:

Public static List <Map <String, Object> getProcIdForDb2File () {// obtain the current time 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 ;}

Call test:

Public static void main (String [] args) throws IOException, InterruptedException {int taskSize = 13; // create a thread pool ExecutorService pool = Executors. newFixedThreadPool (taskSize); // create multiple tasks with returned values // 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 ();}

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.