stagingprop utility

The stagingprop propagates staged data and managed files from the production-ready data to the production environment.

The stagingprop retrieves all the unprocessed STAGLOG records. An unprocessed STAGLOG record is any record where the value of the column STGPROCESSED is set to 0. Successful stagingprop updates STGPROCESSED to processed (1).

The stagingprop has two stages: consolidation and propagation. During consolidation, stagingprop examines STAGLOG and determines which STAGLOG records can be marked processed without propagation. Processed STAGLOG records are then propagated to the production database.

We can run stagingprop consolidation without propagation by omitting the following parameters: destdb, destdb_user, and dest_passwd. If some of the parameters are supplied, or if the stagingprop utility cannot establish a connection to the production database with the parameters, the utility does not run successfully. We can also skip the consolidation phase when there are no new records to consolidate since the previous consolidation ran. When there are no new records within the STAGLOG database table that need to be consolidated, a message outputs to the log file to indicate that consolidation is to be skipped. Otherwise, the consolidation phase executes normally. An end-of-consolidation marker marks the last examined record from the consolidation phase. To revert this new functionality so that the consolidation phase is always performed, the end-of-consolidation marker must be deleted using the following SQL command:

delete from STAGLOG where stgrfnbr=-1;

Where the -1 value represents the end-of-consolidation marker.

Run stagingprop on a system that can connect to both the staging environment and the production environment database.

After staging propagation, the cache gets invalidated by the following mechanisms:

• Invalidation using the CACHEIVL table, where the DynaCacheInvalidation command invalidates entries in the dynamic cache based on the dependency ID, cache ID, or template for a cached page that are recorded in the CACHEIVL table. The default cache invalidation interval is 10 minutes.

• Invalidation by directly running the Java classes configured in the StagingPostRowPropagateConfig.properties file, where the stagingprop calls post propagation tasks for the tables listed in the properties file.

If the staging environment contains either web activities, or content spots, refresh the registry before any updates are displayed on the site.

To successfully run the stagingprop, the staging and production environments must be at the same maintenance level and have the same features enabled.

We can determine which tables are propagated by the stagingprop utility by viewing the list of managed tables.

Optimize the performance of the stagingprop by ensuring that the default isolation level for WebSphere Application Server is set to Cursor Stability.

Parameter values

dbtype

• (DB2) Optional: Specify DB2.

  DB2 is the default database type and we can omit the dbtype parameter from the command.

• (Oracle) Required: Specify Oracle.

scope

Optional: The table scope level for the publication to the production environment. Use this parameter to filter the publication by table. Specify one of the following parameters:

_all_

Specify _all_ to publish all records.

Copies records related to the site and to all merchants. Records are copied in the following order:

1. Site records are copied from STGSITETAB

2. Site records are copied from STGMRSTTAB

3. Merchant records are copied from STGMERTAB

4. Merchant records are copied from STGMRSTTAB

_site_

Publish only site-related records. Site-related records are data common to all merchants. For example, the language and country or region code that is used by the system. This data comes from the STGSITETAB table.

_merchant_

Publish only merchant-related records. For example, store information is customized for individual merchants, and rows from the store tables are specific for each merchant. Copy all data for all merchants, not just data for one individual merchant. This data comes from the STGMERTAB table.

s

Specify a custom scope list as defined in the file specified by the configfiles parameter. We can specify multiple scope lists by separating the scope list names with a slash character ("/"). The stagingprop follows the order of the database tables in the list or lists provided. When creating the database table lists, ensure that any referenced tables appear in the list before the referencing tables.

configfile

The full path to the file containing the scope information for the custom scope.

If you do not set the scope to _all_, propagate site data before merchant data, since the site data is used by all merchants. Otherwise, propagation fails because of a mismatch between the foreign and primary keys.

sourcedb

Required: The name of the database in the staging environment.s must be a valid, full JDBC URL or follow the Type 4 database name convention:

db_server:db_port/db_name.

sourcedb_user

Required: The logon ID of the database schema owner who created either the source or production database schema.

sourcedb_passwd

Required: The password of the logon ID specified by the sourcedb_user parameter.

sourcedb_schema

Optional: Schema on the source database where all operations are conducted. Specifically, this schema should have all database objects required by an active WebSphere Commerce instance. When not specified, the value defaults to the schema active on the source database when a connection is established.

destdb

Required: The name of the database on the production environment.s must be a valid, full JDBC URL or follow the Type 4 database name convention:

• db_server:db_port/db_name

If you do not want to propagate the consolidated data to the production environment, do not use this parameter.

destdb_user

Required: The logon ID of the database schema owner who created either the source or production database schema. This parameter is mandatory when using a remote database.

Note: If you do not want to propagate the consolidated data to the production environment, do not use this parameter.

destdb_passwd

Required: The password of the logon ID specified by the destdb_user parameter. If not specified, the system prompts you to enter the password. This parameter is mandatory when we use a remote database.

Note: If you do not want to propagate the consolidated data to the production environment, do not use this parameter.

destdb_schema

Optional: Schema on the destination database where all operations are conducted. Specifically, this schema should have all database objects required by an active WebSphere Commerce instance. When not specified, the value defaults to the schema active on the destination database when a connection is established.

destdb_locktimeout

• Optional: Number seconds the stagingprop connection to the production database should wait to obtain a lock on the database it is updating. If the stagingprop cannot obtain a lock within the specified number of seconds, the database transaction is rolled back.

  Specify a value of zero (0) to have the stagingprop wait until it can obtain a lock on the database record it wants to update. If not specified, the stagingprop utility waits for the default time set in the database configuration. Contact the database administrator to find out the default lock timeout value.

transaction

Optional: Number of changes that occur before the changes are committed to the production database. If not specified, change logs are committed according to the one action.

one

The stagingprop runs as a single transaction and is committed only after publication is successful. If the publication fails, the transaction rolls back returning our production database to the state before the stagingprop began.

list

Changes to the production database are committed after all of the change logs for the list of database tables specified by the scope parameter are processed. Set the scope and configfile's parameters to specify this transaction level.

table

Changes to the production database are committed after all operations for a production database table are processed.

n

Changes to the production database are committed after every n transaction is processed. If you specify the batchsize parameter along with this transaction parameter value, changes to the production database are committed according to a multiple of the batchsize value that meets or exceeds the transaction parameter value.

For example, if you specify transaction 35 and batchsize 20, changes to the production database are committed every 40 changes. 40 is the closest number of changes that is a multiple of the batchsize parameter that meets or exceeds the transaction parameter value. If you specify transaction 20 and batchsize 35, changes to the production database are committed every 35 operations.

filter

Optional: Specify the filter mark value to select which records to publish. Use this parameter to file the publication by record.

By default, the filter option checks for the filter mark value in the STGFILTER column of the STAGLOG table. If you have filter mark values in a different column of the STAGLOG table, use the filtercolumn option to specify the column in which you defined the filter mark value.

Filter mark values must be positive integers. Filter marks values of zero or negative integers are reserved for IBM internal use.

WebSphere Commerce does not provide tools to set or validate filter mark values. We must ensure that a set of changes that use one filter mark value do not have the same filter mark value as another set of changes.

filtercolumn

Optional: Which column in the STAGLOG table contains the filter mark values. The column specified must have a type of INTEGER.

batchsize

Optional: Turns on or off SQL batch updates and specifies the number of consolidated change log records to include in one SQL batch. Change log records are consolidated according to the consolidationSize parameter setting.

If not specified, the batchsize parameter is set to a value of 100.

Setting the batchsize parameter to a value of 0 (zero) turns off SQL batch update. Turn off SQL batch if we are publishing any of the following changes from the production-ready data to the production environment:

• Use a workspace to delete a WebSphere Commerce object that involves the MEMBER table. This includes objects such as users, organizations, customer segments, member groups, customer territory groups, or customer price groups.

When SQL batch update is turned on, change log records are sorted by change type (insert, update, or delete). Each batch contains changes of one type. For example, if you have 102 insert changes and the batchsize parameter is set to 100, 2 SQL batches are created: one batch with 100 insert operations and the other with 2 insert operations.

Use SQL batch updates improves the speed with which the stagingprop updates the production database.

Note:

• JDBC batching may cause inconsistent error handling. Setting batchsize 0 turns off JDBC batching and might help you identify the exact records that are causing errors.

• To generate a comprehensive log file, set trace to a higher trace level. See the trace parameter.

retry

Optional: Number of times the stagingprop reattempts a transaction when it encounters a transaction rollback.

waittime

Optional: Number of seconds the stagingprop waits between retry attempts.

If you do not specify the retry parameter value, the stagingprop utility does not retry a transaction when it encounters a transaction rollback. The stagingprop utility exits with an error.

consolidationSize

Optional: Performs the consolidation phase of stagingprop one table at a time, rather than all at once. This parameter limits the number of records fetched from the database at any given time during the consolidation phase to the specified size.

When not specified, the default behavior of stagingprop is to perform the consolidation phase for all unprocessed records in the STAGLOG table at once. If the specified size is larger than the total number of unprocessed records in the STAGLOG table, stagingprop reverts to the default behavior.

The key benefit of using consolidationSize is to avoid having stagingprop abend because of instances of java.lang.OutOfMemoryError, or to issues that stem from exhaustion of database transactional log space. Performing the consolidation phase one table at a time significantly reduces the JVM heap footprint during consolidation. Consider the scenario where the value specified for consolidationSize is x and the total number of unprocessed records in the STAGLOG table is y:

1. If x >= y, the behavior of the stagingprop consolidation phase behavior remains the same as the default behavior.

2. Otherwise, consider a staged table, A, whose total number of unprocessed records in the STAGLOG table is z:

   1. If z <= x, A's entire set of unprocessed records in the STAGLOG table is fetched once.

   2. If z > x, A's unprocessed records in the STAGLOG table is retrieved in (1 + floor(z/x)) number of fetches.

A good rule of thumb to use when you determine how to specify consolidationSize is to limit the number of fetches to perform for each to-be-consolidated table to one. The following SQL statement can be used to retrieve the number of unprocessed records that exist in the STAGLOG table for each staged table:

select q.* from (select count(*) numrecs, stgtable from staglog where stgprocessed = 0 group by stgtable) q order by q.numrecs desc, q.stgtable

The output is in descending order of the number of unprocessed records for each staged table.

log

Optional: The path and name of the file in which the stagingprop records its activities and errors. The time stamp might be appended to the file name, for example, myLog_ yyyy.mm.dd_hh.mm.ss.zzz.log.

If this parameter is not specified, a log file called stagingprop_yyyy.mm.dd_hh.mm.ss.zzz.log is created in the utilities_root/logs directory.

actionOnError

Optional: Defines the level of error tolerance.

Use actionOnError to define how stagingprop proceeds when it encounters errors. In certain situations, stagingprop should tolerate errors to quickly propagate data. While at other times stagingprop should exit upon encountering any error. actionOnError supports three values:

0	On error throw an exception and exit.
1	On error go to next.
2	Tolerate consolidation errors and on error go to next

When a primary key collision or unique index violation occurs and actionOnError is set to 1 or 2, stagingprop logs the error and then continues. As well, if an error is encountered, stagingprop propagation marks the corresponding STAGLOG record STGPROCESSED column with one of the following values:

1	Delete operation with no result or error
2	Update operation with no result or error
3	Insert operation with no result or error
4	Error that is encountered during consolidation. -4 is logged only when consolidation errors are tolerated (actionOnError value is 2.)
5	The primary key for the table specified in the STGTABLE column was not found in the physical table on the staging database. -5 is logged regardless of whether actionOnError is specified or not

(DB2) If an exception occurs within a batch, only the failing record is marked with an error, stagingprop continues with the rest of the batch.

(Oracle) If an exception occurs within a batch, all records up to the first failing record are committed on the production database. However, all records are marked as -3 in the STAGLOG table on the staging database.

trace

Optional: Defines the level of tracing in the log. trace has four possible values:

0	High-level summary only.
1	Table level information and Global summary report.
2	Table summary report and row level information.
3	SQL statements and diagnostic. Trace values are incremental; each value includes the level of detail of the previous value.

lockStaglog

Optional: Specifies whether stagingprop acquires an EXCLUSIVE lock on the STAGLOG table. lockStaglog has two possible values:

0	No lock is acquired.
1	An EXCLUSIVE lock is acquired.

cutoffTime

Optional: Cutoff time for stagingprop. stagingprop does not examine any STAGLOG table records that correspond to data that is inserted after the specified time stamp value.

Specify the time stamp value using the pattern yyyy-MM-dd.HH:mm:ss. Enclose the cutoffTime value in double quotation marks to prevent any errors. See Class SimpleDateFormat.

For example:

<stagingprop-executable> [<other-arguments>] -cutoffTime "2011-10-05.12:25:00" [<other-arguments>]

paramfile

Optional. Path to the parameter file that includes command-line arguments and values. Each argument and value needs to be in the format argument=value with a single argument and value on each line in the file. Any passwords within this parameter file must be encrypted.

customfilter%

Optional. Custom filter condition that the stagingprop is to use to filter the data that the stagingprop publishes. When the utility runs, only the records for objects that match the specified filter condition are processed and propagated to production. The value that you set for the customfilter% parameter is passed into the SQL that is defined in a staging filter configuration file. This configuration XML file must define the SQL for how the stagingprop processes objects that match the custom filter condition. We can include multiple custom filters in a single staging operation.

To use the customfilter% parameter in the command line, the SQL must include a {customfilterparametername} parameter. The parametername value in the SQL must match the '%' value in the command line.

For example, we can define the SQL for a custom filter condition that propagates only the data that belongs to a specific store. Within this SQL, we can include the parameter {customfilterstoreid} to represent the store ID value. Then, when you run the stagingprop, you specify the store ID as the value for the custom customfilterstoreid parameter. This value then replaces the {customfilterstoreid} parameter in the SQL to process the records for that store.

filterconfigfile

Optional. Staging filter configuration XML file that defines the SQL for how the stagingprop is to filter and process records for publishing specific objects. If a customfilter% parameter and value is set in the command line, the utility substitutes the value into the SQL filter condition. The utility then propagates only the data that matches the filter condition. When you specify the value for the staging filter configuration file, include the relative path to the configuration file from the bin directory.

Related tasks
Run utilities from the Utility server Docker container
Configure the Oracle database connection for utilities to authenticate users with Oracle Wallet
Create a database table filter list
Create a staging filter configuration XML file
Filter data for the stagingprop to propagate
Publish data to the production database
Prevent the WebSphere Commerce application from restarting during the propagation of managed file data
Create SQL triggers to override WebSphere Commerce database table triggers

Related reference
Example: Propagate filtered promotion data to the production database