How to Tell if a Table is a Part of Stagingprop

This post is specific to WebSphere Commerce.

One of the most frequent things I do is to look up whether a table is a part of stagingprop.

When to Check

The most obvious time to check is when someone asks “Is this table a part of stagingprop”. I get that from one developer or another at least once every week or two.

The most frequent trigger for me checking is when a developer asks me to execute some DML(Data Manipulation Language – insert, update, or delete) on a production or production-role server. If you are PCI compliant, you shouldn’t have developers access the production database directly. We run any direct SQL for production through a DBA. Before I execute anything on production, I first check to make sure the table is not a part of stagingprop. If anyone ever asks you to make any change to a table on a production-role server, you must either say NO or deal with the consequences(stagingprop failure).

Why Are Stagingprop Tables NOT Listed in the Information Center?

Someone has inevitably found this page through a search engine, and has been pulling their hair out and asking themselves why, oh why isn’t this information available in the WebSphere Commerce Information Center.

The answer is actually pretty simple. The tables that are a part of stagingprop are a frequently customized area. This is especially true for Business to Business (B2B) web sites. But even traditional customer facing sites frequently customize this area. I would say that the tables I most frequently change stagingprop status for are:

• CMDREG
• INVENTORY
• Any Custom Table

Because of the dynamic nature, I am actually glad they’re not in the Information Center.

How to Tell if a Table is Propped

I am not going to cover all the ways a table could be improperly set up for stagingprop. (post for another day, maybe) I am going to cover how you can check to see if a table is being propped assuming someone set it up properly. This includes default tables.

Let us look at the example of POLICYDESC, and how to see if it is part of stagingprop.

There are two things you must check. The answer to BOTH of these must be ‘yes’ for a table to be part of stagingprop.

Is the Table Listed in STGMERTAB or STGSITETAB, STGMRSTTAB?

To determine this, simply query these tables. In my case:

$ db2 "select * from wscomusr.stgsitetab where tabname='policydesc' with ur"

TABNBR      TABNAME                   OPTCOUNTER UPDATABLE_UK_COL                                                                                             
----------- ------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------

  0 record(s) selected.

$ db2 "select * from wscomusr.stgmertab where tabname='policydesc' with ur"

TABNBR      TABNAME                   OPTCOUNTER UPDATABLE_UK_COL                                                                                             
----------- ------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
       1155 policydesc                         - -                                                                                                            

  1 record(s) selected.

$ db2 "select * from wscomusr.stgmrsttab where tabname='policydesc' with ur"

TABNBR      TABNAME                   COLNAME                   OPTCOUNTER
----------- ------------------------- ------------------------- ----------

  0 record(s) selected.

Note: the TABNAME here is always lower case.
Note: if you’re not on Commerce 7, Fix Pack 5, you won’t have that fourth column in STGMERTAB and STGSITETAB. It is new in Fix Pack 5 of Commerce, along with some other significant changes to stagingprop.

The above tells me that yes, this table is in the table list for the merchant scope (STGMERTAB).

Does the Table have the Three Required Staging Triggers?

Here, you’ll query SYSCAT.TRIGGERS and glance at the trigger text. This is the query I use:

$ db2 "select char(substr(trigname,1,12),12) as trigname, char(substr(text,1,70),70) as text from syscat.triggers where tabname='POLICYDESC' with ur"

TRIGNAME     TEXT
------------ ----------------------------------------------------------------------
PERF_PO2     CREATE TRIGGER perf_po2 NO CASCADE BEFORE UPDATE ON policydesc REFEREN
STAG0608     Create  trigger STAG0608 AFTER INSERT ON policydesc REFERENCING NEW AS
STAG0609     Create  trigger STAG0609 AFTER UPDATE ON policydesc REFERENCING NEW AS
STAG0610     Create  trigger STAG0610 AFTER DELETE ON policydesc REFERENCING OLD AS

  4 record(s) selected.

Note: the TABNAME here is always upper case.

All of the Commerce-supplied triggers will start with “STAG”, but custom staging triggers could have a number of names and follow a number of different naming standards, so I don’t limit the results based on the trigger name. Here, we are looking for three triggers. If we know them to be staging triggers, we can look at just the first few characters of the ‘TEXT’ as I have selected it here to make sure there is one for INSERT, one for UPDATE, and one for DELETE. If the naming doesn’t make it clear, you may have to look at a longer section of ‘TEXT’ to ensure that these triggers are inserting rows into the STAGLOG table for each of the three operations.
In this case, the answer is clearly ‘yes’ – the staging triggers are there.

Answer

Our answer for this example is ‘yes’, POLICYDESC is a part of stagingprop. If the table does not appear in any of the lists OR it doesn’t have the three staging triggers, then it is not part of stagignprop. Or if it has a lesser number of staging triggers, its stagingprop status has been modified

Tracking stagingprop Changes

Where I work, we have started tracking whenever we modify a table’s stagingprop status. This is both because we may have many different developers for a client and want to know who requested a change, when, and also because we trade clients and tasks between DBAs, and each DBA has enough clients that we can’t remember every request or even remember which client a request we did last week was on. So we create a table that we know to always enter such information in. I swear one day I’ll have time to write a trigger to populate the table automatically. We track:

• TABNBR from STGMERTAB
• TABNBR from STGSITETAB
• TABNBR from STGMRSTTAB
• TABNAME
• Insert Trigger Text
• Update Trigger Text
• Delete Trigger Text
• Time triggers were added or removed
• Comments – includes who requested it or why it was removed

After we have populated this tracking table, we then drop the triggers, but usually leave the table in STGMERTAB, STGSITETAB, or STGMRSTTAB. This allows us to easily remember where in the sequence of tables a table fits if we need to add it back.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home