Running the Database Cleanup utility in a staging environment

You can run the Database Cleanup utility in the staging environment. To run the cleanup operation against a staging environment database, the staging and production environment databases must be synchronized.

Note: Whenever you run the Database Cleanup utility in your staging environment, repeat the following steps to ensure that the staging and production environment 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.

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 server. 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:
    WindowsRunning the following SQL file can take 5-10 minutes to complete. 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
    SolarisLinuxAIXRunning 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
    For IBM i OS operating systemDB2Running the following SQL file can take 20-30 minutes. Run the WC_installdir/bin/removestagetriggers_iseries.sh file from the Q-Shell (STRQSH). Usage:
    /QIBM/ProdData/CommerceServer70/bin/removestagetriggers_iseries.s h hostname schema dbuser dbpwd instance_root_directory
    Where
    hostname
    The hostname of the machine that includes the database.
    schema
    The name of the database schema, usually this value is also the name of the instance.
    dbuser
    The user profile that owns the schema, usually this value is also the name of the instance.
    dbpwd
    The password for the user profile.
    instance_root_directory
    The fully qualified directory of the instance home. Usually this value is the format /QIBM/UserData/CommerceServer70/instances/instance_name
  5. Run the Database Cleanup utility on the staging server 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:
    WindowsRunning the following SQL file can take 5-10 minutes to complete. 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
    SolarisLinuxAIXRunning 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
    For IBM i OS operating systemDB2Running the following SQL file can take 90-120 minutes. Run the WC_installdir/bin/addstagetriggers_iseries.sh file from the Q-Shell (STRQSH). Usage:
    /QIBM/ProdData/CommerceServer70/bin/addstagetriggers_iseries.s h hostname schema dbuser dbpwd instance_root_directory
  7. Start the WebSphere Commerce Staging server.

Results

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