Progress Database Reorganization

As you use your database, adding and deleting information from it over time, it becomes sluggish and needs to be reorganized. Reorganization (Dump & Load) makes all of your data contiguous again, optimizing performance, and resetting the high water mark. You take all of the data out of the database, create a new database structure, and then put all of the data into that new structure.

Other reasons you might want to dump & load are:

To create another version or copy of the database
Migration to a different version of Progress or a different operating system
Schema update
The greatest benefit of a dump and load is increased performance.
How often should you dump & load?

How often you should perform this maintenance task is a matter of opinion; Progress® has their recommendations and Dan Foreman has his. Both of these opinions are based on the percentage of scatter in the database. We recommend a scheduled dump & load based on the historical usage of your database. How do you know the historical usage of your database? You need to monitor performance and usage statistics. Statistics will show you how much data you are putting into, removing, and moving around in your database. Statistics will also show you how efficient the database is managing your information, and how well it is performing for your end users. If you don’t maintain historical usage information, you obviously cannot use that to determine when a dump & load is needed. When this is the case, you can run a database analysis report and compare the scatter factor to the default recommendations. If your performance has become sluggish and you haven’t ever done a dump & load – obviously you need to. The creation date of the database will tell you when the last dump & load was performed – if ever.
How long will it take?

Dependent on the size of your database, a dump & load can be a time consuming task – and you need to make sure that you allocate enough time to do it. There are things that you can do to decrease the time required – you can automate the task, use the largest buffer space that you can get away with, do a binary dump & load, or – if you have Progress v9, you can use storage areas to segregate the dynamic data from the static data thus dumping and loading areas of the database rather than the entire database.
Issues you may encounter

If you have a modified schema, inadequate resources, customized code, or corrupt data, you may run into problems. Before you start a dump & load, you need to make sure that you have the resources necessary for successful completion. These include adequate security, adequate disk space, adequate memory, and enough time to do it in – and time to recover if the dump & load fails for some reason. If progress encounters errors while loading the data, it may be necessary to reconstruct the data to get it to load properly. Issues we’ve encountered
A stripped version of progress preventing access to the Data Administration module
Table size greater than 1GB (using older versions of Progress)
Inadequate disk space
STGET Errors
CRC Errors
Methods of Dump & Load

There are various methods that you can use to dump & load your database, including:
data dictionary utilities
The slowest of these is the data dictionary utilities because they are single threaded utilities. Bulkload enables automation of the data dictionary process, making it a bit faster but still single threaded. Export/Import requires that you write the code to do the dump & load. Binary load is the fastest – and our preferred method. We also write custom scripts to automate the process as much as possible.

You can increase the speed of the dump & load by running the database in no-integrity mode. However, if the database crashes during the dump or load, the database will be unrecoverable and you will need to start over.

How to dump & load


Perform all month end/year end maintenance tasks
Ensure that you have adequate resources to perform the dump & load (you will need diskspace equal to about 160% the size of your database; you also need to ensure that you have adequate memory available)
Notify users of the planned maintenance schedule
Verify Year & Century parameters
Save a copy of all parameter and structure files
Put all background queue jobs on hold
Shutdown background queue & data collection processes
Shutdown all non-essential processes that are running on the server
Disable User Logons
Shutdown the database
Backup the database

Dump the schema
Dump the tables
Delete all files on the filesystem where the database will reside
Check the filesystem for inconsistency; fix if needed

Create a new empty database & connect to it
Load the schema
Load the tables
Review log files for any errors reported
Backup the database

Rebuild Indexes
Backup the database

Start the database
Run a database analysis report and compare to the one that you created in “Preliminary Activities” to ensure that everything that was dumped was also loaded.
Verify that multiple users are able to connect to the database and that critical tasks can be performed
Perform normal “Reboot Verification” procedures
Restart background queues & data collection processes
Restart all non-essential processes that were suspended
Remove the Hold flag from all background queue jobs
Record the date, database size, and any issues encountered into a log file
Record the High Water Mark for future monitoring
After the database has been running a few days, compare new reports from background queue log to an old background queue log to see if reports are being executed any faster after the dump & load.
If your preventive maintenance schedule includes copying a multi-volume production database to a backup/test environment – and you’ve modified the structure of your production database – you may need to change your copy script and the structure file in the backup/test environment.
In summary

A dump & load should be an essential part of your maintenance plan. It is a time consuming process, and it can get pretty technical. The alternative is that your database will, eventually, slow to a crawl.