stagingprop utility

The stagingprop utility retrieves all the unprocessed STAGLOG records and processes them. An unprocessed STAGLOG record is any record where the value of the column STGPROCESSED is set to 0. Successful stagingprop updates these STAGLOG records in the STGPROCESSED column from unprocessed (0) to processed (1). The stagingprop utility 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.

You 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.

A new function is available to 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 by using the following SQL command:

delete from STAGLOG where stgrfnbr=-1; 

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

To run the stagingprop utility, type the command within a command line on a system that can connect to both the staging server and the production server database.

To run shell scripts:

1. Log on as a user profile that has a CCSID other than 65535.
2. Start a Qshell session.
3. Run the utility as follows:

WC_installdir/bin/stagingprop.sh <parameters and values>

Utility command

The stagingprop utility has the following file name:

• stagingprop.sh
• stagingprop.bat

Parameter values

dbtype

• Optional: Specify DB2. DB2 is the default database type and you can omit the dbtype parameter from the command.
• Required: Specify one of the following values:

  DB2/OS400

  Specify DB2/OS400 when you use the Native JDBC driver.

  DB2/OS400ToolBox

  Specify DB2/OS400ToolBox when you use the IBM Toolbox for Java JDBC driver.

Required: Specify Oracle.

scope

Required: The table scope level for the publication to the production server. Use this parameter to filter the publication by table.

Specify one of the following parameters:

_all_

Specify _all_ to publish all records.

Copies both records that are 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_

Specify _site_ to publish only site-related records. Site-related records are data that is 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_

Specify _ merchant_ to 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.

Note: You must 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 that is specified by the configfile s parameter. If you specify a custom scope, you must specify the configfile s parameter. You can specify multiple scope lists by separating the scope list names with a slash character ("/").

The stagingprop utility follows the order of the database tables in the list or lists provided. When you create your database table lists, ensure that any referenced tables appear in the list before the referencing tables.

configfile s

The full path to the file that contains the scope information for the custom scope. For instructions on creating this file, see

Creating a database table filter list.

Note: If you do not set your scope to _all_:

• Propagate site data before merchant data, since the site data is used by all merchants. Otherwise, your propagation fails because of a mismatch between the foreign and primary keys.

dbtable

(Deprecated) Use the scope parameter and specify a custom scope to publish a specific table.

The name of any specific table to be published. All changed records in this table are published, provided the records are within the scope that is specified by the scope parameter; otherwise, no records are published.

sourcedb

Required: The name of the database on the staging server.

• If the dbtype parameter is DB2/OS400, specify the name of the database on the staging server, as displayed in the relational database directory.
• If the dbtype parameter is DB2/OS400ToolBox, specify the host name of the server on which the production-ready data resides.

Note: For DB2 UDB databases, the DB2 Type 4 JDBC driver is used, where the Type 4 database name is prefixed with the database server and port. For example, 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.

The user profile that is associated with the commerce instance. The value is the same as the source database schema.

sourcedb_passwd

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

sourcedb_schema

Optional: Specifies the schema on the source database where all operations are conducted. Specifically, this schema should have all database objects that are 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 server.

• If the dbtype parameter is DB2/OS400, specify the name of the database on the production server, as displayed in the relational database directory.
• If the dbtype parameter is DB2/OS400ToolBox, specify the host name of the server on which the production database resides.

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

Note: For DB2 UDB databases, the DB2 Type 4 JDBC driver is used, where the Type 4 database name is prefixed with the database server and port. For example, db_server:db_port/db_name.

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.

The user profile that is associated with the commerce instance. The value is the same as the production database schema.

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

destdb_passwd

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

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

destdb_schema

Optional: Specifies the schema on the destination database where all operations are conducted. Specifically, this schema should have all database objects that are 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: Specifies the number seconds the stagingprop utility connection to the production database should wait to obtain a lock on the database it is updating. If the stagingprop utility 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 utility wait until it can obtain a lock on the database record it wants to update.

  If you do not specify this parameter, the stagingprop utility waits for the default time that is set in the database configuration. Contact your database administrator to find out the default lock timeout value.

transaction

Optional: Specifies the number of changes that occur before the changes are committed to the production database. If you do not specify this parameter, change logs are committed according to the one action.

one

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

list

Changes to the production database are committed after all of the change logs for the list of database tables that are specified by the scope parameter are processed. You must 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. You 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: Specifies which column in the STAGLOG table contains the filter mark values. The column that is 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 you do not specify this parameter, 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 you are publishing any of the following changes from the production-ready data to the production server:

• Using 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.

Using SQL batch updates improves the speed with which the stagingprop utility 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. For more information, see the trace parameter.

retry

Optional: Specify the number of times the stagingprop utility reattempts a transaction when it encounters a transaction rollback.

waittime

Optional: Specify the number of seconds the stagingprop utility 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. When used, 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, stagingprop's 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.

CAUTION: consolidationSize might prolong the consolidation phase of stagingprop. Use consolidationSize only after all other option are exhausted. It is recommended that you first attempt to tune the system and database parameters and maintain existing stagingprop behavior. For example, increase the maximum reserved allocation of the JVM heap, increase the size of log space that is reserved for database transactions, or both.

Using consolidationSize no longer has an adverse impact on the consolidation phase of stagingprop. If you encounter java.lang.OutOfMemoryError or issues with database transactional log space during the consolidation phase, consider setting consolidationSize as a workaround to the default method of consolidation. This might be especially useful if tuning your system or database is not possible.

log

Optional: The path and name of the file in which the stagingprop utility 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 that is called stagingprop_yyyy.mm.dd_hh.mm.ss.zzz.log is created in the following log directory:

• WC_installdir/logs
• WC_userdir/instances
• WC_installdir\logs

waspath

Optional: This parameter is required for the stagingprop utility to publish managed files to the production server. The parameter has no effect if the stagingprop utility is used only to copy data to the production server.

You must specify this parameter with the profilename, appname, and modulename parameters to publish managed files using the stagingprop utility. If you miss specifying one parameter, managed files are not published by the stagingprop utility.

The full path to the location of the WebSphere Commerce profile. This path is used to locate the wsadmin.sh or wsadmin.bat file on staging or authoring server.

profilename

Optional: This parameter is required for the stagingprop utility to publish managed files to the production server. The parameter has no effect if the stagingprop utility is used only to copy data to the production server.

You must specify this parameter with the waspath, appname, and modulename parameters to publish managed files by using the stagingprop utility. If you miss specifying one parameter, managed files are not published by the stagingprop utility.

The name of the WebSphere Application Server profile on the staging server that contains WebSphere Commerce. By default, the profile name is the same as the WebSphere Commerce instance name. Ensure that the staging server and the production server are in the same federated environment.

appname

Optional: This parameter is required for the stagingprop utility to publish managed files to the production server. The parameter has no effect if the stagingprop utility is used only to copy data to the production server.

The value of the enterprise archive (EAR) that you are propagating files within on the production server. For instance, the WebSphere Commerce EAR

You must specify this parameter with the waspath, profilename, and modulename parameters to publish managed files. If you do not define these three parameters, managed files are not published by the stagingprop utility.

Note: If the store web archive (WAR) for your site is separated from the WebSphere Commerce enterprise archive (EAR), set the parameter value to be the store EAR. By default the value of this parameter for a separated store EAR is WCStores_instanceName.ear. You must specify this parameter value when you run the fileprop utility as part of the stagingprop process. When the fileprop utility runs, the value of the appname parameter must be correctly passed in before the utility can call the WebSphere Application Server wcsadmin command.

modulename

Optional: This parameter is required for the stagingprop utility to publish managed files to the production server. The parameter has no effect if the stagingprop utility is used only to copy data to the production server.

You must specify this parameter with the waspath, profilename, and appname parameters to publish managed files by using the stagingprop utility. If you miss specifying one parameter, managed files are not published by the stagingprop utility.

The name of the application module to which managed files are copied. The value is typically Stores.war.

wsadmin_user

Optional: This parameter is optional when the stagingprop utility publishes managed files to the productions server. The parameter has no effect if the stagingprop utility is used only to copy data to the production server.

A WebSphere Application Server administrative user ID.

This parameter is required if WebSphere Application Server global security is enabled. For more information about global security, see Administrative security.

wsadmin_passwd

Optional: This parameter is optional when the stagingprop utility publishes managed files to the productions server. The parameter has no effect if the stagingprop utility is used only to copy data to the production server.

The password for the user ID specified by the wsadmin_user parameter.

This parameter is required if WebSphere Application Server global security is enabled. For more information about global security, see Administrative security.

ILogConfigPath

Optional: This parameter applies only to staging servers configured for ILOG JRules integration.

This parameter is required for the stagingprop utility to propagate ILOG JRules rule data from the staging Rule Execution Server to the production Rule Execution Server. The parameter value is the path where the ILOG JRules configuration property files are stored. This path is the WebSphere Commerce Enterprise Archive for the staging server instance, for example:

/opt/IBM/WebSphere/AppServer/profiles/demo/installedApps/WC_demo_cell/WC_demo.ear

For default paths for various operating systems, see WC_eardir.

forceFileProp

Optional: Specify whether to run the fileprop utility.

To run the fileprop utility, set forceFileProp to yes. By default, forceFileProp is set to no.

Note: If the waspath, profilename, appname, and modulename parameters are specified, the fileprop utility runs automatically as part of stagingprop regardless of the forceFileProp value. When the four parameter values are set, data and managed files are copied to the production server. If the waspath, profilename, appname, and modulename parameters are not specified, but forceFileProp is set to yes, the fileprop utility copies data files only to the production server.

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 – (default) 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 that is 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

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

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 - (default) 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.

Note: 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

(default) An EXCLUSIVE lock is acquired.

cutoffTime

Optional: Specifies the 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 by using the pattern yyyy-MM-dd.HH:mm:ss. Enclose the cutoffTime value in double quotation marks to prevent any errors. For more information, see Class SimpleDateFormat.

For example:

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

paramfile

Optional. Specifies the 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. Specifies a custom filter condition that the stagingprop utility is to use to filter the data that the stagingprop utility 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 utility processes objects that match the custom filter condition. You 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, you can define the SQL for a custom filter condition that propagates only the data that belongs to a specific store. Within this SQL, you can include the parameter {customfilterstoreid} to represent the store ID value. Then, when you run the stagingprop utility, 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. Specifies the staging filter configuration XML file that defines the SQL for how the stagingprop utility 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.