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 or authoring environments to production.

Note: Key splitting is no longer necessary if your instance was created solely on WebSphere Commerce Version 8 and was not migrated from Version 7. In Version 8, all staged database tables have the primary keys split at instance creation. In Version 7, the primary key for only some staged tables were split, such as tables that were introduced in feature pack releases. If you are migrating from Version 7, split the key ranges for your staged database tables.

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

With the default key splitting, the authoring environment results in having larger key values, while the production environment results in having lower key values to prevent key conflict. However, be aware that there are some values, such as store_id, catalog_id, and catgroup_id that should be kept common between authoring and production. Ensure that these types of key values are adjusted accordingly.

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 staging server (4-7) and the production server (0-3) 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
    • LinuxAIXWC_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.
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. To help ensure that the keys are 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. For example, if an action group has a different primary key value between environments and you then associate a new action with the action group in your staging environment, the keys can be out of sync. 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.

Store publish temporarily enables staging triggers for access control tables.

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
      • LinuxAIXWC_installdir/schema/common/keysplitting/wcs.keys.midrange.staging.sql
      • WindowsWC_installdir\schema\common\keysplitting\wcs.keys.midrange.staging.sql
    • Production
      • LinuxAIXWC_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
      • LinuxAIXWC_installdir/schema/common/keysplitting/optional/highvolume/wcs.keys.midrange.staging.sql
      • WindowsWC_installdir\schema\common\keysplitting\optional\highvolume\wcs.keys.midrange.staging.sql
    • Production
      • LinuxAIXWC_installdir/schema/common/keysplitting/optional/highvolume/wcs.keys.lowrange.production.sql
      • WindowsWC_installdir\schema\common\keysplitting\optional\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
      • LinuxAIXWC_installdir/schema/common/keysplitting/optional/lowvolume/wcs.keys.midrange.staging.sql
      • WindowsWC_installdir\schema\common\keysplitting\optional\lowvolume\wcs.keys.midrange.staging.sql
    • Production
      • LinuxAIXWC_installdir/schema/common/keysplitting/optional/lowvolume/wcs.keys.lowrange.production.sql
      • WindowsWC_installdir\schema\common\keysplitting\optional\lowvolume\wcs.keys.lowrange.production.sql