Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: data archival policy

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  3. #3
    Join Date
    Sep 2003
    Posts
    218

    data archival policy

    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.

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Hi,
    If your version is v8.1 or >.
    You can use the "Replication center"

    Abel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •