Results 1 to 7 of 7
  1. #1
    Join Date
    May 2016
    Location
    Wellington
    Posts
    21
    Provided Answers: 1

    Lightbulb Answered: Archiving Old DB2 data to a different database

    Hello!
    We have a huge database which we archive to keep only the last 5yrs of data. The 5-10yrs old data is moved to a different database and older data is removed. We already completed this last year, so this year we just need to move one year worth of data(2010) to archive database and remove 2005 data from archive database.

    We plan to
    1. Export 2010 data from Live database
    2. Export 2011-2016 data from Live database
    3. Load replace only 2011-2016 data to Live database

    Similarly
    4. Export 2005 data from Archive db
    5. Export 2006-2009 data from Archive
    6. Load Replace 2006-2009 data on archive db
    7. Load insert 2010 data on archive database.

    We did not want to use Insert/Delete. Is there any other method this could be done?
    FYI, this is DB2 on AIX

    Thanks in Advance

  2. Best Answer
    Posted by stiruvee

    "You can try "Load from cursor" command to load data into new table. After loading data into new table, drop old table and rename new table. This is better option than export+load.

    1. Load data into new table using "Load from cursor"
    2. Drop RI constraints on old table
    3. Drop old table
    4. Rename new table
    5. Recreate indexes and RI constraints.

    Satya..."


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello,

    You should use range partitioning for your huge tables. Detaching old partitions / attaching new ones must be much more efficient.
    Why don't you use it?
    Regards,
    Mark.

  4. #3
    Join Date
    May 2016
    Location
    Wellington
    Posts
    21
    Provided Answers: 1
    Thanks for your reply Mark!
    This database is very old and just last year we have decided to archive the old data as it became huge. Also this database is sadly still on V8
    Last edited by abhivelu; 05-02-16 at 18:42.

  5. #4
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Another method is to use HPU instead of Export, but given your company has failed to upgrade to a supported DB2-version then it's unlikely your company purchased the High-Performance-Unload for that old version of DB2.
    Otherwise export and load are your main options. Although I recall that DB2 V8.2 supported union-all-views which allowed a primitive rollout scheme. But if that was not already implemented for this application then it is a waste of money to retrofit it compared to the costs of upgrading to a supported DB2 version with range-partitioning and row-compression and possibly column-organized tables.
    You did not define what 'huge' means to you in terms of petabytes or terabytes or gigabytes, although that is a relevant fact.

  6. #5
    Join Date
    May 2010
    Location
    India
    Posts
    82
    Provided Answers: 2
    You can try "Load from cursor" command to load data into new table. After loading data into new table, drop old table and rename new table. This is better option than export+load.

    1. Load data into new table using "Load from cursor"
    2. Drop RI constraints on old table
    3. Drop old table
    4. Rename new table
    5. Recreate indexes and RI constraints.

    Satya...
    Last edited by stiruvee; 05-03-16 at 06:33.

  7. #6
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Load from cursor was available in V8.1 and V8.2, although I had reliability issues with it back then. If there is no IP connectivity between source and target it is not an option. But when connectivity exists, and with correct scripting that parallelises the actions then it is great (at least with later versions of DB2).

  8. #7
    Join Date
    May 2016
    Location
    Wellington
    Posts
    21
    Provided Answers: 1
    Hi Satya,
    I am testing out with load from cursor and seems to be a good plan! Thank you so much!

    Abhi

Tags for this Thread

Posting Permissions

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