Running the Database Cleanup utility in a staging environment

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

Whenever you want to run the Database Cleanup utility in your staging environment, repeat the following steps to ensure that the staging and production databases remain synchronized during the cleanup operation. If you want to run the utility on only your 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 your 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 HCL 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 your 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:
    LinuxRunning 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 you can run this file as the dbuser. Run the file as the dbuser so that you 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
      • Oraclechown dbuser/dbgroup WC_installdir/schema/oracle/wcs.droptrigger.sql
    2. Run the following command in the shell prompt window to switch users so that you 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

      • OracleWC_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
      • Oraclechown 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 you used previously.
    When the utility completes cleaning the database, your 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:
    LinuxRunning 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 you can run this file as the dbuser. Run the file as the dbuser so that you 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
      • Oraclechown 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 you 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 you 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

      • OracleWC_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
      • Oraclechown wasuser/wasgroup WC_installdir/schema/oracle/wcs.stage.trigger.sql
  7. Start the HCL 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.