Examples of copying data to the production-ready database

The following examples illustrate how we can copy tables from the production database to the production-ready data. It is important to remember that we cannot use the stagingcopy utility if requests for quotes (RFQs) are on our 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. Open the Utility server Docker container.

2. Type the following command:

   • (DB2) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype db2

   • (Oracle) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle

3. 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) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype db2 -log log_file_name

• (Oracle) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle -log log_file_name

If the sourcedb_passwd and destdb_passwd options are not provided, the user will be prompted for them on the command line.

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. Open the Utility server Docker container.

2. Type the following command:

   • (DB2) ./stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype db2

   • (Oracle) ./stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle

3. 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) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype db2 -cleanup_stage_db only

• (Oracle) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle -cleanup_stage_db only

To only copy data, specify the -cleanup_stage_db no parameter:

• (DB2)

  ./stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password 
       -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype db2 -cleanup_stage_db only
  ./stagingcopy.sh -scope _site_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name 
       -destdb_user user -destdb_passwd password -dbtype db2 -cleanup_stage_db only
  ./stagingcopy.sh -scope _site_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name 
       -destdb_user user -destdb_passwd password -dbtype db2 -cleanup_stage_db no
  ./stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name 
       -destdb_user user -destdb_passwd password -dbtype db2 -cleanup_stage_db no

• (Oracle)

  ./stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password 
       -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle -cleanup_stage_db only
  ./stagingcopy.sh -scope _site_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password 
       -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle -cleanup_stage_db only
  ./stagingcopy.sh -scope _site_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password 
       -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle -cleanup_stage_db no
  ./stagingcopy.sh -scope _merchant_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password 
       -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle -cleanup_stage_db no

Note: Deleting the site tables can affect the merchant tables. Always clean the merchant data first, followed by the site data. Then copy the site data first, and then the merchant data.

Example 3

After you clean the site tables from production-ready data, copy the site tables from production database to stage database.

1. Open the Utility server Docker container.

2. Type the following command:

   • (DB2) ./stagingcopy.sh -scope _site_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype db2

   • (Oracle) ./stagingcopy.sh -scope _site_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle

3. 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)

  • (AIX) (IBM i) (Linux)

    • 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

• (Oracle)

  • (AIX)

    • 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

(AIX) (Linux)

Example 4

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

1. Open the Utility server Docker container.

2. Type the following command:

   • (DB2) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype db2 -script_file stage_copy.sql

   • (Oracle) ./stagingcopy.sh -scope _all_ -sourcedb production_database_name -sourcedb_user user -script_file stage_copy.sql -sourcedb_passwd password -destdb staging_database_name -destdb_user user -destdb_passwd password -dbtype oracle -script_file stage_copy.sql

3. 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. Copy the following files to the database server:

• stage_copy.sql

• utilities_root/schema/9.0.0.0/dbtype/wcs.stage.trigger.sql

• utilities_root/schema/9.0.0.0/dbtype/wcs.droptrigger.sql

Where dbtype is db2 or oracle. (DB2) Run the following scripts:

1. (Linux) Enter 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 a DB2 command window and enter:

   • db2 -vtd# -f wcs.droptrigger.sql

   • db2 -vtd# -f stage_copy.sql

   • db2 -vtd# -f wcs.stage.trigger.sql

(Oracle) Run the following scripts:

1. Logon.

2. Open an SQLPlus window.

3. Connect as DBA and enter:

   • @wcs.droptrigger.sql

   • @stage_copy.sql

   • @wcs.stage.trigger.sql

Related concepts
The stagingcopy utility and the production database