I realized today that I haven’t done a post on the basics of stagingprop. All the pieces are there somewhere in the info center, and I love the info center once you’ve got the basics down, but I remember how confusing stagingprop was when I first started out, even though I had a major expert on the topic to ask questions of.
I also find that developers don’t really understand how it works and what it can and cannot do, and spend some time on education in that regard when I work with developers who are new to Commerce.
What is Stagingprop
First off, Stagingprop is a WebSphere Commerce utility. It is NOT a DB2 utility.
So the idea is that you have not just your production database that serves requests of all sorts from the Commerce application, but that you also have a Staging database. The concept of a staging server solves a number of problems for an OLTP implementation. First of all, it allows business users to make changes to products and to espots and various other elements of the site and review the totality of the site prior to moving the changes to production. This allows a number of changes to appear on the production website as a group, and also allows such changes to be timed for a sale or a holiday. Having a staging server also prevents business users from making changes directly to a production website and limits access to a production database.
A Technical Overview of Stagingprop
Technically, there’s no magic here. The general approach of stagingprop is:
- As changes are made to the staging database, triggers on the tables being changed note changes in a table (STAGLOG)
- The changes noted include the timestamp, the table being changed, the type of change, and the primary key of the row. Full before/after images of the row or records of exactly what part of the row was updated are NOT recorded.
- When stagingprop.sh is executed, it reads STAGLOG, STGMERTAB, STGSITETAB, and STGMRSTTAB and based on those tables, queries the staging database for the full data and inserts/updates/deletes data on production.
Most stagingprop tables are exactly identical on staging and production.
What Stagingprop ISN’T
Stagingprop is not a magic wand to get two environments in sync. The tables have to be appropriately in sync to begin with and then stagingprop can keep up with the changes.
It isn’t the most efficient method of data movement, and vast amounts of data will be extremely slow using this method.
Stagingprop needs to be coaxed and worked with and understood. EVERY site will at some time suffer a stagingprop failure and require intervention to resolve the issue.
For each table that is a part of stagingprop, there are three triggers on that table. All are AFTER triggers. One each for INSERT, UPDATE, and DELETE. When one of those operations happens on the table, these triggers insert a row into the STAGLOG table for every affected row. The row inserted into the staglog table includes the table name, and the values and columns of the primary key of the row affected, along with the operation type (I, U, D). There are other things that are or can be part of it, but that’s the meat of it.
This is the heart of stagingprop. In a basic discussion, the most important columns to be aware of are:
- STGRFNBR – unique generated key for the table
- STGSTMP – the timestamp of the operation
- STGOP – the operation type (I=Insert, U=Update, D=Delete)
- STGOKEY1, STGOKEY2, STGOKEY3, STGOKEY4, STGOKEY5 – the old value(s) of the primary key column(s)
- STGNKEY1, STGNKEY2, STGNKEY3, STGNKEY4, STGNKEY5 – the old value(s) of the primary key column(s)
- STGPROCESSED – flag indicating whether the row has been processed or not. 0=not processed, 1=processed
This tells stagingprop what tables to look at, and what order to process them in when run with the merchant scope. Rows from STAGLOG will only be processed if they’re in one of these tables when stagingprop is run with the merchant scope.
This tells stagingprop what tables to look at, and what order to process them in when run with the site scope. Rows from STAGLOG will only be processed if they’re in one of these tables when stagingprop is run with the site scope.
This resolves the order of processing for any tables that are present in both STGMERTAB and STGSITETAB when stagingprop is run with the all scope.
There are some things that are specifically noted by IBM as unsupported by stagingprop. These include:
- (theoretically) Any data changes that are not made through the Commerce tools.
- Any manual changes to primary keys. Yes, it looks like STAGLOG should be set up to handle this, but stagingprop can’t. If such changes are made through the Commerce tools, the Commerce tools do them as a drop/insert.
- Changing any data in any stagingprop table in production in any way. This is the most common problem we run into – that someone did the data work both in staging and prod and then stagingprop fails due to that.
There are probably more. Some data changes made outside of the Commerce tools will work – you just have to be careful, backup frequently, and test thoroughly.
How to Troubleshoot Problems
Stagingprop writes some pretty decent details to the stagingprop.log file (you can change default location on execution if you like). If you encounter a failure, copy the detailed error message from there. Frequently you will then use the STGRFNBR to query STAGLOG for details on the row that is currently failing. Also, you will frequently query the table that row is in for both stage and prod to try to find the underlying issue. You’ll frequently have to either change data in one environment or sometimes mark the row as processed in STAGLOG so that stagingprop can skip it. If you do mark it as processed, use a value other than 1 in case you need to go back and revisit. I pick a value not being used – usally 7, 9, or 99.
Stagingprop is like an onion – lots of stinky layers. It’s rare to have a single problem on a single row and then go and be successful the next run. Often it is an iterative process of finding problems, correcting them, and then trying again. Getting to the root of why and how you have a problem as soon as possible is very important, because it can help you solve for more than one row at a time.
Advanced Stagingprop topics
I have at least two clients who use filtered stagingprops. I have no idea how that is implemented on the front end, but in STAGLOG, there’s a column called STGFILTER. When changes are made, that column is populated with one of many values and then when stagingprop is run, a filter value is specified and only rows with a matching value are stagignpropped. This can cause issues with manual updates especially and you’re more likely to run into something that is missing a parent because that parent somehow ended up in a different filter value. But it is an important feature when you have multiple stores that need separate abilities to stagingprop running in the same Commerce database.
You can add custom tables to staginprop. I won’t go into the details now, but it’s all in the Info Center (and I’ll likely touch on it in a future post). Theoretically you could add any table as long as you make sure that you respect RI (and data security too, of course).
Ok, so maybe not an “Advanced Topic”, but something to cover in detail in another post – stagingcopy is designed to set up your staging environment in the first place, from your prod environment.
Why did they do it this way?
This is one of the most frequent questions I get when talking to client DBAs about stagingprop. Why didn’t Commerce make use of dpropr? My best guess is that they wanted something that would work in either Oracle or DB2 (LUW or Z), and to get that to happen they had to go with their own solution using triggers in some manner. I would imagine that they were avoiding the overhead of full before/after images of the data, and decided that the reduced complexity of that was worth the added complexity of administering the partial data capture. I also haven’t worked with dpropr since version 7, so don’t recall if it is even possible to have some involved tables that are not 100% synced (such as MEMBER) and how dpropr handles the mountains of RI in a Commerce database.
There are quite a few flaws with stagingprop, but overall, it works once the developers and business users are fully educated on its use. For my simpler “steady-state” clients, there’s actually very little to have to deal with on it.
Anyway, there will be many more posts on stagingprop – the easiest way to deal with stagingprop is if you have a reference on the problems that can occur. Mine is in my head, but there’s precious little out there on the web about it, so as I run into or think of them, I’ll post about problems and gotchas.