How to Delete data from Spring Batch Metadata tables that are 90 days old. [BATCH-1747]

[spring-projects-issues]

Mariam opened BATCH-1747 and commented

I want to write stored procedure to delete records that are created in spring batch metadata. Can you please let me know the query to do it. I need to delete data that are 90 days old. We are using oracle as DB.

---

Affects: 2.1.5

[spring-projects-issues]

Mariam commented

I want to delete records from all Batch_XXX tables based on last_updated date in batch_job_execution table.

[spring-projects-issues]

Lucas Ward commented

I'm closing as won't fix, as this issue is much more appropriate to ask on the forum(it's not an issue, it's a question). However, I'm assuming you're having issues because of the foreign key constraints. You have three options:

1. remove the constraints
2. Delete in the correct order, such that children are deleted before parent, in the case of SB it would be:
   batch_step_execution_context
   batch_step_execution
   batch_job_execution_context
   batch_job_execution
   batch_job_params
   batch_job_instance

As long as you remove from the bottom up, it will be fine.

3. Update the constraints to support cascading delete.

[spring-projects-issues]

Elryk commented

For all interested people, I have published on Github a tasklet that removes the Spring Batch historic:
https://github.com/arey/spring-batch...ryTasklet.java
I hope that may help

[javaHelper]

@Elryk - https://github.com/arey/spring-batch...ryTasklet.java - This link is no more active now. Could you please share us the correct link or share code to delete Spring Batch Metadata tables 30 days old.

[arey]

Hi @javaHelper, here is the link: https://github.com/arey/spring-batch-toolkit/blob/spring-batch-toolkit-4.0.0/src/main/java/com/javaetmoi/core/batch/tasklet/RemoveSpringBatchHistoryTasklet.java

[hmojicag]

Spring batch metadata is actually ephemeral and data that is in COMPLETED or FAILED status seem to not be used anymore by anything, I would recommend deleting it to free up storage space, unless you actually want to keep those 90 days of historic batch processes for audit reasons.

I implemented this script which basically deletes everything that is not in use in the right order to avoid dependency problems:

DELETE FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID IN (SELECT BATCH_STEP_EXECUTION.STEP_EXECUTION_ID FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION WHERE STATUS IN ('COMPLETED','FAILED')));
DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION WHERE STATUS IN ('COMPLETED','FAILED'));
DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION WHERE STATUS IN ('COMPLETED','FAILED'));
DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION WHERE STATUS IN ('COMPLETED','FAILED'));
DELETE FROM BATCH_JOB_EXECUTION WHERE STATUS IN ('COMPLETED','FAILED');
DELETE FROM BATCH_JOB_INSTANCE WHERE JOB_INSTANCE_ID NOT IN (SELECT BATCH_JOB_EXECUTION.JOB_INSTANCE_ID FROM BATCH_JOB_EXECUTION);