This post is specific to WebSphere Commerce.
I ran into a new stagingprop failure today(which doesn’t happen all that often anymore), and it taught me something about custom staging triggers, too.
What this Error Looks Like
Stagingprop fails, and a message such as this this in the stagingprop log.
(Tue Oct 02 09:51:44 EDT 2012)The sequence of the following operations in STAGLOG table is invalid: operation U followed by U. (Tue Oct 02 09:51:44 EDT 2012) 6665471: UPDATE table stloc - stloc_id=510303 (Tue Oct 02 09:51:44 EDT 2012) 6665472: UPDATE table stloc - stloc_id=510303
This was a new one. I’d seen issues with a D followed by U error before, but not two U’s. Usually this type of error is either associated with a primary key change or issues with related rows in different filters. In this case neither is the issue:
db2 "select stgrfnbr, stgstmp, stgtable, stgop, stgprocessed, stgnkey1, stgokey1, STGMENBR from wscomusr.staglog where stgtable='stloc' and (stgnkey1=510303 or stgokey1=51303) and stgprocessed=0 with ur"
STGRFNBR STGSTMP STGTABLE STGOP STGPROCESSED STGNKEY1 STGOKEY1 STGMENBR
-------------------- -------------------------- ------------------ ----- ------------ -------------------- -------------------- -----------
6665471 2012-10-01-12.53.07.728499 stloc U 0 510303 510303 1
6665472 2012-10-01-12.53.07.728499 stloc U 0 510303 510303 0
2 record(s) selected.
On examining the row, you can see that the only difference between the two update rows was the STGMENBR column. I had to look up what this column is. From the WebSphere Commerce Info Center:
Indicates whether staged table referenced in STGTABLE is a site or merchant table:
- 0: site table
- 1: merchant table
What the Real Issue Is
In this specific case, someone had added some custom staging triggers without deleting the existing out-of-the-box staging triggers. The only difference between the two sets of triggers was the value that they populated for STGMENBR. Stagingprop can handle multiple updates for the same row – it does it all the time. But if the updates for the same row have different STGMENBRs, apparently stagingprop cannot handle that.
In this case, the table in question is part of the site scope, which I verified by figuring out which table it was in:
$ db2 "select * from wscomusr.stgsitetab where tabname='stloc' with ur"
TABNBR TABNAME OPTCOUNTER
----------- ------------------------- ----------
5003 stloc -
1 record(s) selected.
$ db2 "select * from wscomusr.stgmertab where tabname='stloc' with ur"
TABNBR TABNAME OPTCOUNTER
----------- ------------------------- ----------
0 record(s) selected.
The solution was to mark the rows processed that had a STGMENBR of 1 for this table and one dependent table (stlocattr) that had the same issue. After that, stagingprop ran successfully.
Now your situation might not be the same as mine – a client DBA added these triggers, and it’s a mistake that wouldn’t happen for a database I’m supporting myself. If I add custom triggers for a table, I drop the out of the box ones if there are any, and ask why we need the custom ones if someone else is providing the syntax. In this case, this database will just run into exactly the same issue again if the custom triggers are left in place.
The Lesson for Custom Triggers
This actually taught me an interesting lesson. In several cases, I’ve seen issues where we add custom tables to stagingprop, but then the data won’t prop unless we add the table to both the stgsitetab and stgmertab tables. Which I shouldn’t have to do, but in the cases in question they were always propping with a scope of all, so it wasn’t a problem. I now suspect that in those cases, I’ve written the custom triggers with the wrong value for STGMENBR.
So the moral of the story is: you learn something new every day.
