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.

delete from STAGLOG where stgrfnbr=-1; 

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

Note:

• Before running the stagingprop utility for the first time (after creating authoring and live environments), connect to the authoring database, and execute the following statement. This will help to avoid duplicate key exception arising due to Flexnet stagetable data (for example, GP Value, *GPEnd *Value records in the STORECONF table).

  update staglog set stgprocessed=1 where stgprocessed=0 and stgtable='storeconf' and stgokey3 in ('GP', 'GPEnd') and stgop='I'

• If your staging environment contains either web activities, or content spots, you must refresh the registry before any updates are displayed on the site.
• Your staging and production environments must be at the same maintenance level to successfully run the stagingprop utility. Your staging and production environments also must have the same features enabled.
• You can determine which tables are propagated by the stagingprop utility by viewing the list of managed tables. For more information, see ../tasks/tsstables.html#tsstables.
• Optimize the performance of the stagingprop utility by ensuring that the default isolation level for WebSphere Application Server is set to Cursor Stability. For more information about how to set the default level of isolation for WebSphere Application Server, see Changing the default isolation level for non-CMP applications and describing how to do so using a new custom property webSphereDefaultIsolationLevel.

Parameter values

dbtype

• Optional: Specify DB2. DB2 is the default database type and you can omit the dbtype parameter from the command.
• 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 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.

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

Note: 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 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 environment, 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 HCL 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 HCL internal use.

HCL 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 environment:

• Using a workspace to delete an HCL 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, 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 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 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 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

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

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