Oracle Quick Export Data file

There are many ways to generate data files, but most of them are inefficient, and for large data warehouses, efficient export files are urgently needed:

Here, the Shell+java method integrates the data file generation tools written by the old Bear, which is 70% more efficient than the normal export methods:

Shell file Generation script:

Java Call script: Here implement callable interface, implementation of multi-threaded call mode:

/** * @Title: Db2filesync.java * @Package etl.etlutils * @Description: TODO * @author lubin Su * @date July 15, 2015 PM 12: 43:09 * @version V1.0 */package etl.etlutils;import java.io.bufferedreader;import java.io.ioexception;import Java.io.I Nputstream;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 July 15, 2015 PM 12:43:09 * @version V1.0 */public class Db2file Sync 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 * @param destDir * @param srcsql * @param filenm * @param statcycleid */public D B2filesync (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;} /** * Data Sheet issued as a file * @author Lubinsu * @date July 12, 2015 * @param procid * @throws unsupportedencodingexception */public vo ID db2fileextractbyprocinfo (String srcdb, String Descdir, String srcsql, String filenm) throws unsupportedencodingexception {//Get current time SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");D ate Vstarttime = new Date (); System.out.println ("Start sending ... "+ Sdf.format (vstarttime)); string[] cmd = new string[5];//environment variable 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 "," 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 has NEW MAIL] "," Logname=css_int "," Classpath=::/usr/java6_64/lib "," ssh_connection=132.228.78.73 49796 132.228.27.132 "," 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 (Ioexcep tion e) {e.printstacktrace ();} InputStream stderr = Prc.geterrorstream (); InputStreamReader ISR = new InputStreamReader (stderr); BufferedReader br = new BufferedReader (ISR); String line = null;//Prints the return information in 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 return number if (Exitval > 0) {System.out.println ("Process exitvalue:" + exitval);} Get 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 Ex ception {SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy/mm/dd HH:mm:ss");D ate 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});d B2fileextractbyprocinfo (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;}}

Get the process information for the configuration:

public static list<map<string, object>> Getprocidfordb2file () {//Get current time simpledateformat SDF = new SimpleDateFormat ("YyyyMMdd HH:mm:ss");D ate 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 "+" W HEN O.run_type = 2 then\n "+" To_char (to_date (' "+ vnowdatestr +" ', ' yyyymmdd hh24:mi:ss ')-1, ' yyyymm DD ') \ n "+" when O.run_type = 3 then\n "+" To_char (to_date (' "+ vnowdatestr +" ', ' Yyyym MDD 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.pro c_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;}

To invoke the test:

public static void Main (string[] args) throws IOException, interruptedexception {int tasksize = 13;//Create a thread pool Executorservi Ce pool = executors.newfixedthreadpool (tasksize);//Create multiple tasks with return 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 ());p ool.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 ());p ool.submit (db2file); Pool.shutdown ();}

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Quick Export Data file