Dear Please Help Me
- Thread starter Mike
- Start date
Is it for QAD?
TomBascom
Curmudgeon
There are many subtle details -- whole books have been written on the topic. But at a broad level:
1) Make an empty database with the same schema as your production db.
2) Decide which tables need to be archived -- "all tables" is unusual. If you think you need to archive all of your tables you likely have not thought about it enough.
3) Decide on the criteria -- perhaps it is something like "orders that were shipped at least 12 months ago".
4) Determine how to apply that criteria to the tables selected in step #2. It may not be as simple as "WHERE datefield <= X". You might, for instance, have parent-child relationships to consider like orders to order-lines where only one table has the key field available.
5) Write some code to accomplish the archive & purge. Personally I prefer to connect the two databases and use BUFFER-COPY rather than dumping to .d files and then loading them. Something like:
Notice that the code above is restartable -- it can be aborted at any time and then re-run to completion later. A huge mistake that people make is to try to turn the archive & purge operation into a db transaction. Doing that is a very bad mistake. You end up with a huge bi file and gigantic lock table. If you take the "big transaction" approach you will eventually be in a position where you cannot successfully run your archive and purge.
1) Make an empty database with the same schema as your production db.
2) Decide which tables need to be archived -- "all tables" is unusual. If you think you need to archive all of your tables you likely have not thought about it enough.
3) Decide on the criteria -- perhaps it is something like "orders that were shipped at least 12 months ago".
4) Determine how to apply that criteria to the tables selected in step #2. It may not be as simple as "WHERE datefield <= X". You might, for instance, have parent-child relationships to consider like orders to order-lines where only one table has the key field available.
5) Write some code to accomplish the archive & purge. Personally I prefer to connect the two databases and use BUFFER-COPY rather than dumping to .d files and then loading them. Something like:
Code:
/* totally untested psuedo code...
*/
for each src.order exclusive-lock where src.order.order-date < ( today - 365 ):
for each src.order-line exclusive-lock where src.order-line.orderNum = src.order.orderNum:
create dst.order-line.
buffer-copy src.order-line dst.order-line. /* archive */
delete src.order-line /* purge */
end.
create dst.order.
buffer-copy scr.order dst.order. /* archive */
delete src.order. /* purge */
end.Notice that the code above is restartable -- it can be aborted at any time and then re-run to completion later. A huge mistake that people make is to try to turn the archive & purge operation into a db transaction. Doing that is a very bad mistake. You end up with a huge bi file and gigantic lock table. If you take the "big transaction" approach you will eventually be in a position where you cannot successfully run your archive and purge.
Last edited:
cj_brandt
Active Member
if you are trying to purge over 50% of a table, i have had better luck with saving off the data I want to keep. Drop the table and then load the data back in.
if you are using Progress 9, then you probably don't have access to SQL drop table command, so you would need to rename the table and create the empty table and load the data back in. Then slowly purge the data from the renamed table.
if you are using Progress 9, then you probably don't have access to SQL drop table command, so you would need to rename the table and create the empty table and load the data back in. Then slowly purge the data from the renamed table.