Posted by 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.
Staging Triggers
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.
Tables Involved
STAGLOG
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
STGMERTAB
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.
STGSITETAB
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.
STGMRSTTAB
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.
Unsupported Actions
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
Filtered Stagingprops
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.
Custom Tables
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).
Stagingcopy
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.
Hi there,
I’m a db2 DBA working on a site with WC and StagingProp for the first time. We’re having a problem where the db2 (9.7 FP 0) transaction logs become saturated and it looks like Stagingprop is the culpret. When the WC guys look at the logs there is an error with a row (much like you have decribed in this article) but it seems that perhaps Stagingprop is not rolling back the UOW when it hits this error instead leaving the UOW open until the logs get saturated :/
I could obviously increase the number of secondary logs (or indeed make them infdinite) IF it’s just the case that SP needs a lot of logs, but it already has 76 logs available (1.5GB) and looking at the archive logs it seems the prop only usually uses 3-4 at most so I’m thinking it actually an SP crash which isn’t rolling back for some reason.
Gosh that was longwinded, apologies… but have you come across this behavior before?
Cheers,
Gerry
Is this a particularly large stagingprop? You can always take a look at the number of rows in the staglog table on the staging database with a STGPROCESSED of 0 to get an idea of how many rows it is trying to move. This would give you an idea if 1.5 GB is reasonable on log files, or if you need to increase it. I’ve seen exceedingly large (millions of rows) stagingprops cause problems before. When invoking stagingprop, are you using a value for -trasaction? My preference is to specify that to increase the frequency of commits, but I have clients who don’t like to do it because it means that only part of the stagingprop succeeds, leaving the production-role database in an “inconsistent” state.
My favorite starting points for log files are a LOGFILSIZ of 10,000 with about 12 primary and 50 secondary log files. So that’s about 2.8 GB. I fairly frequently find myself bumping logsecond up to the max, though, and occasionally sometimes even end up increasing LOGFILSIZ, so it is certainly possible that more space is needed in the log files – very dependent on your database size and the amount of data you’re trying to stageprop.
Personally, assuming I had disk space, I’d bump up the size of the log files before going down the road of digging into other issues at the Stagingprop level.
Dam good Crooks i am searching about stageprop bit dint found any thing like this…
Read your other article…
Dam good..
Any article on order flow as well??
Nothing on Order flow. What I have on orders so far is just dbcliean:
DBClean – Stale Guest Orders
DBClean – Junk OrderItems
Hi Ember,
I know this is an old post but it’s still useful where I am working. Have you ever seen an issue where you have several pending changes for a store (merchant level publishing) but need to mark a few of them as published because they aren’t wanted in Prod, then later you need to publish the records prior (date wise) to the marked published ones? I’m finding that they remain unprocessed and aren’t picked up by the staging prop job.
Any shared experience would be appreciated.
Yes. I generally pick a number I won’t use for anything else, and update stgprocessed to that number (101 or something). Then when I want them to go, I switch stgprocessed for those rows back to 0. This works if there are no dependencies that are missed.