Spring Batch元数据表清除

Question

在MySQL DB中，1. spring batch是否提供了清除元数据表的方法? 2.还是需要手动清除和归档元数据表? 3.如何在没有清除机制的PROD环境中很好地维护spring元数据表？

需要这方面的指导！

Answer 1

我已经在这个问题上挣扎了很长一段时间，但是，还没有标准的实现。

然后我想出了一个我自己的存储过程，

我已经创建了自己的变量-用于清除过去6个月的数据AGO_SIX_MONTH_DATE

您可以使用自己的值。

CREATE OR REPLACE PROCEDURE SPRINGBATCH."SPRINGBATCH_METADATA_CLEANUP" IS
  FHANDLE           UTL_FILE.FILE_TYPE;
  DIR_NAME_IN       VARCHAR2(200);
  LOGFILE1_IN       VARCHAR2(200);
  TODAY_DATE        DATE;
  AGO_SIX_MONTH_DATE            DATE;
  v_code  NUMBER;
  v_errm  VARCHAR2(200);
  
  type JOB_INSTANCE_ID_T is table of SPRINGBATCH.BATCH_JOB_INSTANCE  .JOB_INSTANCE_ID%TYPE index by PLS_INTEGER;
  JOB_INSTANCE_IDS  JOB_INSTANCE_ID_T;
  
-- --------------------------------------------------------------------------------------------------------------
-- Data purge procedure to drop SPRING BATCH metadata tables 
-- -------------------------------------------------------------------------------------------------------------
BEGIN

-- get today's date and open a file to log procedure progress
     SELECT SYSDATE INTO TODAY_DATE FROM DUAL;
     
     -- variables for all old dates
     select add_months(TODAY_DATE,-6)   into AGO_SIX_MONTH_DATE from dual;

     
    --  NAME OF THE LOG FILE DIRECTORY
     DIR_NAME_IN := '/oracle/local/logs';  
    
    -- NAME OF THE LOG FILE
     LOGFILE1_IN := 'sprinbatch_data_purge'||TO_CHAR(TODAY_DATE,'DDMMYYYY_HH24MISS')||'.log';  

     FHANDLE := UTL_FILE.FOPEN(DIR_NAME_IN,LOGFILE1_IN,'a');
     UTL_FILE.PUT_LINE(FHANDLE,'SPRINGBATCH DATA purge Started');



    
    -- cache below query into - JOB_INSTANCE_IDS for referrences below
    --  select distinct JOB_INSTANCE_ID from SPRINGBATCH.BATCH_JOB_EXECUTION where CREATE_TIME < AGO_SIX_MONTH_DATE
    
    select distinct JOB_INSTANCE_ID bulk collect into JOB_INSTANCE_IDS from SPRINGBATCH.BATCH_JOB_EXECUTION where CREATE_TIME < AGO_SIX_MONTH_DATE ;
 
  



    BEGIN -- TRY 
    
    UTL_FILE.PUT_LINE(FHANDLE,'Purging SPring batch metadata tables started ');
    UTL_FILE.PUT_LINE(FHANDLE,'Logging time --> ' ||TO_CHAR(SYSDATE,'DDMMYYYY_HH24MISS'));
    




--1  BATCH_JOB_EXECUTION_CONTEXT  --> JOB_EXECUTION_ID
delete from SPRINGBATCH.BATCH_JOB_EXECUTION_CONTEXT  where JOB_EXECUTION_ID in 
(  select distinct JOB_EXECUTION_ID from SPRINGBATCH.BATCH_JOB_EXECUTION where CREATE_TIME < AGO_SIX_MONTH_DATE  ) ;
UTL_FILE.PUT_LINE(FHANDLE,'BATCH_JOB_EXECUTION_CONTEXT number of rows deleted --> ' || SQL%ROWCOUNT );


--2  BATCH_JOB_EXECUTION_PARAMS --> JOB_EXECUTION_ID
delete from SPRINGBATCH.BATCH_JOB_EXECUTION_PARAMS where JOB_EXECUTION_ID in 
(   select distinct JOB_EXECUTION_ID from SPRINGBATCH.BATCH_JOB_EXECUTION where CREATE_TIME < AGO_SIX_MONTH_DATE   ) ;
UTL_FILE.PUT_LINE(FHANDLE,'BATCH_JOB_EXECUTION_PARAMS number of rows deleted --> ' || SQL%ROWCOUNT );

--3 BATCH_STEP_EXECUTION_CONTEXT --> STEP_EXECUTION_ID  
delete from SPRINGBATCH.BATCH_STEP_EXECUTION_CONTEXT where STEP_EXECUTION_ID in 
(  select distinct STEP_EXECUTION_ID from SPRINGBATCH.BATCH_STEP_EXECUTION where START_TIME < AGO_SIX_MONTH_DATE and JOB_EXECUTION_ID in 
(   select distinct JOB_EXECUTION_ID from SPRINGBATCH.BATCH_JOB_EXECUTION where CREATE_TIME < AGO_SIX_MONTH_DATE   )  ) ;
UTL_FILE.PUT_LINE(FHANDLE,'BATCH_STEP_EXECUTION_CONTEXT number of rows deleted --> ' || SQL%ROWCOUNT );


--4 BATCH_STEP_EXECUTION -->  STEP_EXECUTION_ID , JOB_EXECUTION_ID,  START_TIME 
delete from SPRINGBATCH.BATCH_STEP_EXECUTION where START_TIME < AGO_SIX_MONTH_DATE ;  
UTL_FILE.PUT_LINE(FHANDLE,'BATCH_STEP_EXECUTION number of rows deleted --> ' || SQL%ROWCOUNT );

 
 --5   BATCH_JOB_EXECUTION -->  JOB_INSTANCE_ID , JOB_EXECUTION_ID , CREATE_TIME 
delete from SPRINGBATCH.BATCH_JOB_EXECUTION where CREATE_TIME < AGO_SIX_MONTH_DATE ; -->  JOB_INSTANCE_ID , JOB_EXECUTION_ID , CREATE_TIME 
UTL_FILE.PUT_LINE(FHANDLE,'BATCH_JOB_EXECUTION number of rows deleted --> ' || SQL%ROWCOUNT );
 
 
--6  BATCH_JOB_INSTANCE --> JOB_INSTANCE_ID 
     FOR rec IN 1 .. JOB_INSTANCE_IDS.LAST()
  LOOP
 delete from SPRINGBATCH.BATCH_JOB_INSTANCE  where JOB_INSTANCE_ID =  JOB_INSTANCE_IDS(rec).JOB_INSTANCE_ID  ;
 -- UTL_FILE.PUT_LINE(FHANDLE,'BATCH_JOB_INSTANCE number of rows deleted --> ' || SQL%ROWCOUNT );   
  END LOOP;
  
  
    commit;
    
    UTL_FILE.PUT_LINE(FHANDLE,'Purging SPring batch metadata tables Completed ');
    UTL_FILE.PUT_LINE(FHANDLE,'Logging time --> ' ||TO_CHAR(SYSDATE,'DDMMYYYY_HH24MISS'));
    
        
    -- END TRY 
    
    EXCEPTION -- CATCH 
    WHEN OTHERS THEN
    rollback;
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 200);
    UTL_FILE.PUT_LINE(FHANDLE,'Purging SPRING BATCH metadata tables failed ');
    UTL_FILE.PUT_LINE(FHANDLE,' ERROR_CODE --> '|| v_code || ' ERROR_MESSAGE --> '|| v_errm   );
    END; -- CATCH
    


    UTL_FILE.FCLOSE(FHANDLE);
    END SPRINGBATCH_METADATA_CLEANUP;
/

Answer 2

如果您不需要批处理作业的历史记录，我认为不时地清除它们是合法的。

Spring batch没有提供任何清除它的机制。

“如何在没有清除机制的生产环境中很好地维护spring元数据表”这取决于您可以管理存储多少数据，以及您的应用程序生成多少数据-每秒/分钟/小时/天1批？