Spring Batch Meta-Data tables Purging

Question

In MySQL DB, 1. Does spring batch provides a way to purge meta-data tables? 2. Or we need to purge and archive the meta-data tables manually? 3. how spring meta-data tables are maintained well in PROD environment with out Purging mechanism?

Need guidance on this!

Eckman

Answer 1

I have been struggling with this for a long time but, there is no standard implementation for this.

Then I came up with a my own stored procedure ,

I have created my own variable - for clearing last 6 months data AGO_SIX_MONTH_DATE

You can use your own value.

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;
/

Phocis

Answer 2

If you don't need history of your batch jobs, I think it's legit to purge them from time to time.

Spring batch doesn't provide any mechanisms to purge it.

"how spring meta-data tables are maintained well in PROD environment with out Purging mechanism" It depends how much data can you manage to store and how much data your application generates - 1 batch per second/minute/hour/day?

Etienne