row retention period

[barasel1]

I have a requirement to delete row from a table, after it has passed 90 days from the date it was inserted.

One way is do it writing a program which would run every day to delete the 'expired' rows.

Is there any mechanism db2 provides to do this automatically?(something similar to trigger but based duration from a row inserted)

Thanks

 

[MarcLodge]

I don't think it can be done with a trigger as these work on table modification, and that's not what you are dealing with here.

I've not come across something that can do this and my initial feeling is that you will have to write a daily running program to perform the delete.

I'm happy to be proved wrong though.......

Marc

 

[datamodeler]

A stored procedure containing a "LOOP" statement that determines that it is a new day and calls one or more other procedures to do your deletions.

Not sure how much of a resource hog this might be but it does stay within the dbms.

If "LastDatePerformed" were written to some table, and this procedure were executed at dbms startup, then the process would even survive machine failure.

Mike Harris

Mike Harris
Data Modeling & Administration

 

[blom0344]

This requirement is also known as 'purging'.
With - for instance - ERP systems this is most commonly handled by special sets of business rules and performed batch-wise in the quiet hours. However this usually involves some sort of native 'scheduler' and sets of procedures. I think RDBMS vendors rely on applications to take care of it

Ties Blom

 

[Truusvlugindewind]

Upgrade to V9. Read about "range partitioning". Need I say more?

 

[Hattusas]

You could try scheduling a script.

One way is do it writing a program which would run every day to delete the 'expired' rows.

Try doing in that in DB2 Script Center.

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr