DB2分区表的生成程序

一个java的分区表生成程序,用于生成裁剪和初始化SQL用于自动化调度:

package ibm.imc.util;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class TPAlterSTMTSMaker {
	
	public static String base_date = "01/01/2013";
	public static int period = 8;
	public static String tabname = "EM";
	public static String path="C:\\EMDoNotDeleteThisFile\\";
	

	public static void main(String[] args) throws ParseException {
		//TPAlterSTMTSMaker.generateCreateStmt();
		//System.out.println("------");
		//System.out.println(TPAlterSTMTSMaker.generateAttachStmt());
		//System.out.println("------");
		//System.out.println(TPAlterSTMTSMaker.generateDetachStmt());
		TPAlterSTMTSMaker.updateContent("C:\\EMDoNotDeleteThisFile\\db2.txt", "connect to um user db2admin using db2admin@", false);
		TPAlterSTMTSMaker.updateContent("C:\\EMDoNotDeleteThisFile\\db2.txt", TPAlterSTMTSMaker.generateDetachStmt(), true);
		TPAlterSTMTSMaker.updateContent("C:\\EMDoNotDeleteThisFile\\db2.txt", TPAlterSTMTSMaker.generateAttachStmt(), true);
		System.out.println("Finished.");
	}
	
	public static String getDateStr()
	{
		SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");//定义格式   
	    Timestamp now = new Timestamp(System.currentTimeMillis());//获取系统当前时间 
	    String date = df.format(new Date());
	    return date;
	}
	
	public static long getDateDiff(Date date1, Date date2)
	{
		long diff = date1.getTime()/86400000 - date2.getTime()/86400000;
		return diff;
	}
	
	public static String getPartitionStmts(int day) throws ParseException
	{
		SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		String current_date = getDateStr();
		String current_date_add_1 = getSpecifiedDayAfterNDays(current_date,day);
		String current_date_add_2 = getSpecifiedDayAfterNDays(current_date_add_1,1);
		long diff = getDateDiff(df.parse(current_date_add_1),df.parse(base_date));
		int offset = (int) (diff % period);
		
		String sql="partition day" + offset + " starting '" + current_date_add_1 + "' inclusive ending '"
				+ current_date_add_2 + "' exclusive";
		
		return sql;
	}
	
	public static String getSpecifiedDayAfterNDays(String date, int n)
	{		
		SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		if (n <= 0) {return date;}
		String current_date = getSpecifiedDayAfter(date);
		for (int i = 1; i < n; i++) {
			current_date=getSpecifiedDayAfter(current_date);
		}
		
		return current_date;
	}

	public static String generateAttachStmt() throws ParseException
	{
		String sql="";
		String lineSeparator = System.getProperty("line.separator", "/n");
		sql += "alter table " + tabname + " attach " + getPartitionStmts(1) + " from table em_tp_reuse_tb@";
		sql += lineSeparator;
		sql += "commit@";
		sql += lineSeparator;
		sql += "set integrity for " + tabname + " allow write access immediate checked@";
		sql += lineSeparator;
		sql += "commit@";
		return sql;
	}
	
	public static String generateDetachStmt() throws ParseException
	{
		String sql="";
		String lineSeparator = System.getProperty("line.separator", "/n");
		
		SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		String current_date = getDateStr();
		String current_date_add_1 = getSpecifiedDayAfterNDays(current_date,1);
		long diff = getDateDiff(df.parse(current_date_add_1),df.parse(base_date));
		int offset = (int) (diff % period);
		
		sql += "alter table " + tabname + " detach partition day" + offset + " into table em_tp_reuse_tb@";
		sql += lineSeparator;
		sql += "commit@";
		sql += lineSeparator;
		sql += "import from '" + path + "null.del' of del replace into em_tp_reuse_tb@";
		sql += lineSeparator;
		sql += "commit@";
		return sql;
	}
	
	public static void generateCreateStmt() throws ParseException
	{
		String sql="";
		String lineSeparator = System.getProperty("line.separator", "/n"); 
		sql += "create table " + tabname + "(date timestamp,id char(16),site char(128),device char(128),loc char(128),value char(32)) partition by range(date) (";
		for(int i = 0; i < period; i++) {
			String sql_p = getPartitionStmts(i);
			sql += sql_p;
			if (i != period - 1) {sql += ",";}
		}
		sql += ") compress yes @";
		
		sql += lineSeparator;
		
		sql += "create index IDX_EM_DATE on EM(DATE) PARTITIONED @";
		
		System.out.println(sql);
	}
	
	public static boolean updateContent(String name,String content,boolean append){
        boolean res = true;
        String path = name;
        File file = new File(path);        
        try {
            if (!file.exists()) {
                file.createNewFile();
            }
            FileWriter writer = new FileWriter(file,append);
            if(append){
                content = System.getProperty("line.separator")+content;
            }
            writer.write(content);
            writer.flush();
            writer.close();
        } catch (IOException ex) {
            res = false;
            ex.printStackTrace();
        }
        return res;
    }
	       /**
	     * 获得指定日期的前一天
	     * 
	     * @param specifiedDay
	     * @return
	     * @throws Exception
	     */
	    public static String getSpecifiedDayBefore(String specifiedDay) {//可以用new Date().toLocalString()传递参数
	        Calendar c = Calendar.getInstance();
	        Date date = null;
	        try {
	            date = new SimpleDateFormat("MM/dd/yyyy").parse(specifiedDay);
	        } catch (ParseException e) {
	            e.printStackTrace();
	        }
	        c.setTime(date);
	        int day = c.get(Calendar.DATE);
	        c.set(Calendar.DATE, day - 1);

	        String dayBefore = new SimpleDateFormat("MM/dd/yyyy").format(c
	                .getTime());
	        return dayBefore;
	    }

	    /**
	     * 获得指定日期的后一天
	     * 
	     * @param specifiedDay
	     * @return
	     */
	    public static String getSpecifiedDayAfter(String specifiedDay) {
	        Calendar c = Calendar.getInstance();
	        Date date = null;
	        try {
	            date = new SimpleDateFormat("MM/dd/yyyy").parse(specifiedDay);
	        } catch (ParseException e) {
	            e.printStackTrace();
	        }
	        c.setTime(date);
	        int day = c.get(Calendar.DATE);
	        c.set(Calendar.DATE, day + 1);

	        String dayAfter = new SimpleDateFormat("MM/dd/yyyy")
	                .format(c.getTime());
	        return dayAfter;
	    }

}

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*