So every client I support has issues with stagingprop at some point. The vast majority of them are due to mistakes made in the development process. Many developers do not fully understand what stagingprop does, so here’s a general description that might help.

Stagingprop

Stagingprop is a tool used to move catalog related data from a staging or staging-role environment to a production or production-role environment. There are three major advantages of this approach:

1. The site can be verified in the staging environment before changes are made directly in production
2. Developers and business users do not have direct access to production, which can help satisfy PCI and other security standards
3. Updates to the production database can be scheduled at a low volume time, reducing load on the production database during peak periods

Some of the tables involved can be quite large (don’t get me started on the disadvantages of the attribute data model in Commerce databases), and the tables involved are critical to site operation, so the entire table is not copied every time. Instead, Commerce captures changes (via triggers and the staglog table) and when stagingprop is run, it applies those changes roughly in sequence, taking into account the proper order required for Referential Integrity on the production-role database.

Stagingprop does not capture all data changed. Instead, it captures only the primary keys involved, the operation type (Update, Insert, Delete), and the timestamp of the operation. When stagingprop is run, it then uses this data captured in the staglog table to query the tables in the staging-role database and perform the appropriate action using that data on the production-role database.

Stagingprop does not support:

1. Changes to the primary keys (even one column of a multi-column key). CMC and other tools may “allow” this, but behind the scenes, they are doing a delete and an insert.
2. Any inserts, updates, or deletes to the stagingproped tables on the production-role database

Violation of these will result in stagingprop failures, and stagingprop will not run for any tables yet to be processed until the underlying issue is addressed. I’ll go into more detail on failures in a future post.

The triggers used to capture the changes should only exist on the staging-role database (they may affect performance if they exist on the production-role database). The base triggers are created when stagingcopy is run (which it should be from prod to stage as a step in getting the systems set up for stagingprop). The file it uses to create these triggers is in 

/opt/IBM/WebSphere/Commerce60/schema/db2/wcs.stage.trigger.sql

There can be additional triggers added by CMC or feature pack enablement, and there can also be custom triggers added, so that script should not be taken as the #1 authority. I think I’ll cover the triggers in greater detail in a future post too.

I think that covers the basics, anyway. I plan to do a series of posts on stagingprop this week – mostly because it has been a pain point for me lately.