Hi

We are running DB2 v8.1.3 on Win2k with Sp4. I have a specific question with regard to data archival policy.. We have around 100 tables with complex relationships around it. Now the client requirement is that records that are 6 months old can be archived so that he can manage the resource well.

While we understand the process i.e identify data to be archived, copy the data and finally delete the data, however, what is still not clear is that should we extract the data into a flat file or a history table (which is a replica of base table)? What is the preferred way to do this? and what are the pros-cons of the same?

Any help is greatly appreciated...thanks.

I would go with archives on DB2 tables. If they want to keep it, eventually someone will want to access it. It would be a good idea if you could define some maximum retention period for the data. The archive tables could possibly be denormalized to have fewer tables that contain archive data, and which will also allow easier ad-hoc queries.

Many thanks! for your quick response. Here's what I can think off. Please let me know if this is ok.

* Create a archive database which is a exact replica of production database. Therefore, I will create all the 100 tables with FK relationships around.

* On a regular basis (every six months), move the rows (that fulfill the selection criteria)from production to archive database as defined by the data to be archived stage. This should preserve Referential Integrity (RI) intact. Besides, selective data from production gets deleted.

My question is;
* How should the above logic implemented? eg: SQL scripts, SQL Proc or Java program

* If the answer to the above question is SQL Scripts then how do we rollback to make sure there are no orphaned records?

* When the data model changes drastically then how can we restore from the archive files?

Please help.

Thanks.

Hi,
If your version is v8.1 or >.
You can use the "Replication center"

Abel.