This blog promotes knowledge sharing through experience and collaboration. For more product information, visit our WebSphere Commerce CSE page. For easier navigation, utilize the Categories to find posts that match your interest.
Maintaining Staging and Production Databases Clean and Consistent
The cleanup task during maintenance of a staging and production environments always needs mutual consideration. You should not run a cleanup task without considering data consistency between the two servers.
Note: the following information assumes that your staging and production servers have data-consistency (or in sych).
If you're going to perform a cleanup job, most of the time (if not always) start with the production environment. Then, proceed with the staging one. A common mistake is to run the cleanup in staging and then run stagingprop to propagate the changes to production. Although in some cases it may work, it is not the correct approach as there may be data dependencies in production that will not allow the deletion of objects.
For instance, consider cleaning catalog entry objects from the CATENTRY table. If you marked for delete catalog entry objects in staging and run dbclean, later stagingprop may run into errors when propagating the deletes to production. If you have order items referencing a catalog entry, the catalog entry cannot be deleted due to its foreign key constraint to other child tables such as ORDERITEMS. The staging server doesn't exhibit this constraint because it doesn't hold orders' data.
The following is a recommended procedure. Note that the steps are for your consideration, and they may vary depending on your maintenance needs.
1. Run stagingprop
Run stagingprop to propagate the latest changes from staging to production. After successful completion, stop the staging server to prevent any further changes to the database before cleanup completes.
2 Run dbclean on production
Run dbclean on production to remove the records ready to be purged. You can find a list of out-of-the-box objects handled by dbclean at Database Cleanup utility objects. Keep in mind, as mentioned earlier, that not all object marked for delete will be deleted. For example, if you are deleting catalog entry objects:
3. Drop staging triggers
After running dbclean in production, we move to staging and drop the WebSphere Commerce staging triggers. You can find the out-of-the-box scripts in the following locations:
If you have custom tables that have their own triggers, make sure the script you're running drops them as well.
4. Run dbclean on staging
We dropped the staging triggers in the previous step to avoid dbclean deletes from getting registered in the STAGLOG table for propagation. Now, we can run dbclean on staging.
Upon successful completion, the data should be consistent with production. The exceptions would be the production records that are still referenced by other objects. These will eventually be removed in later cleanup attempts.
5. Re-create staging triggers and start the server
Recreate the staging triggers using the appropriate script.
If you have custom tables that have their own triggers, make sure the script you're running creates them as well
Last, start the staging server.