I'm still looking for some help on how to archive records from our database. We are running Progress 7.3c with Symix 5.2 as the front end. I know about the dump & reload, I just don't understand how I'm supposed to remove the old data for archiving. Our database is so big that when ever a big job is run we get a data buffer overflow. I already have the buffers set to 25000, & can't go any higher. I need to get the old data off! I really appreciate any help I can get. Et
Still looking for help
- Thread starter Etsme
- Start date
TomBascom
Curmudgeon
Presuming that Symix lacks an archive or purge feature you have two options:
1) Delete it.
2) Archive it.
In both cases you need to be able to identify the data that you wish to remove. If you can write a query to list those records you're halfway there. To remove the stuff you no longer need use DELETE. To make it efficient "chunk" the DELETEs into groups of 100 or so records.
If you need to archive the data you can either copy to an archive database or just dump it to .d files depending on why you need to archive it and how you plan to access it if you need it again sometime.
Having said all that... what is this "buffer overflow" error that you speak of? Does it have an error number? I have a feeling that it isn't what you think it is.
In any event -B 25,000 is pretty small. Even for v7.3 (You wouldn't be running the infamous 7.3c01 release would you?) You're long overdue for an upgrade.
1) Delete it.
2) Archive it.
In both cases you need to be able to identify the data that you wish to remove. If you can write a query to list those records you're halfway there. To remove the stuff you no longer need use DELETE. To make it efficient "chunk" the DELETEs into groups of 100 or so records.
If you need to archive the data you can either copy to an archive database or just dump it to .d files depending on why you need to archive it and how you plan to access it if you need it again sometime.
Having said all that... what is this "buffer overflow" error that you speak of? Does it have an error number? I have a feeling that it isn't what you think it is.
In any event -B 25,000 is pretty small. Even for v7.3 (You wouldn't be running the infamous 7.3c01 release would you?) You're long overdue for an upgrade.
What we are looking to do with archiving though is allow reports to seemlessly access both the original database and archival on a needed basis.
We will put all data that can be archived (we have built an extensive plan of what CAN be archived), and put the data in the archive database.
We need to have users be able to run a report for any time frame our database has history for... so if production has kept 7 years of data, the remaining 12 years can be accessed without a user being aware of the secondary connection.
Is this something that can be done easily?
Both databases will have the same structure, so essentially we would be prefer to make the archive database RO (if that could be realistic).
Is this feasible?
We will put all data that can be archived (we have built an extensive plan of what CAN be archived), and put the data in the archive database.
We need to have users be able to run a report for any time frame our database has history for... so if production has kept 7 years of data, the remaining 12 years can be accessed without a user being aware of the secondary connection.
Is this something that can be done easily?
Both databases will have the same structure, so essentially we would be prefer to make the archive database RO (if that could be realistic).
Is this feasible?
TomBascom
Curmudgeon
There is no simple, easy, no work involved, magical "make it look seamless" way to do what you want.
But if you have complete control of the reporting code then you could, in theory anyway, write your queries to pull data from both databases and act appropriately.
But I've got to wonder... what's the point of archiving if you still need to access the data? It seems like a rather pointless exercise.
But if you have complete control of the reporting code then you could, in theory anyway, write your queries to pull data from both databases and act appropriately.
But I've got to wonder... what's the point of archiving if you still need to access the data? It seems like a rather pointless exercise.
tamhas
ProgressTalk.com Sponsor
I have to echo Tom's thoughts here. In order to archive the data you have to do one of two things -- move it to a similarly structured database or move it into a differently named table in another database. In the former case, you will need to modify every report which needs access to the historical data to use aliases to pull from both sets of data. In the latter case you will have to modify all the queries to pull from both tables. A lot of work and a bunch of fiddly stuff to do to get it to work and you still have the same amount of data on-line. Why not just leave the data where it is and access it when you need it?
- (my, DBA, benefit though) Database Refreshing - on a monthly basis we intend to refresh our databases on TEST and DEV using data from Production. It will act as a time saving mechanism as we begin to automate this procedure.
- (Server admin benefits) Backups of the archive databases will occur weekly, while the non-archive Databases will stay within their same daily backup routine (time saving)
From an application development standpoint, I haven't been involved long enough to fully gauge the benefit for them or the end users.
Matt
- (Server admin benefits) Backups of the archive databases will occur weekly, while the non-archive Databases will stay within their same daily backup routine (time saving)
From an application development standpoint, I haven't been involved long enough to fully gauge the benefit for them or the end users.
Matt
taqvia
Member
It doesnt seem to be feasible to have your Application connecting to two diff database(Prod & and lets say Archive) It wont make much of difference as the application has to scan through two diff database. If i have completely understood your issue then there might be two or three probabale solutions to it.
1> Identfiy the criteria for Purging data(lets say account has not been touched /no updates made for last n months) Purge all this records and load them into the new archive database.Have a dedicated client application (replicated code of production ) running against the archive database and when the user needs to have a look at the historical data simply login to these new client session/application . In this case there will be no code change required.
2> If you wish that the user should not be aware of the diff database for pulling out the records or running the query cretae a new table in the existing databse with minimal number of fileds say ACCTNBR which will hold values for all the records purged (say if you are purging records based on the ACCTNBR then all Purged acctnbr will have entry in this new table.) If the applcation finds an entry for the ACCTNBR which the user is looking for then connect to the archive database for pulling out the records.This will require a code change and to be frank smart code change as it would be connecting to a simalar database structure and needs to be handled as there cannot be same table.fieldname for the connected database. An include file can be wriiten to connect the new database such as "connect <db path>".
3> Third way might be to not to create any new database but purge the records in form of .d or xml and store it at location x. when the system identifies that the user is requesting for a purged data it will simply run a routine to find the corresponding .d or .xml and load it on runtime. (In this case the data will be added back to the production database.) Also it may also lead to performance issue depending upon ur network limitations.
Arshad
1> Identfiy the criteria for Purging data(lets say account has not been touched /no updates made for last n months) Purge all this records and load them into the new archive database.Have a dedicated client application (replicated code of production ) running against the archive database and when the user needs to have a look at the historical data simply login to these new client session/application . In this case there will be no code change required.
2> If you wish that the user should not be aware of the diff database for pulling out the records or running the query cretae a new table in the existing databse with minimal number of fileds say ACCTNBR which will hold values for all the records purged (say if you are purging records based on the ACCTNBR then all Purged acctnbr will have entry in this new table.) If the applcation finds an entry for the ACCTNBR which the user is looking for then connect to the archive database for pulling out the records.This will require a code change and to be frank smart code change as it would be connecting to a simalar database structure and needs to be handled as there cannot be same table.fieldname for the connected database. An include file can be wriiten to connect the new database such as "connect <db path>".
3> Third way might be to not to create any new database but purge the records in form of .d or xml and store it at location x. when the system identifies that the user is requesting for a purged data it will simply run a routine to find the corresponding .d or .xml and load it on runtime. (In this case the data will be added back to the production database.) Also it may also lead to performance issue depending upon ur network limitations.
Arshad
ron
Member
I share Tom's concern about what you are trying to do. Writing your own logic to interfere with the data in a "package" system is fraught with danger - because you have a high risk of creating referential integrity problems.
I also have considerable concern that you have chosen the best solution to your problem.
Why don't you step right back and post an explanation of your system - and what you believe to be your problem. Maybe some wise and experienced people can suggest a better way to tackle this.
And, finally, I also share Tom's concern that you are still using such an old version of Progress. Many things have been improved. Many bugs have been fixed. You really should bite the bullet and move up to a newer, supported, release of Progress. :awink:
Ron.
I also have considerable concern that you have chosen the best solution to your problem.
Why don't you step right back and post an explanation of your system - and what you believe to be your problem. Maybe some wise and experienced people can suggest a better way to tackle this.
And, finally, I also share Tom's concern that you are still using such an old version of Progress. Many things have been improved. Many bugs have been fixed. You really should bite the bullet and move up to a newer, supported, release of Progress. :awink:
Ron.
At this point our application SX.e is customized to the point of being hard to recognize. Our team of developers if both experienced and capable (4 people with almost 10 years each in Progress -- in a company of 140 people), that is not a concern. They understand the logic and how it flows, and what can and cannot be archived.
So while we use a standard ERP package, if any person familiar with the product came to our company, it would be hard to recognize. We have a significant amount of information that is rarely needed, and segregating this to another area seems like the best means to reduce backups and assist in speeding up and reducing report size.
I am in no way concerned about their ability in my department.
Why do this outside my already stated reason. We work in distribution of software, which readily discontinues products, however history of these transactions can't be purged (some non-licensing software can). So all of these products while no longer current need require an extensive number of notes and other information will rarely be required, but must be retained.
We aren't doing a simple dump of data because within the programs (and RTB) we can find the programs that manage the information we need, and also any associated information to coincide with that. So while we could dump pos for seven years back, however, since product info doesn't have a date to key off of, we would have to dump all the products as well (and effectively creating two records in different locations). We have a discontinue date to work with, but as I mentioned above, discontinued doesn't mean we can delete it.
Some of this could be stated incorrectly, however, my intent when posting was not to put myself in a position defending the developers actions. Believe me they are more than capable.
-- and I agree with this being a departure from the original topic, I appreciate your comments.
The Progress version, we are working towards, we are in the midst of installing Optio, and it is not a small project.
So while we use a standard ERP package, if any person familiar with the product came to our company, it would be hard to recognize. We have a significant amount of information that is rarely needed, and segregating this to another area seems like the best means to reduce backups and assist in speeding up and reducing report size.
I am in no way concerned about their ability in my department.
Why do this outside my already stated reason. We work in distribution of software, which readily discontinues products, however history of these transactions can't be purged (some non-licensing software can). So all of these products while no longer current need require an extensive number of notes and other information will rarely be required, but must be retained.
We aren't doing a simple dump of data because within the programs (and RTB) we can find the programs that manage the information we need, and also any associated information to coincide with that. So while we could dump pos for seven years back, however, since product info doesn't have a date to key off of, we would have to dump all the products as well (and effectively creating two records in different locations). We have a discontinue date to work with, but as I mentioned above, discontinued doesn't mean we can delete it.
Some of this could be stated incorrectly, however, my intent when posting was not to put myself in a position defending the developers actions. Believe me they are more than capable.
-- and I agree with this being a departure from the original topic, I appreciate your comments.
The Progress version, we are working towards, we are in the midst of installing Optio, and it is not a small project.
tamhas
ProgressTalk.com Sponsor
It seems to me that the issues have been covered here. Yes, it is certainly possible to create a history table and to move records of a certain age into it. If this table is in a separate area or even database, that can mean reduced dump and load times or possibly even reduced backup times in the case of the multiple database approach, assuming that one backs up the history database less frequently. But, use of two different tables like this will, however, require modifying any report where you desire to combine the two data sets and the modification may well be non-trivial.
Alternatively, you could copy everything from the active table to the history table and then purge data from the active table. That gives you the potential ambiguity of duplicate records and the complexity of handling the update to make sure all changes are applied to history, but reports could presumably use one or the other of the two tables and the modifications would be simpler. While this might seem to have less programming involved, it isn't an approach I would recommend since it presents some serious issues about the tables getting out of sync.
The other option, of course, is to do nothing and leave the data combined. Generally, that would be my recommendation.
Alternatively, you could copy everything from the active table to the history table and then purge data from the active table. That gives you the potential ambiguity of duplicate records and the complexity of handling the update to make sure all changes are applied to history, but reports could presumably use one or the other of the two tables and the modifications would be simpler. While this might seem to have less programming involved, it isn't an approach I would recommend since it presents some serious issues about the tables getting out of sync.
The other option, of course, is to do nothing and leave the data combined. Generally, that would be my recommendation.