0

I would like to periodically, e.g. once a year, archive a set of table rows from our DB2 9.7 database based on some criteria. So e.g. once a year, archive all EMPLOYEE rows that have a creation date older than 1 year ago?

By archive I mean that the data is moved out of the DB schema and stored in some other location, in a retrievable format. Is this possible to do?

user1340582
  • 19,151
  • 35
  • 115
  • 171

2 Answers2

3

System doesn't need to access archived data

If you don't need to access archived data by your program then I would suggest this:

  • create export (reference here) script, e.g.:
echo '===================== export started ';

values current time;
-- maybe ixf format would be better?
export to tablename.del of del 
select * from tablename
where creation_date < (current date - 1 year)
;
echo '===================== export finished ';
  • create delete db2 script, e.g.:
echo '===================== delete started ';
values current time;

delete from tablename.del of del 
where creation_date < (current date - 1 year)
;
commit;

echo '===================== delete finished ';
  • write batch script which calls everything copies new exported file to the safe location. When calling the script we want to ensure that delete is not done until data is placed on safe:
db2 connect to db user xx using xxx
db2 -s -vtf export.sql
7z a safe-location-<date-time>.7z tablename.del
if no errors till now:
   db2 -s -vtf delete.sql
  • register batch script as a cron job to do this automatically

Again, since deleting is very sensitive operation, I would suggest to have more than one backup mechanisms to ensure that no data will be lost (e.g. delete to have some different timeframe - e.g. delete older than 1.5 year).

System should access archived data

If you need your system to access archived data, then I would suggest one of the following methods:

  • export / import to other db or table / delete
  • stored procedure which does select + insert to other db or table / delete - for example you can adapt sp in answer nr. 3 in this question
  • do table partitioning - reference here
Community
  • 1
  • 1
Robert Lujo
  • 15,383
  • 5
  • 56
  • 73
  • This is great stuff Robert. Thanks! System does not need to access archived data. We are archiving old Log data that take unnecessary space in our transactional database, but due to the law they need to be stored somewhere. – user1340582 May 13 '13 at 06:10
0

Sure, why not? One fairly straight forward way is to write a stored procedure thab basically would:

  • extract all records of the a given table you wish to archive into a temp table,
  • insert those temp records into the archive table,
  • delete from the given table where the primary key is IN the temp table

If you wanted only a subset of columns to go into your archive, you could extract from a view containing just those columns, as long as you still capture the primary key in your temp file.

WarrenT
  • 4,502
  • 19
  • 27
  • Thanks for your response Warren. An SP could be a solution, but DB2 9.7 does not have archiving function built in? The data may need to be e.g. compressed and sent to a remote disk. Therefore I think a build in archiving function would be powerful. – user1340582 May 10 '13 at 06:43
  • Ah, I didn't catch that you wanted the archive to be outside of DB2. IBM's solution would probably be to use Optim. @Robert's approach (+1) is probably much simpler and cheaper, if you don't need access. How many tables are we talking about and what volume of data to archive? Why do you want to archive? – WarrenT May 11 '13 at 00:07
  • Optim? Sounds like an expensive approach then ;) We are going to archive between 2-5 tables, probably millions of rows. These are Logging related rows and take unnecessary space in transactional database, but due to the law these still need to be stored somewhere. – user1340582 May 13 '13 at 06:07