File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Design and the fly likes Audit table purge logic Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » Design
Bookmark "Audit table purge logic" Watch "Audit table purge logic" New topic

Audit table purge logic

Rahul Ba
Ranch Hand

Joined: Oct 01, 2008
Posts: 206
I have task for Purging the data from database. This data is from Audit table, hence it does not have any relationship with the other tables. There are around 40 audit tables and it has timestamp. I want to derive best strategy to purge the data. I have idea as below, please let me know your opinion.

My Strategy:
1. I will write a Store procedure which will delete data from all 40 tables which are more than 1 months old.
2. I will write a java program to call SP and inturn Java program will be called by scheduler.
3. The Schedule will trigger at 15 of each month.

What is your opinion on the same, is my steps can be improved in tuns of performance.

Thank you !
Heena Agarwal
Ranch Hand

Joined: Dec 25, 2013
Posts: 262
You have 40 audit tables. Purging audit data may take a considerable amount of time. So you might want to have more than a single job that can run in parallel.

Is 15th of every month also the time when the database is not going to be very busy? That might also be a thing to consider.
Rahul Ba
Ranch Hand

Joined: Oct 01, 2008
Posts: 206
Thanks Heena, It make sense !
I agree. Here's the link:
subject: Audit table purge logic
It's not a secret anymore!