WebSphere Commerce Enterprise

Key splitting

You can split the primary key ranges of database tables between the staging and production environments with the key splitting process. By splitting the key ranges, you can prevent a primary key collision during propagation of data from staging to production.

If your site uses a staging server, data changes to content are typically made on the staging server and propagated to the production server. Depending on the scenario, you might need to change content on both the staging server and the production server. However, updating the same content on both servers can potentially cause a primary key collision. For instance, a collision can occur when a Site Administrator (host) updates data in the reseller marketplace on the staging server and a reseller updates data on the production server.

WebSphere Commerce uses a key manager that generates primary keys for database tables. The keyrange is defined within the KEYS database table. If the production server and staging server use the same keyrange, the key manager can allocate the same primary key value for the same table on the separate servers. This allocation on both servers causes a primary key collision. As a result, this keyrange must be split so the keyrange on each server is mutually exclusive. The keyrange for a table can be split after instance creation is complete on both servers.

As an example the following keyrange identifies the available primary keys for a primary key column. Within this range, the absolute minimum is 0 and the absolute maximum is 10. The keyrange for the production server (0-3) and the staging server (4-7) are mutually exclusive.
               counter|lowbound|highbound
            ----------+--------+---------
ORIGINAL:            0|       0|       10
PRODUCTION:          0|       0|        3
STAGING:             4|       4|        7

            0 1 2 3 4 5 6 7 8 9 10
ORIGINAL:   |-------------------------|
PRODUCTION: |------|
STAGING:            |------|
By default, some WebSphere Commerce tables have key ranges that are already split for each server. This key splitting is completed during instance creation. The tables that have keys that are split during instance creation are tables that can be frequently modified in the production environment. To identify the tables that already have key values that are split for the staging environment, refer to the following files within your environment:
  • Staging environment
    • SolarisLinuxAIXWC_installdir/schema/db_type/wcs.staging.keys.xml
    • WindowsWC_installdir\schema\db_type\wcs.staging.keys.xml
Where db_type is the type of database your site uses.
All tables that are created during feature pack enablement have their key ranges split for each server. To identify these feature pack tables, refer to the following files within your environment:
  • SolarisLinuxAIXWC_installdir/components/foundation/schema/fepX/db_type/wcs.schema.foundation.sql
  • WindowsWC_installdir\components\foundation\schema\fepX\db_type\wcs.schema.foundation.sql
Where fepX is the feature pack level that your system is on, for example, fep6.
Note: When you split the primary key ranges between your staging and production environment, ensure that the keys for access control database tables are always synchronized. Avoid splitting the keys for access control database tables. If the keys for the tables are not in sync, you can encounter problems after you propagate data between environments. Store publish temporarily enables staging triggers for access control tables.

For example, if an action group has a different primary key value between environments and you associate a new action with the action group in your staging environment. When you attempt to propagate the change to your production environment, the propagation can fail, or the new action can be associated with the wrong action group.

Before you propagate access control data into your production environment, review the access control tables and the KEYS table in both environments and ensure that the primary key values for the tables are in sync between environments. Alternatively, run the Staging Copy utility for access control tables to synchronize the tables between environments before you propagate data such as through store publish.

WebSphere Commerce Version 7.0.0.3WebSphere Commerce Version 7.0.0.6WebSphere Commerce Version 7.0.0.5WebSphere Commerce Version 7.0.0.7WebSphere Commerce Version 7.0.0.0WebSphere Commerce Version 7.0.0.2WebSphere Commerce Version 7.0.0.1WebSphere Commerce Version 7.0.0.4

Splitting the keyrange

There are different ways to split the keyrange. One approach is to change the values of the COUNTER column within the KEYS table that is based on values in the UPPERBOUND and LOWERBOUND columns. The following are sample SQL statements that you can use to split the keyrange on the staging and production servers:
Note: When you split the keyrange for database tables, larger ID values are introduced as primary keys. Ensure that any JSP and JavaScript pages that retrieve key values can handle the larger ID values. For more information about updating your store pages to support splitting the keyrange for database tables, see Updating the Aurora starter store for key splitting.
If you are a Site Administrator, you can run the following SQL statements to complete the key splitting for a table. Run these statements only when the staging server and production server instance creation is complete. If you do run the following statements after the production or staging servers are running for some time, the key splitting might not work. This failure to split the keys can occur because the current key counter can be beyond one third of the full keyrange. If this failure occurs, review the KEYS database table and update the SQL statements to split the remaining available keyrange.
Portion one
Run this SQL statement on the production server. This statement ensures that the production server uses only one third of the full keyrange. 
update keys set upperbound = (upperbound-lowerbound)/3 + lowerbound
where tablename in (select tabname from stgmertab union all select tabname from stgsitetab)
and lowerbound = default_lower_bound_value
and upperbound = default_upper_bound_value
Where default lower bound value and default upper bound value are the existing lower and upper bound values of the keyrange for the table.
Portion two
Run this SQL statement on the staging server. This statement ensures that the staging server uses only one third of the full keyrange.
update keys set upperbound = (upperbound-lowerbound)/3*2 + lowerbound, 
lowerbound = (upperbound-lowerbound)/3 + lowerbound + 1, 
counter = (upperbound-lowerbound)/3 + counter + 1 
where tablename in (select tabname from stgmertab union all select tabname from stgsitetab)
and lowerbound = default_lower_bound_value
and upperbound = default_upper_bound_value
Where default lower bound value and default upper bound value are the existing lower and upper bound values of the keyrange for the table.
Portion three
If your site uses a second staging server, run this SQL statement to assign the server the remaining one third of the full keyrange.
update keys set lowerbound = (upperbound-lowerbound)/3*2 + lowerbound + 1, 
counter = (upperbound-lowerbound)/3*2 + counter + 1 
where tablename in (select tabname from stgmertab union all select tabname from stgsitetab)
and lowerbound = default_lower_bound_value
and upperbound = default_upper_bound_value
Where default lower bound value and default upper bound value are the existing lower and upper bound values of the keyrange for the table.
WebSphere Commerce Version 7.0.0.6WebSphere Commerce Version 7.0.0.7Note: For more information about how to define the SQL statements to assign the key ranges, review the contents of the following files. These files include the SQL statements for setting the key ranges for the database tables that are available by default with WebSphere Commerce:
WebSphere Commerce Version 7.0.0.6
  • Staging environment
    • SolarisLinuxAIXWC_installdir/schema/common/fixpack6/wcs.fixpack6.keys.midrange.fp0.staged.sql
    • WindowsWC_installdir\schema\common\fixpack6\wcs.fixpack6.keys.midrange.fp0.staged.sql
  • Production environment
    • SolarisLinuxAIXWC_installdir/schema/common/fixpack6/wcs.fixpack6.keys.lowrange.fp0.staged.sql
    • WindowsWC_installdir\schema\common\fixpack6\wcs.fixpack6.keys.lowrange.fp0.staged.sql
WebSphere Commerce Version 7.0.0.7
  • Staging environment
    • SolarisLinuxAIXWC_installdir/schema/common/fixpack7/wcs.fixpack7.keys.midrange.sql
    • WindowsWC_installdir\schema\common\fixpack7\wcs.fixpack7.keys.midrange.sql
  • Production environment
    • SolarisLinuxAIXWC_installdir/schema/common/fixpack7/wcs.fixpack7.keys.lowrange.sql
    • WindowsWC_installdir\schema\common\fixpack7\wcs.fixpack7.keys.lowrange.sql
WebSphere Commerce Version 7.0.0.8 or later

Splitting the keyrange

You can complete key-splitting of database tables through the following three options.
  1. Run the following scripts to split all tables that are staged and do not contain a keyrange that is split by default.
    • Staging
      • SolarisLinuxAIXWC_installdir/schema/common/keysplitting/wcs.keys.midrange.staging.sql
      • WindowsWC_installdir\schema\common\keysplitting\wcs.keys.midrange.staging.sql
    • Production
      • SolarisLinuxAIXWC_installdir/schema/common/keysplitting/wcs.keys.lowrange.production.sql
      • WindowsWC_installdir\schema\common\keysplitting\wcs.keys.lowrange.production.sql
  2. Run the following scripts to split a set of tables that are staged and are considered to be high-volume tables with a possibility of being altered in both the staging and production servers. Use this option if you want to avoid splitting the keyrange on all staged tables.
    • Staging
      • SolarisLinuxAIXWC_installdir/schema/common/keysplitting/highvolume/wcs.keys.midrange.staging.sql
      • WindowsWC_installdir\schema\common\keysplitting\highvolume\wcs.keys.midrange.staging.sql
    • Production
      • SolarisLinuxAIXWC_installdir/schema/common/keysplitting/highvolume/wcs.keys.lowrange.production.sql
      • WindowsWC_installdir\schema\common\keysplitting\highvolume\wcs.keys.lowrange.production.sql
  3. Run the following scripts to split a set of tables that are staged and are considered to be low-volume tables. These scripts split the keys for the remaining staged tables that are not included by the scripts within option 2. Cumulatively, options 2 and 3 contain all staged tables and are equivalent to running the scripts within option 1.
    • Staging
      • SolarisLinuxAIXWC_installdir/schema/common/keysplitting/lowvolume/wcs.keys.midrange.staging.sql
      • WindowsWC_installdir\schema\common\keysplitting\lowvolume\wcs.keys.midrange.staging.sql
    • Production
      • SolarisLinuxAIXWC_installdir/schema/common/keysplitting/highvolume/wcs.keys.lowrange.production.sql
      • WindowsWC_installdir\schema\common\keysplitting\highvolume\wcs.keys.lowrange.production.sql