Announcement Announcement Module
Collapse
No announcement yet.
purge/move historical data from spring batch tables Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • purge/move historical data from spring batch tables

    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.

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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...

        Comment


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

          Comment


          • #6
            Try with this statements

            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.

            Code:
            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)

            Comment


            • #7
              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

              Comment

              Working...
              X