用groovy写的简单文件生成程序。主要上在AIX上通过GROOVY生成gz包形式的明细文件。挺好用的。写起来也简单
import groovy.sql.Sql import java.io.Serializable; import java.util.concurrent.LinkedBlockingQueue; import java.util.concurrent.ThreadPoolExecutor; import java.util.concurrent.TimeUnit; //取出可以执行的任务 一次取5个 DetailSql=""" select * from WI.IDX_DETAIL_LOAD_CFG t1, WI.IDX_DETAIL_LOAD_INFO t2 where t1.IDX_ID = t2.IDX_ID and t1.STS_ID =1 order by SERIAL_ID fetch first 10 rows only """ // and t1.STS_ID =1 public static class ThreadPoolTask implements Runnable,Serializable{ private static final long serialVersionUID = 0; //保存任务所需要的数据 def serial; def sql; def filename; def path def runfile; def errorfile; public ThreadPoolTask(SERIAL,SQL,FILENAME,PATH,RunFile,ErrorFile){ this.serial = SERIAL; this.sql = SQL; this.filename = FILENAME; this.path = PATH; this.runfile = RunFile; this.errorfile = ErrorFile; doAction(serial,2); } public void run(){ //处理一个任务 // println "${threadPoolTaskData.SERIAL_ID} zhunbei kaigao" String strSql=String2GB(sql); // String strSql=sql; if(testSQL(strSql,serial,errorfile)){ //println "testSQL" FileTemplemt(strSql,filename,path); OverAction("${filename}.gz",serial,runfile); //println "file over" } } //生成完文件并转移成功后更改数据库标示 synchronized OverAction(fileName,SERIAL,RunFile){ def OKFile=""" update WI.IDX_DETAIL_LOAD_CFG set FILE_NAME='${fileName}', STS_ID=3, ETL_TIMESTAMP = current timestamp where SERIAL_ID=${SERIAL} WITH UR """ try { def SQLCONN= Sql.newInstance("jdbc:db2://10.154.145.39:50000/hndpdb", "用户名", "密码", "com.ibm.db2.jcc.DB2Driver") SQLCONN.execute(OKFile); RunFile<<new java.util.Date().toLocaleString()+" file:${fileName} is OK /n"; SQLCONN.close(); }catch(e){ if(e.getSQLState() =="57033"){ println String2GB("OverAction()死锁${SERIAL}"); Thread.sleep(1000*30); //休息30秒 继续 OverAction(fileName,SERIAL,RunFile); } } } //生成文件失败后更新数据库标志 synchronized doAction(SERIAL,flag){ def sqlFile=""" update WI.IDX_DETAIL_LOAD_CFG SET STS_ID=${flag}, ETL_TIMESTAMP = current timestamp where SERIAL_ID=${SERIAL} WITH UR """ //加上锁表等待判断 try { def SQLCONN= Sql.newInstance("jdbc:db2://10.154.145.39:50000/hndpdb", "用户名", "密码", "com.ibm.db2.jcc.DB2Driver") SQLCONN.execute(sqlFile); SQLCONN.close(); }catch(e){ if(e.getSQLState() =="57033"){ println String2GB("doAction()死锁${SERIAL}"); Thread.sleep(1000*30); //休息30秒 继续 doAction(SERIAL,flag); } } } //转化字符串 private static String String2GB(str){ // 换机器的时候要判断一下当前机器上默认的字符集是什么 // System.getProperty("file.encoding") return new String(str.getBytes("GB18030")); // return new String(str.getBytes("ISO8859-1"),"GBK"); } private static String SMSString2GB(str){ return new String(str.getBytes("ISO8859-1"),"GBK"); } //生成的sh文件模板 def FileTemplemt={ sql,fileName,path -> //判断是否存在对应的目录 File fileDesc=new File("/etl_data_01/deep/${path}"); if (!fileDesc.exists()) { //不存在的时候 //主动创建一个目录 fileDesc.mkdir(); } File file=new File(fileName+".sh"); file.write(""" db2 connect to hndpdb user hndp using hndp99 db2 "${sql}" > ${fileName} db2 connect reset gzip ${fileName} rm /etl_data_01/deep/${path}${fileName}.gz mv ${fileName}.gz /etl_data_01/deep/${path} rm ${fileName}.sh """); Thread.sleep(2000); //赋权 def proc1 ="chmod +x ${fileName}.sh".execute(); proc1.waitFor(); Thread.sleep(1000); def proc2 = "${fileName}.sh".execute(); proc2.waitFor(); } //SMS短信通知 def SMS2USER={strMsisdn,strDesc -> def SQLCONN= Sql.newInstance("jdbc:db2://10.154.145.16:50000/hnbasdb", "用户名", "密码", "com.ibm.db2.jcc.DB2Driver"); String sql = " insert into WEBADMIN.MESSAGE (MESSAGE_ID,MSISDN,MESSAGE,STATE) values (char(current date)||' '||char( current time),'" + strMsisdn + "','" + strDesc + "','0')"; SQLCONN.execute(sql); SQLCONN.close(); } //测试sql语句是否可执行 //出错将错误记录到日志并修改标示为4 def testSQL={strSql,SERIAL,ErrorFile-> //不加1 >2 碰到大文件会直接内存溢出死掉 看来firestRow的实现和我理解的有差距 strSql=strSql.toUpperCase(); int wherepos=-1; if(strSql.indexOf("WHERE")>-1){ wherepos=strSql.indexOf("WHERE"); } if(wherepos>-1){ strSql=strSql[0..wherepos-1]+" WHERE 1<>1 AND " +strSql[wherepos+6..-1] } // strSql=strSql.toUpperCase().replaceAll("WITH.*UR",""); // strSql=strSql+" fetch first 1 rows only "; // strSql=strSql+" AND 1<>1 WITH UR "; def SQLCONN= Sql.newInstance("jdbc:db2://10.154.145.39:50000/hndpdb", "用户名", "密码", "com.ibm.db2.jcc.DB2Driver") try{ def row = SQLCONN.firstRow(strSql) }catch (e){ ErrorFile<<String2GB(new java.util.Date().toLocaleString()+" Error SQL=${strSql} /n Error =${e.getMessage()} /n SERIAL_ID=${SERIAL} /n--------------------/n"); doAction(SERIAL,4); SQLCONN.close(); String Errordesc="明细文件生成出错! 出错序列号:${SERIAL},报错时间:"+new java.util.Date().toLocaleString()+""; //插入短信信息到仓库短信表,实现短信通知 SMS2USER("XXXXXXXXXXX",SMSString2GB(Errordesc)); //张健滢 SMS2USER("XXXXXXXXXXX",SMSString2GB(Errordesc)); //孙 return false; } SQLCONN.close(); return true; } } /** 程序入口 **/ File ErrorFile=new File("Error.txt"); File RunFile=new File("Run.txt"); Object lock =new Object(); ThreadPoolExecutor threadPool = new ThreadPoolExecutor(3, 4, 600, TimeUnit.SECONDS, new LinkedBlockingQueue<Runnable>(5), new ThreadPoolExecutor.CallerRunsPolicy()); while(true){ // println "dodo ="+new java.util.Date().toLocaleString(); sql = Sql.newInstance("jdbc:db2://10.154.145.39:50000/hndpdb", "用户名", "密码", "com.ibm.db2.jcc.DB2Driver") println "jinlai" sql.eachRow(DetailSql, { String sql=it.ETL_SCRIPT; String SERIAL=it.SERIAL_ID; String FILENAME="${it.TIME_ID}_${it.AREA_ID}_${it.IDX_ID}.txt"; String PATH=it.PATH_URL; threadPool.execute(new ThreadPoolTask(SERIAL,sql,FILENAME,PATH,RunFile,ErrorFile)); } ); sql.close(); // println "zhunbeixiuxi" Thread.sleep(5*1000); System.gc(); // }
