Filtering data for the stagingprop utility to propagate

We can filter the data that the stagingprop utility propagates to publish updates for only specific objects to our production database. We can filter data by tables, by change records, by custom filters, or by a combination of the filter options.

When you filter the data 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, we can configure the utility to filter by table or by change record. When you run you run the utility, we can use command-line parameters to identify the filtering method and any required configuration file or value to use. We can filter data by using custom filters.

When we use a custom filter, the stagingprop utility propagates data that matches the filter. We can use multiple custom filters at a time to filter the data to propagate, but the filters must be defined within the same configuration file. A custom filter can filter data by criteria such as the store, locale, or object ID (for example promotion ID, category ID, or folder ID). Custom filters apply to only the objects and tables that are defined in the specified configuration file. If a table is not included in any filter definition within the configuration file, the contents of that table are propagated normally. For example, if you have catalog and promotion data that is flagged for propagation and you configure custom filters for promotions, the stagingprop utility propagates the specific promotion data that matches the custom filters and propagates all of the catalog data that is flagged for propagation.

We can use more than one filtering method at a time. For example, if you have catalog and promotion data that is flagged for propagation, we can use custom filters to filter the promotion data and include a custom list that configures the stagingprop utility to propagate only the data within promotion-related database tables. With these filtering methods combined, the stagingprop utility propagates only the promotion data that matches the custom filters, not the catalog data.

Note: By using custom filters, we might not need to modify the triggers for the INSERT and UPDATE operations on the staging database for use in updating the production database. If you include the queries to fetch INSERT and UPDATE propagation data in the staging filter configuration file, you need to modify only the triggers for DELETE operations. If you use the change record filtering option, you need to modify the triggers for the INSERT, UPDATE, and DELETE operations.

For INSERT and UPDATE operations that use custom filters, the staging process uses the custom filter configuration and the primary key values in the STAGLOG table to associate records with the appropriate tables in the staging database and fetch the filtered records for propagation.

For DELETE operations that use custom filters, the staging process cannot fetch or query the records in the staging database. The records are already deleted in the staging database so the staging process cannot determine whether a custom filter applies, such as whether a deleted record was associated with a particular store ID. The only way to identify that a record primary key is associated with a custom filter value is to record the association before you delete the record. We must continue to use the AFTER DELETE trigger associated with the STAGLOG table to determine this association for DELETE operations.

Procedure

When we are applying filters to the stagingprop utility process, we can use any of the following filter methods:

• To use a custom filter to filter data:

  1. Create a staging filter configuration file. This file must define the SQL that the utility is to use to retrieve and propagate the filtered data. When we use a staging filter configuration file, you override the default stagingprop utility behavior during the propagation phase of the stagingprop operation. This override causes the utility to retrieve and use the SQL that we define within your configuration file.

     To identify the specific set of data to be filtered, we can either identify the data set explicitly within the configuration file, or include substitution parameters, {customfilterparametername}, in the configuration file. If we use a substitution parameter, we can then include the customfilter% parameter and the value to identify the data set in the command-line when you run the stagingprop utility. The value for the parametername and % must match so that the value for the customfilter% parameter can be passed from the command-line into the SQL that is defined in the configuration file. By using substitution parameters, we can create a configuration file that we can reuse to propagate the same types of objects by a different ID without needing to update the configuration file. For example, the following stagingprop utility sample includes the substitution parameter {customfilterfolderid}. The value for this parameter must be a folder ID. The sample configuration file defines SQL to filter promotion data by a folder ID so the utility retrieves and propagates only the promotions included within the identified folder. In the utility command for this sample the customfilterfolderid parameter is used to pass in the folder ID that replaces the {customfilterfolderid} substitution parameter:

     • Example: Propagating filtered promotion data to the production database.

  2. Update the database triggers for the staging tables to replace the existing database triggers for the tables that we are applying custom filters to in your configuration file. Since only certain records are propagated, we must override the default database triggers to indicate that only the records for the filtered data are propagated. See Creating SQL triggers to override WebSphere Commerce database table triggers.

  3. Specify the following command-line parameters when you run the stagingprop utility:

     • The filterconfigfile parameter with the file path to the staging filter configuration file as the value for the parameter. We must include this parameter to use a custom filter.

     • The customfilter% parameters and values to use for the substitution parameters in the SQL in the staging filter configuration file to override the default SQL that the utility uses. If your configuration file does not include any substitution parameters, do not include this parameter when you run the utility. If your file includes substitution parameters, include the matching parameters and values when you run the utility to ensure that the SQL utility is complete and valid.

  4. If we are propagating catalog data that needs to be indexed with WebSphere Commerce search, ensure that you update the search index to include your propagated data.

• To use a custom list to filter data by database table:

  1. Create a file containing one or more lists of database tables.

  2. Specify the scope and configfile parameters when you run the stagingprop utility. The scope parameter of the stagingprop utility identifies that we are filtering data by database table. You have four options to scope the propagation of data at the table level:

     _all_

     All production-ready data is propagated to the production environment.

     _site_

     Only the tables that contain site data are propagated to the production environment.

     _merchant_

     Only the tables that contain merchant data are propagated to the production environment.

     s

     Only the defined database tables that are contained in the list s are propagated to the production environment. The list is in a file specified by the configfile parameter. We can specify multiple lists by separating the lists with a slash character ("/").

• To use a change record to filter data:

  1. The filter parameter of the stagingprop utility filters data by change record.

  2. For each change record we want to propagate, insert the same positive integer value into the STGFILTER column for the change records in the STAGLOG table. We can insert this information by using either of the following options:

     • Manually update the STAGLOG table to change the appropriate records.

     • Update the INSERT, UPDATE, and DELETE triggers for any applicable table to insert the changes into the STGFILTER column of the STAGLOG table.

  3. Specify the filter parameter along with the positive integer value when you run the stagingprop utility.

Related tasks
Creating a staging filter configuration XML file
Listing managed tables
Creating a database table filter list
Publishing data to the production database
Creating SQL triggers to override WebSphere Commerce database table triggers

Related reference
Example: Propagating filtered promotion data to the production database
stagingprop utility