Example: Propagating filtered promotion data to the production database

Use the following example code to help you understand how to use a staging filter configuration file and command-line parameters to configure the stagingprop utility. By using a staging filter configuration file, you can publish only the data that matches a custom staging filter condition. In this example, the stagingprop utility is configured to publish only promotion data that is included within a specific promotion folder.

Use this example to help you create staging filters that can filter the data to be published by any parameter, such as store ID or category. By using staging filters, you can promote data to production whenever you need to publish a specific set of data between regular large staging operations.

When you filter the objects to propagate, you reduce the impact to site performance from the stagingprop process by reducing the amount of data that is being propagated. To filter the data to be propagated, you can configure the utility with a staging filter configuration file that defines how the utility processes data. When you run the stagingprop utility, you can then use command-line parameters to specify the configuration XML file and the object that is to be filtered. For more information, see Filtering data for the stagingprop utility to propagate.

Before you begin

Ensure that you complete the following tasks:

In your authoring or staging environment, use Management Center to create or change promotions and add the promotions into a promotion folder. When you save the changes for the promotions, you create a record for the promotions within the STAGLOG database table. This record identifies that the records are changed and ready to be propagated to the production environment.
In Management Center, view the list of all promotion folders in your store. Configure the columns that display in the list to display the folder Unique ID value. Record the ID for the promotion folder that includes the promotions that you want to propagate to the production environment. This example uses an ID of 10001.
In the production environment, ensure that any catalog or marketing assets that are associated with the promotions that you want to publish are available.
In your staging environment, update the database triggers for your database. Manually run the promotionFilterByFolderTriggers.sql script file to generate the triggers on the relevant table. You need to complete this task only once for a table. These triggers replace the existing database triggers on your staging server for the certain promotion tables.

About this example

This example demonstrates how to use a staging filter to propagate only the new and changed promotions that are included within a specific promotion folder. This example uses a sample staging filter configuration file to define the SQL that the stagingprop utility uses to select the data to be propagated. This SQL also defines how the utility propagates the data. This configuration file overrides the default stagingprop utility behavior during the propagation phase of the staging operation. This override causes the utility to retrieve and use the SQL that is defined within the configuration file. The utility uses the SQL to propagate the filtered data. In this example, the promotions within a specified folder. This sample configuration file, promotionFilterByFolder.xml, is in the following directory

WC_installdir/components/foundation/samples/Staging/Promotion
WC_installdir\components\foundation\samples\Staging\Promotion
WCDE_installdir\components\foundation\samples\Staging\Promotion

Note: Only the promotions that are within the specified folder when you run the stagingprop utility are propagated to production. When the stagingprop utility runs with the sample promotionFilterByFolder.xml file, the utility only creates or changes the promotions in the production environment. No promotions are deleted. If you want to remove an active promotion in production, use the Promotions tool to deactivate the promotion.

To specify that the stagingprop utility is to use a staging filter configuration file, you must include the parameter -filterconfigfile in the command line when you run the utility. The value for the parameter must be the filepath and name for the configuration file.

To identify the specific set of data to be filtered, you can either identify the data set within the configuration file, or include a substitution parameter, {customfilterparametername}, in the configuration file. If you use the substitution parameter, you can then include the -customfilter% parameter in the command line when you run the stagingprop utility. The value of the two parameters must match so that the value for the parameter can be passed from the command line into the SQL that is defined in the configuration file. In this example, the custom filter parameters {customfilterfolderid} and -customfilterfolderid are used. You must specify the folder ID of the folder that includes the promotions to propagate as the value for the -customfilterfolderid parameter in the command line.

After you run the stagingprop utility to propagate filtered data, you must update the database triggers for the staging tables. Since only certain records are propagated, you must override the default database triggers to indicate that only the records for the filtered data are propagated. The SQL file, promotionFilterByFolderTriggers.sql, includes the SQL database triggers that you run in this example is in the following directory:

WC_installdir/components/foundation/samples/Staging/Promotion/dbtype
WC_installdir\components\foundation\samples\Staging\Promotion\dbtype
WCDE_installdir\components\foundation\samples\Staging\Promotion\dbtype

Where dbtype is the database for your environment. SQL triggers are available for both the DB2 and Oracle databases.

Procedure

In a command-line utility, go to the following directory:
- - WC_installdir/bin
  - WC_installdir\bin
  - WCDE_installdir\bin
- - WC_installdir/bin
  - WC_installdir\bin
Enter the following command to run the stagingprop utility with the appropriate staging filter parameters and values:
- - stagingprop.sh -scope _all_ -sourcedb staging_database_name -destdb production_database_name -log log_file_name -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password -customfilterfolderid 10001 -filterconfigfile ../components/foundation/samples/Staging/Promotion/promotionFilterByFolder.xml
  - stagingprop.bat -scope _all_ -sourcedb staging_database_name -destdb production_database_name -log log_file_name -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password -customfilterfolderid 10001 -filterconfigfile ..\components\foundation\samples\Staging\Promotion\promotionFilterByFolder.xml
  - stagingprop -scope _all_ -sourcedb staging_database_name -destdb production_database_name -log log_file_name -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password -customfilterfolderid 10001 -filterconfigfile ..\components\foundation\samples\Staging\Promotion\promotionFilterByFolder.xml
- - stagingprop.sh -scope _all_ -sourcedb staging_database_name -destdb production_database_name -log log_file_name -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password -customfilterfolderid 10001 -filterconfigfile ../components/foundation/samples/Staging/Promotion/promotionFilterByFolder.xml
  - stagingprop -scope _all_ -sourcedb staging_database_name -destdb production_database_name -log log_file_name -dbtype oracle -sourcedb_user user -sourcedb_passwd password -destdb_user user -destdb_passwd password -customfilterfolderid 10001 -filterconfigfile ..\components\foundation\samples\Staging\Promotion\promotionFilterByFolder.xml
Review the log file for the stagingprop utility to ensure that the staging process successfully completed. The log file is in the following directory:
- WC_installdir/logs
- WC_installdir\logs
- WCDE_installdir\logs
In your production or target environment, verify that the new and changed promotions are available in Management Center.