purge/move historical data from spring batch tables

[giovanni.dalloglio]

Hi all,

We are considering how to storicize the 6 SB tables:

• the row number in that tables can't increase too much
• it has no interest for us the data 6-months-old

We plan to do it in a tasklet that delete all the rows that are no more useful.

Is this topic already resolved by somebody?

Someone have better ideas?

Thanks

G.D.

[ticino]

Hi,
I will have the same problem very soon. Will great to have a job that archive old DB entries and clean up the DB.
Somebody have already implemented something like this ?

ticino

[Elryk]

Hi,
I have the same requirement. Do you implement it like explained into the post https://jira.springsource.org/browse/BATCH-1747 ? Or have you used the Spring Batch Java API ?
Moreover do you know if a feature is plan de limit the historic number ?
Regards,
Elryj

[giovanni.dalloglio]

I'm sorry, we solved taht issue with a list of "delete from ...". I'm not satisfied, but it is enough to address the issue.

The specific issue (BATCH-1747) is closed as "won't fix": i assume that there are no more plans...

[Elryk]

Thanks Giovanni for this answer. Do you have please the SQL requests. You may save me a few hours.

[giovanni.dalloglio]

Here are our SQL statements.

Pay attention: I'm not sure that they are "prefects," we wanted to delete the old stuff, and we were not too accurate.

Try to validate them before re-use, to see if those statements are good for you.

DELETE FROM %PREFIX%B_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID IN (
SELECT STEP_EXECUTION_ID FROM %PREFIX%B_STEP_EXECUTION WHERE START_TIME < ? )


DELETE FROM %PREFIX%B_STEP_EXECUTION WHERE START_TIME < ? 	


DELETE FROM %PREFIX%B_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID in  (
SELECT JOB_EXECUTION_ID FROM %PREFIX%B_JOB_EXECUTION where CREATE_TIME < ?)


DELETE FROM %PREFIX%B_JOB_EXECUTION where CREATE_TIME < ?


DELETE FROM %PREFIX%B_JOB_PARAMS WHERE JOB_INSTANCE_ID in  (
SELECT JOB_INSTANCE_ID FROM %PREFIX%B_JOB_EXECUTION where CREATE_TIME < ?)
 

DELETE FROM %PREFIX%B_JOB_INSTANCE WHERE JOB_INSTANCE_ID NOT IN (SELECT JOB_INSTANCE_ID FROM %PREFIX%B_JOB_EXECUTION)

[Elryk]

Hi Giovanni,

Thanks a lot for those statements I started from. For more accuracy, I have refined those about the BATCH_STEP_EXECUTION_CONTEXT and the BACH_STEP_EXECUTION tables in order to use the job execution CREATE_TIME instead of the step execution START_TIME.
For all interested people, I have published on github a tasklet than remove spring batch historic :
https://github.com/arey/spring-batch...ryTasklet.java

I hope that may help