stagprop for custom table in IBM WCS

NOTE: If you want your custom table to be used in stagprop, Make sure table name doesnot exceed 18 CHAR length.

The stagingprop utility propagates staged data and managed files from the production-ready data to the production server. If you have customized your database schema by creating new tables and you want to stage these custom tables, perform the following steps for each custom table you want to stage.

Step 1 : Identify scope of table

To enabling custom tables for staging first we need to identify scope of table based on following three criteria :

Site data

The table records contain site changes. For example, new taxes or currencies. For site tables, insert only into STGSITETAB.

Merchant data

The table records contain store 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. For merchant tables, insert only into STGMERTAB.

insert into STGMERTAB values ('10003','x_attributes,NULL,NULL)

Mixed site and merchant data

The table records contain both site and store changes. These hybrid tables are defined in the STGSITETAB table. For hybrid tables, one of the table columns is used to decide whether the record is SITE or MERCHANT. This table column can be defined in the COLNAME column of the STGMRSTTAB table. For example, the MEMBER table uses MEMBER_ID to decide if the record is SITE or MERCHANT.

In both the staging server and the production server, insert the customized table into the STGSITETAB, STGMERTAB, or STGMRSTTAB table, depending on the scope of the table.

If you are inserting customized parent and child tables, ensure the TABNBR column value for the child tables are higher than the TABNBR column value for the parent tables.

Step 2 : Creating triggers for custom tables

We need to add SQL statements to create and drop triggers for the new table to the following files:

•  WC_installdir/schema/db2/wcs.stage.trigger.sql
• WC_installdir/schema/db2/wcs.droptrigger.sql

Gather the following information about the custom table:

• table name
• table scope (site, merchant, or mixed site and merchant)
• primary or unique key columns

Put SQL statements to create three triggers for the custom database table in the wcs.stage.trigger.sql file. You must create three triggers:

• An INSERT trigger to capture insert operations on the custom table.
• An UPDATE trigger to capture update operation on the custom table.
• A DELETE trigger to capture delete operations on the custom table.

Trigger naming convention

The trigger naming convention is a guideline to ensure that new triggers for custom tables have unique names.

 For DB2 : WebSphere Commerce names a trigger by incrementing that last number of the last trigger in the db2/wcs.stage.trigger.sql file. Do not use this naming convention for your new triggers as your custom trigger names could conflict with any new triggers introduced by WebSphere Commerce in fix packs or later releases.

INSERT trigger example(DB2)

This trigger logs INSERT actions in the STAGLOG table after an INSERT action happens on the CATGROUP table.

Create  trigger STAG0098
               AFTER INSERT ON catgroup REFERENCING NEW AS N
               FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
     INSERT INTO staglog
     (  stgrfnbr
     ,  stgstmp
     ,  stgtable
     ,  stgop
     ,  stgmenbr
     ,  stgkey1name
     ,  stgokey1
     ,  stgnkey1
     )
     VALUES (
         NEXTVAL FOR STAGESEQ
      ,  CURRENT TIMESTAMP
      ,  'catgroup'
      ,  'I'
      ,  1
      ,  'catgroup_id'
      ,  N.catgroup_id
      ,  N.catgroup_id
     );
               END#

UPDATE trigger example (DB2)

This trigger logs UPDATE actions in the STAGLOG table after an UPDATE action happens on the CATGROUP table.

Create  trigger STAG0099
               AFTER UPDATE ON catgroup REFERENCING NEW AS N OLD AS O
               FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
     INSERT INTO staglog
     (  stgrfnbr
     ,  stgstmp
     ,  stgtable
     ,  stgop
     ,  stgmenbr
     ,  stgkey1name
     ,  stgokey1
     ,  stgnkey1
     )
     VALUES (
         NEXTVAL FOR STAGESEQ
      ,  CURRENT TIMESTAMP
      ,  'catgroup'
      ,  'U'
      ,  1
      ,  'catgroup_id'
      ,  O.catgroup_id
      ,  N.catgroup_id
     );
               END#

DELETE trigger example

This trigger logs DELETE actions in the STAGLOG table after a DELETE action happens on the CATGROUP table.

Create  trigger STAG0100
               AFTER DELETE ON catgroup REFERENCING OLD AS O
               FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
     INSERT INTO staglog
     (  stgrfnbr
     ,  stgstmp
     ,  stgtable
     ,  stgop
     ,  stgmenbr
     ,  stgkey1name
     ,  stgokey1
     )
     VALUES (
         NEXTVAL FOR STAGESEQ
      ,  CURRENT TIMESTAMP
      ,  'catgroup'
      ,  'D'
      ,  1
      ,  'catgroup_id'
      ,  O.catgroup_id
     );
               END#

Manually run the wcs.stage.trigger.sql script file to generate the triggers on the relevant table.

Note: To verify the triggers defined, you run the following SQL statements to query the system table ALL_TRIGGERS:

select * from all_triggers;