Examples of copying data to the production-ready database

The following examples illustrate how you can copy tables from the production database to the production-ready data. It is important to remember that you cannot use the stagingcopy utility if requests for quotes (RFQs) are on your production system.

Example 1

Note: Type the entire utility on a single line. The utilities are shown here, on more than one line, for presentation purposes only.

After you clean the production-ready data, copy the production database to the production-ready data with the scope set to all:

  1. SolarisLinuxAIXWindowsSet the PATH environment variables.
  2. SolarisLinuxAIXWindowsConfigure the database.
  3. SolarisLinuxAIXWindowsChange to the directory to which you want log files written.
  4. For IBM i OS operating systemThe log files will default to the following directory:
    • WC_userdir/instances/stagingcopy_{sourcedb_user} _{destdb_user}_{timestamp}.log)
  5. Type the following command:
    • DB2
      • For IBM i OS operating systemSolarisLinuxAIX.stagingcopy.sh -scope _all_ -sourcedb production_database_name -destdb staging_database_name -sourcedb_user user -destdb_user user
      • Windowsstagingcopy -scope _all_ -sourcedb production_database_name -destdb staging_database_name
    • Oracle
      • SolarisAIX.stagingcopy.sh -scope _all_ -sourcedb production_database_name -destdb staging_database_name dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • Windowsstagingcopy -scope _all_ -sourcedb production_database_name -destdb staging_database_name-dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
  6. Examine the stagingcopy_yyyy.mm.dd_hh.mm.ss.zzz.log file to verify that the utility was successful.
To specify the log file name and path, use the log file parameter:
  • DB2
    • For IBM i OS operating systemSolarisLinuxAIX.stagingcopy.sh -scope _all_ -sourcedb production_database_name -destdb staging_database_name -log log_file_name -sourcedb_user user -destdb_user user
    • Windowsstagingcopy -scope _all_ -sourcedb production_database_name -destdb staging_database_name -log log_file_name
  • Oracle
    • SolarisAIX.stagingcopy.sh -scope _all_ -sourcedb production_database_name -destdb staging_database_name -log log_file_name dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
    • Windowsstagingcopy -scope _all_ -sourcedb production_database_name -destdb staging_database_name -log log_file_name -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password

If you are using DB2 and are not logged on as the database administrator, you need to provide values for thesourcedb_user, sourcedb_passwd, destdb_user, and destdb_passwd options.

Example 2

After you clean the merchant tables from production-ready data, copy the merchant-related tables from the production database to production-ready data:

  1. Set the PATH environment variables.
  2. Configure the database.
  3. Change to the directory to which you want log files written.
  4. Type the following command:
    • DB2
      • For IBM i OS operating systemSolarisLinuxAIXstagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -sourcedb_user user -destdb_user user
      • Windowsstagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name
    • Oracle
      • SolarisAIXstagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • Windowsstagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
  5. Examine the stagingcopy_yyyy.mm.dd_hh.mm.ss.zzz.log file to verify that the utility was successful.
To only clean the production-ready data, specify the -cleanup_stage_db parameter:
  • DB2
    • For IBM i OS operating systemSolarisLinuxAIXstagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only -sourcedb_user user -destdb_user user
    • Windowsstagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only
  • Oracle
    • SolarisAIXstagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
    • Windowsstagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
To only copy data, specify the -cleanup_stage_db no parameter:
  • DB2
    • For IBM i OS operating systemSolarisLinuxAIXstagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no -sourcedb_user user -destdb_user user
    • Windowsstagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no
  • Oracle
    • SolarisAIXstagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
    • Windowsstagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
Tip: When you copy with the scope set to merchant, ensure that you copy the site scope data first. Otherwise, your copy will fail.

Example 3

After you clean the site tables from production-ready data, copy the site tables from production database to stage database.
  1. Set the PATH environment variables.
  2. Configure the database.
  3. Change to the directory to which you want log files written.
  4. Type the following command:
    • DB2
      • For IBM i OS operating systemSolarisLinuxAIXstagingcopy.sh -scope _site_ -sourcedb production_database_name -destdb staging_database_name -sourcedb_user user -destdb_user user
      • Windowsstagingcopy -scope _site_ -sourcedb production_database_name -destdb staging_database_name
    • Oracle
      • SolarisAIXstagingcopy.sh -scope _site_ -sourcedb production_database_name -destdb staging_database_name - dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • Windowsstagingcopy -scope _site_ -sourcedb production_database_name -destdb staging_database_name -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
  5. Examine the stagingcopy_yyyy.mm.dd_hh.mm.ss.zzz.log file to verify that the utility was successful.
Deleting the site tables can affect the merchant tables because of the cascade delete restriction. Clean the merchant data first, followed by the site data, and then copy the data:
  • DB2
    • For IBM i OS operating systemSolarisLinuxAIX
      • stagingcopy.sh -scope _site_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only -sourcedb_user user -destdb_user user
      • stagingcopy.sh -scope _site_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no -sourcedb_user user -destdb_user user
      • stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no -sourcedb_user user -destdb_user user
      • stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only -sourcedb_user user -destdb_user user
    • Windows
      • stagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only
      • stagingcopy -scope _site_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only
      • stagingcopy -scope _site_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no
      • stagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no
  • Oracle
    • SolarisAIX
      • stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • stagingcopy.sh -scope _site_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
    • Windows
      • stagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • stagingcopy -scope _site_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db only -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • stagingcopy -scope _site_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • stagingcopy -scope _merchant_ -sourcedb production_database_name -destdb staging_database_name -cleanup_stage_db no -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
SolarisLinuxAIXWindows

Example 4

Generate the following script to clean and copy the production database to the stage database with scope all.

For IBM i OS operating system This example does not apply, since the -script option is not supported.

  1. Set the PATH environment variables.
  2. Configure the database.
  3. Change to the directory to which you want log files written.
  4. Type the following command:
    • DB2
      • SolarisLinuxAIXstagingcopy.sh -scope _all_ -sourcedb production_database_name -destdb staging_database_name -script_file stage_copy.sql -sourcedb_user user -destdb_user user
      • Windowsstagingcopy -scope _all_ -sourcedb production_database_name -destdb staging_database_name -script_file stage_copy.sql
    • Oracle
      • SolarisAIXstagingcopy.sh -scope _all_ -sourcedb production_database_name -destdb staging_database_name -script_file stage_copy.sql dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
      • Windowsstagingcopy -scope _all_ -sourcedb production_database_name -destdb staging_database_name -script_file stage_copy.sql -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password
  5. Examine the stagingcopy_yyyy.mm.dd_hh.mm.ss.zzz.log file to verify that the utility was successful.

The stagingcopy utility generates the stage_copy.sql script to clean and copy the database.

DB2 Run the following script:
  1. WindowsLog on as the database administrator.
  2. SolarisLinuxAIXEnter su - WC_non_root_user, whereWC_non_root_user is the non-root user under which WebSphere Commerce runs. The value of WC_non_root_user is typically wasuser.
  3. Open a DB2 command window and enter:
    • db2 -vtd# -f stage_copy.sql
Oracle Run the following script:
  1. Logon.
    • WindowsLog on as the database administrator.
    • SolarisAIXEnter su - WC_non_root_user, where WC_non_root_user is the non-root user under which WebSphere Commerce runs. The value of WC_non_root_user is typically wasuser.
  2. Open an SQLPlus window.
  3. Connect as DBA and enter: @stage_copy.sql