Spring Batch Tables Purging

Question

What is the best way to purge Spring Tables?

Does Spring provides any APIS for purging? or, Do We need to execute delete statements on all Spring Batch Tables?

Knocker

Answer 1

I created a procedure to clean Up data before a date given.

    DELIMITER $$

DROP PROCEDURE IF EXISTS purgeJobsTableBefore 
$$

CREATE PROCEDURE purgeJobsTableBefore (
    IN createTime VARCHAR(100)
)
BEGIN

DELETE
FROM
   batch_job_execution_context 
WHERE
   JOB_EXECUTION_ID IN 
   (
      SELECT
         JOB_EXECUTION_ID 
      FROM
         batch_job_execution 
      WHERE
         create_time <= createTime
   );

DELETE
FROM
   batch_job_execution_params 
WHERE
   JOB_EXECUTION_ID IN 
   (
      SELECT
         JOB_EXECUTION_ID 
      FROM
         batch_job_execution 
      WHERE
         create_time <= createTime
   );

DELETE
FROM
   batch_step_execution_context 
WHERE
   step_execution_id IN 
   (
      SELECT
         step_execution_id 
      FROM
         batch_step_execution 
      WHERE
         JOB_EXECUTION_ID IN 
         (
            SELECT
               JOB_EXECUTION_ID 
            FROM
               batch_job_execution 
            WHERE
               create_time <= createTime
         )
   );

DELETE
FROM
   batch_step_execution 
WHERE
   JOB_EXECUTION_ID IN 
   (
      SELECT
         JOB_EXECUTION_ID 
      FROM
         batch_job_execution 
      WHERE
         create_time <= createTime
   );

DELETE
FROM
   batch_job_execution 
WHERE
   create_time <= createTime;

DELETE
FROM
   batch_job_instance 
WHERE
   JOB_INSTANCE_ID < (
   SELECT
      MIN(JOB_INSTANCE_ID) 
   FROM
      batch_job_execution 
   WHERE
      create_time >= createTime);

END;
$$

You just have to call the procedure with a date to clear up data before that date.

CALL purgeJobsTableBefore('2020-05-22');

Aesthetics

Answer 2

i have been struggling with this for a ling 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.

The solution is at below link -

Spring Batch Meta-Data tables Purging

Zither

Answer 3

Another option would be to set batch.data.source.init=true in your properties this would mean that you drop and re-create the tables on each new deployment..

Raceme

Answer 4

So far yes, you need to delete data by yourself. Spring Batch tables usually contain important data - so it's unusually that somebody wants to delete it from production system.

Iasis

Thanks ,On git hub I see below one github.com/arey/spring-batch-toolkit/blob/master/src/main/java/… ...Looks like RemoveSpringBatchHistoryTasklet is doing purge kind of activity, Any Idea about this? – Knocker

Yes, it's not spring framework's code, but you can use it to purge your data. – Iasis

Answer 5

After a long research I've discover a clean way.

1. Using this library. The source code is public on github where you can see an example of use reading this test.

2. Manually: First be sure that your server is not running jobs. Then if you are using sql server you can run the Official drop schema script in your DB here. Or if you are using other db you can find an script for you here.Then you can recreate your schema in two ways:

   1. Using official script to do it manually. Take a look here if you are using sql server or search your best option here.
   2. Turning on spring.batch.initialize-schema property to always. Then restart your server to automatic spring batch schema recreation.

spring.batch.initialize-schema=always

Intercross