STAGE PROPAGATION – The Introductory Overview
Stage Propagation in naming we can call it as ‘stageprop’. It is a utility of WCS framework which propagates the staged data from the STAGE environment to the PROD environment. So here we refers two instance databases, one is STAGE and other is PRODUCTION. If you have done some customization on your database schema by creating/updating/deleting any tables and you want to make those changes to be moved to PROD instance, then you need to perform this Stageprop activity.
So the idea is that, before moving anything directly to PROD, we will have a cross check on STAGE. The concept of stageprop had solved problems of viewing results in PROD. Since STAGE is replica of PROD, so business team will do all changes to STAGE like eSpot and other elements. Once it is being confirmed by the business, the data is propagated to PROD by performing this activity. This saves time of recreation of data on PROD and also avoid mishappeing with results. In this way the PROD servers are untouched or a kind of limited interference. This will also facilitates the movement of bulk data at once to PROD.
WOW Skin Science brings you a total natural therapy. Its free from Parabean, Sulphate and other harmful chemicals. Innovative formulations that are inspired by nature and traditional ayurvedic secrets are blended with exotic herbs.
SHOP ON
STAGE PROPAGATION – The Technical Overview
Script Name : stagingprop.sh
Tables Involved : STAGLOG, STGSITETAB, STGMERTAB, STGMRSTTAB
STAGLOG (Stage Prop Table):
This is the driving table or the main table through which this activity will happen.
STGSITETAB (Site Data Table):
This comes into picture when Stageprop runs with site scope. Here the table record contains site related changes. For example, new taxes or currencies. Make an entry into STGSITETAB table.
STGMERTAB (Merchant Data Table):
This comes into picture when Stageprop runs with merchant scope. Here the table record contains store related changes. For example, the STORE and CATENTRY tables are merchant tables. Typically, when a store ID exists in the table, it is likely a merchant table. Make an entry into STGMERTAB table.
STGMRSTTAB (Site & Merchant Data Table Mixed):
This comes into picture when Stageprop runs with any scope. Here the table record contains both site and store changes. Make an entry into STGMRSTTAB table. In this table, one of the column is used to decide whether the record is SITE or MERCHANT. The column name is COLNAME in STGMRSTTAB table. For example, the MEMBER table uses MEMBER_ID to decide if the record is SITE or MERCHANT.
Some regular use tables:
MEMBER – member_id
ORGENTITY – orgentity_id
MBRGRP – mbrgrp_id
MBRGRPDESC – mbrgrp_id
MBRGRPCOND – mbrgrp_id
MBRGRPMBR – mbrgrp_id
STADDRESS – staddress_id
STOREENT – storeent_id
CMDREG – storeent_id
STOREENTDS – storeent_id
STOREGRP – storegrp_id
FFMCENTER – ffmcenter_id
SHIPMODE – shipmode_id
FFMCENTDS – ffmcenter_id
STORE – store_id
STORBLKRSN – storeent_id
STQOTCFG – storeent_id
MGPCONDELE – mbrgrp_id
MGPCONDELENVP – mgpcondele_id
SEOPAGEDEF – storeent_id
SEOTOKENUSGTYPE – storeent_id
SEOURLKEYWORD – storeent_id
STORECONF – storeent_id
STOREDEFCAT – storeent_id
STAGE PROPAGATION – The Procedural Overview
Follow the steps for each custom table you want to do Stageprop.
- Define a primary key index for the table. This primary key should not have more than 5 columns.
- Create a trigger for INSERT, UPDATE and DELETE operation for this table. This trigger will make an entry into STAGLOG table for every Create/Update/Delete operations.
- Create an entry for this custom table into one of these three tables asper the scope : STGSITETAB, STGMERTAB, STGMRSTTAB.
STGMERTAB suits most of our custom needs. If you are making an entry of customized parent and child table, then ensure that TABNBR column value of child table is unique and higher than the TABNBR column value of the parent table. Stageprop pushes the data starting with lower number. Since we are putting data for custom table, we should use a higher number. In this way all parent tables are propogated before child custom tables.
STAGLOG table column details:
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 new value(s) of the primary key column(s)
STGPROCESSED – flag indicating whether the row has been processed or not. 0=not processed, 1=processed
Example of an INSERT trigger:
An INSERT trigger to capture insert operation on the custom table.
Create or replace trigger INSERTSTG
AFTER INSERT ON catgroup REFERENCING NEW AS N
FOR EACH ROW
BEGIN
INSERT INTO STAGLOG
(
stgrfnbr, stgstmp, stgtable, stgop,
stgmenbrname, stgmenbr,
stgpkeyname, stgpkey,
stgkey1name, stgkey2name, stgkey3name, stgkey4name,
stgokey1, stgokey2, stgokey3, stgokey4,
stgnkey1, stgnkey2, stgnkey3 , stgnkey4,
stgprocessed, stgreserved1
)
VALUES
( STAGESEQ.NEXTVAL,CURRENT TIMESTAMP, 'catgroup', 'I',
NULL, 1,
NULL, NULL,
'catgroup_id', NULL, NULL, NULL,
:N.catgroup_id, NULL, NULL, NULL,
:N.catgroup_id, NULL, NULL, NULL,
0, 0
);
END;
Example of an UPDATE trigger:
An UPDATE trigger to capture update operation on the custom table.
Create or replace trigger UPDATESTG
AFTER UPDATE ON catgroup REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
INSERT INTO STAGLOG
(
stgrfnbr, stgstmp, stgtable, stgop,
stgmenbrname, stgmenbr,
stgpkeyname, stgpkey,
stgkey1name, stgkey2name, stgkey3name, stgkey4name,
stgokey1, stgokey2, stgokey3, stgokey4,
stgnkey1, stgnkey2, stgnkey3 , stgnkey4,
stgprocessed, stgreserved1
)
VALUES
( STAGESEQ.NEXTVAL,CURRENT TIMESTAMP, 'catgroup', 'U',
NULL, 1,
NULL, NULL,
'catgroup_id', NULL, NULL, NULL,
:O.catgroup_id, NULL, NULL, NULL,
:N.catgroup_id, NULL, NULL, NULL,
0, 0
);
END;
Example of a DELETE trigger:
A DELETE trigger to capture delete operation on the custom table.
Create or replace trigger DELETESTG
AFTER DELETE ON catgroup REFERENCING OLD AS O
FOR EACH ROW
BEGIN
INSERT INTO STAGLOG
(
stgrfnbr, stgstmp, stgtable, stgop,
stgmenbrname, stgmenbr,
stgpkeyname, stgpkey,
stgkey1name, stgkey2name, stgkey3name, stgkey4name,
stgokey1, stgokey2, stgokey3, stgokey4,
stgnkey1, stgnkey2, stgnkey3 , stgnkey4,
stgprocessed, stgreserved1
)
VALUES
( STAGESEQ.NEXTVAL,CURRENT TIMESTAMP, 'catgroup', 'D',
NULL, 1,
NULL, NULL,
'catgroup_id', NULL, NULL, NULL,
:O.catgroup_id, NULL, NULL, NULL,
:NULL, NULL, NULL, NULL,
0, 0
);
END;
STAGE PROPAGATION – The Troubleshoot Overview
One of the most common reason of the failure of Stageprop is the modification of data on STAGE and PROD instance manually.
We should never do this, as it will unnecessarily delay you fixing the problem of Stageprop failure.
All Stageprop log or activity will be saved into ‘stagingprop.log’ file (you can change default location on execution if you like). If you encounter a failure, then check the detailed error message from here.
Frequently you will then use the STGRFNBR column of STAGLOG table 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 STGPROCESSED column of STAGLOG table 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.
STGPROCESSED column value of STAGLOG table:
-5 = primary-key for table specified in STGTABLE column not found in physical table on staging database -4 = error encountered during consolidation when consolidation-error-toleration is enabled -3 = error encountered during propagation of 'I' record when action-on-error semantics are enabled -2 = error encountered during propagation of 'U' record when action-on-error semantics are enabled -1 = error encountered during propagation of 'D' record when action-on-error semantics are enabled 0 = not processed 1 = processed
Daron Mcglothlin
It’s really a cool and useful piece of information. I’m glad that you shared this useful information with us. Please keep us informed like this. Thanks for sharing.
Laurie
Sweet blog! I found it while surfing around on Yahoo News. Many thanks
0mniartist
I have been exploring for a little bit for any high quality articles or blog posts in this kind of area. Exploring in Yahoo I eventually stumbled upon this website. Studying this info So I am glad to show that I have a very good uncanny feeling I found out just what I needed. I most definitely will make sure to don’t put out of your mind this web site and give it a glance regularly.
0mniartist
You’re actually a just right webmaster. The site loading velocity is incredible. It sort of feels that you’re doing any distinctive trick. In addition, the contents are masterpiece. You have done a excellent job on this subject!
Meggie Claudian Lunnete
I have been checking out a few of your posts and I must say nice stuff. I will surely bookmark your website.
Agustin
Thanks on your marvelous posting! I quite enjoyed reading it, you might be a great author. I will make certain to bookmark your blog and will eventually come back someday. I want to encourage you continue your great work, have a nice morning!
Sharleen
I really like your writing style, fantastic info, regards for posting.
Jane Nikolos Litt
I am actually thankful to the owner of this web page who has shared this great post at here.
Victoria Eggleston
Nice blog here! Also your web site loads up very fast!
chandra benton
You are in reality a just right webmaster. The site loading velocity is amazing. It sort of feels that you’re doing any unique trick. Moreover, The contents are masterpiece. You’ve done a magnificent activity in this matter!
Rosaura
Hi there mates, pleasant piece of writing and nice urging commented here,
I am truly enjoying by these.
Cristabel Boyce Clovah
I think this is one of the most vital information for me.
Yolanthe Denis Samuel
As I am a web-site possessor I believe the content material here is rattling excellent, appreciate it for your hard work.
Robbin Patrice Sink
There is visibly a bundle to identify about this. I assume you made certain nice points in features also.
Essie Muhammad Jonna
Marvelous, what a web site it is! This webpage gives valuable data to us, keep it up.
Almeta Inger Herrle
Please go to the web-sites we stick to, which includes this a single, because it represents our picks from the web.
Tildy Algernon Brendon
I was looking through some of your articles on this site and I believe this web site is real instructive! Retain posting.
Jocelyne Somerset Olenolin
I beloved as much as you will obtain carried out right here.
Feodora Chalmers Rayner
Pretty! This has been an incredibly wonderful post.
Mariele Seward Elvis
Using chalk, sketch out the image of a ladder, then, starting at the bottom, step in and step out from each square until you have made it to the top.
Valida Valdemar Zimmermann
These are typically not as realistic as move games and can make the player into surrealism and dream.
Laraine Ludovico Lauralee
I was really confused, and this answered all my questions.
Grace Dallas Meli
I got what you intend, bookmarked, very nice internet site.
Arda Othello Demetre
Hello. This article was extremely motivating, especially since I was looking for thoughts on this issue last Saturday.
Adaline Carmine Kahlil
Wonderful article! We are linking to this great content on our website. Keep up the great writing.
Vinay
A well explained process to understand during PROD deployment.
Jeevan Kumar
Good content..
Pankaj
Lovely site. Helpful content