Run the Database Cleanup utility in a staging environment

We can use the Database Cleanup utility to cleanup data in a staging environment when the staging and production environment databases are synchronized.

Whenever we want to run the Database Cleanup utility in the staging environment, repeat the following steps to ensure that the staging and production databases remain synchronized during the cleanup operation. To run the utility on only our production server, you do not need to complete the following steps.

Important: Before you work with the SQL files in this task, copy the files from our Docker utility container to the database node.

Procedure

1. Run the Staging Propagation utility to propagate the latest changes to the production environment and ensure that the staging and production environment databases are synchronized. For more information about running this utility, see stagingprop utility.

2. Stop the WebSphere Commerce staging environment. By stopping the server, you prevent changes from occurring in the staging environment database before the database cleanup operation completes.

3. Run the Database Cleanup utility on your production environment to remove any that is marked for delete (markfordelete=1). For more information about running the utility and the appropriate command-line parameters to use for the environment, see Database Cleanup utility command script.

4. Remove the staging triggers from the staging database. Run the wcs.droptrigger.sql SQL file against the staging database to drop these triggers: (Linux) Running the following SQL file can take 5-10 minutes to complete. Change the owner and group ownership of the file from wasuser/wasgroup to dbuser/dbgroup so that we can run this file as the dbuser. Run the file as the dbuser so that we can edit the trigger files for the staging database to add tables to the staging list as part of the cleanup operation. Run the drop trigger file as the dbuser only for the purpose and duration of the database cleanup operation. After your run the file for the cleanup operation, change the file ownership back to the original wasuser owner.

   1. Open a shell prompt window as the root users, and run the following command to change the ownership of the drop trigger SQL file:

      • (DB2) chown dbuser/dbgroup WC_installdir/schema/db2/wcs.droptrigger.sql

      • (Oracle) chown dbuser/dbgroup WC_installdir/schema/oracle/wcs.droptrigger.sql

   2. Run the following command in the shell prompt window to switch users so that we can run the drop trigger SQL file as the dbuser:

      su - dbuser

   3. Open a connection to the database and run the following SQL file:

      • (DB2) WC_installdir\schema\db2\wcs.droptrigger.sql

        For example, db2 -tdf wcs.droptrigger.sql

      • (Oracle) WC_installdir\schema\oracle\wcs.droptrigger.sql

   4. In the shell prompt window, run the following command to change the ownership of the file back to wasuser owner:

      • (DB2) chown wasuser/wasgroup WC_installdir/schema/db2/wcs.droptrigger.sql

      • (Oracle) chown wasuser/wasgroup WC_installdir/schema/oracle/wcs.droptrigger.sql

5. Run the Database Cleanup utility in the staging environment to remove any object record that is marked for delete (markfordelete=1). Use the same parameters to run the utility that we used previously. When the utility completes cleaning the database, the staging and production environments are synchronized.

6. Create or re-create the staging triggers for the staging database. To create or re-create these triggers, run the wcs.stage.trigger.sql SQL file against the staging database: (Linux) Running the following SQL file can take 5-10 minutes to complete. Change the owner and group ownership of the file from wasuser/wasgroup to dbuser/dbgroup so that we can run this file as the dbuser. Run the file as the dbuser so that we can edit the trigger files for the staging database to add tables to the staging list as part of the cleanup operation. Run the add trigger file as the dbuser only for the purpose and duration of the database cleanup operation. After your run the file for the cleanup operation, change the file ownership back to the original wasuser owner.

   1. Open a shell prompt window as the root users, and run the following command to change the ownership of the add trigger file:

      • (DB2) chown dbuser/dbgroup WC_installdir/schema/db2/wcs.stage.trigger.sql

      • (Oracle) chown dbuser/dbgroup WC_installdir/schema/oracle/wcs.stage.trigger.sql

   2. Run the following command in the shell prompt window to switch users so that we can run the add trigger file as the dbuser:

      su - dbuser

   3. Open a connection to the database and run the following SQL file:

      • (DB2) WC_installdir\schema\db2\wcs.stage.trigger.sql

        When we are running this file, specify that the SQL code in the file uses '#' as a terminator instead of the standard terminator. For example, db2 -td# -vf wcs.stage.trigger.sql

      • (Oracle) WC_installdir\schema\oracle\wcs.stage.trigger.sql

   4. In the shell prompt window, run the following command to change the ownership of the file back to wasuser owner:

      • (DB2) chown wasuser/wasgroup WC_installdir/schema/db2/wcs.stage.trigger.sql

      • (Oracle) chown wasuser/wasgroup WC_installdir/schema/oracle/wcs.stage.trigger.sql

7. Start the WebSphere Commerce Staging environment.

Results

Your staging and production databases are synchronized and the databases cleaned. The staging database is set up to listen for changes and to record any changes in the STAGLOG table.

Related concepts
Database Cleanup utility

Related reference
Database Cleanup utility command script