STGMENBR and Stagingprop Failure

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- stloc              U                0               510303               510303           1
             6665472 2012-10-01- 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.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 555

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.