Emerald REST Caching On TS Server for Commerce 9.1

Caching on Emerald's TS application

The following URLs are cached on Emerald;

/wcs/resources/store/{storeId}/adminLookup?q=findByStoreIdentifier&storeIdentifier={storename} 
/wcs/resources/store/{storeId}/associated_promotion?q=byProduct&qProductId={productId}&langId={langId
/wcs/resources/store/{storeId}/espot/{espotIdentifier}?catalogId={catalogId}&name={name}&langId={langId
/wcs/resources/store/{storeId}/inventoryavailability/{catentryId}?langId={langId} 
/wcs/resources/store/{storeId}/guestidentity?langId={langId} 
/wcs/resources/store/{storeId}/online_store 

Only a few of the cached urls require the invalidation method, which is enabled. The inventory url is the critical url, which is cached and requires a caching trigger to keep it up to date with the system's real inventory.

Enabling the REST cache for Emerald

The sample cachespec.xml that defines the rules is provided in the file:

To enable caching, you are required

Emerald Store and Inventory Caching

The product inventory is implemented as a separate rest call to the TS-app server in the Emerald store, which is a REST-based store. This call is submitted straight from the browser to the TS-app, bypassing the CDN layer. The store's performance will be significantly improved by caching this call. Although the call can be cached on the CDN, the content will be temporary, and the invalidation policy will be rudimentary. Caching it on the appserver, on the other hand, is considerably more versatile and will enable invalidation techniques to satisfy business needs and keep the data fresh over time.

Inventory Model and Operations

In general, the inventory/quantity is updated regularly from both the backend and user interaction with the system (when submitting the order).

Daily updates and direct feeds would run on the production database, causing inventory adjustments. The triggers would take track of these changes according to the inventory tracking rules. In other words, the inventory feed will be treated in the same way that the user browses and purchases items from the inventory.

While surfing, the browse page data will verify stock/inventory information to ensure that the end user receives accurate information.

1. The stock count goes from positive number to zero (the product is sold/inventory is depleted).
2. The stock count goes from zero to positive number (new stock arrived in the system)

In other words, the inventory can be cached until it runs out of stock, at which point it should be invalidated, OR the inventory can be stored until the backend feed puts the item back into stock.

There could be a bit more complicated lifecycle of the cached content as:

1. In stock
2. Low stock (where the stock is smaller than some value).
3. Out of stock.

Obviously, we will have to invalidate the inventory as it is passing through each of these conditions:

1. The stock count goes down to LowStock Value (we don’t really need to track if this approached from high or low.
2. The Inventory count goes from positive value to zero.
3. The inventory count goes from zero to positive value.

In the inventory invalidation strategy, this mode of operation and logic must be followed. The mechanism for tracking and emitting the invalidation message to the rest of the system should be integrated into the caching triggers in this specific case.

Daily Updates

Daily updates and direct feeds operate on the production database and might induce alterations to the database's inventory table. Since this inventory table's caching triggers will be active, data feed change operations will be handled in the same location and in the same manner as user browsing and checkout activities on the inventory.

Cache Dependencies and Invalidations

The design of the dependencies and invalidations is defined by the website's actual implementation. The inventory calls on the TS-app server, for example, are rest calls that can be directly invalidated; but, if the inventory is displayed on the search response pages, the cache will be emptied altogether.

Inventory Cache Triggers

HCL Commerce supports a variety of inventory types; however, the triggers presented here are for non-ATP inventory, which has a simpler model and is more widely used.

Conditional Triggers

The triggers should fire only if the condition is satisfied, i.e. they should only insert into CACHEIVL table only if the condition is satisfied. This is achieved with the when clause in the trigger:

REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL
WHEN (clause)
BEGIN ATOMIC

The clause for the first case where we have only instock and out of stock would look like:

WHEN ((N.quantity > 0 AND O.quantity = 0) or (N.quantity = 0 and O.quantity > 0)) 

Or the trigger definition would look as:

REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL
WHEN ((N.quantity > 0 AND O.quantity = 0) or (N.quantity = 0 and O.quantity > 0)) 
BEGIN ATOMIC

When we have 3 levels of inventory, as in instock, low stock and out of stock:

REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL
WHEN ((N.quantity > 0 AND O.quantity = 0) or (N.quantity = 0 and O.quantity > 0) OR (N.quantity = 10 and O.quantity > 10)) 
BEGIN ATOMIC

Tips on how to implement special conditions

The example below demonstrates how to set up triggers on an inventory table. The trigger will insert invalidation messages in the cache ivl for the following objects, in addition to the trigger clauses that follow the business logic:

1. SKU level.
2. Product Level.
3. Bundle Level.

--#SET TERMINATOR #

DROP TRIGGER ch_inventory_u#
CREATE TRIGGER ch_inventory_u
AFTER UPDATE ON inventory
REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL
WHEN ((N.quantity > 0 AND O.quantity = 0) or (N.quantity = 0 and O.quantity > 0) OR (N.quantity = 10 and O.quantity > 10))
BEGIN ATOMIC


INSERT INTO cacheivl (template, dataid, inserttime) VALUES ( NULLIF('A', 'A'),  'catentryId:'|| RTRIM(CHAR(N.catentry_id)) , current_timestamp );

INSERT INTO cacheivl (template, dataid, inserttime)
(SELECT distinct NULLIF('A', 'A'), 'catentryId:'|| RTRIM(CHAR(product.catentry_id)) , current_timestamp
  FROM  catentry sku, catentry product, catentrel
  WHERE  O.catentry_id = N.catentry_id and
         N.catentry_id = sku.catentry_id and
         sku.catentry_id = catentrel.catentry_id_child and
         catentrel.catentry_id_parent = product.catentry_id);

INSERT INTO cacheivl (template, dataid, inserttime)
(SELECT distinct NULLIF('A', 'A'), 'catentryId:'|| RTRIM(CHAR(bundle.catentry_id)) , current_timestamp
  FROM  catentry sku, catentry bundle, catentrel skutoprod, catentrel prodtobund
  WHERE O.catentry_id = N.catentry_id and
        N.catentry_id = sku.catentry_id and
        sku.catentry_id = skutoprod.catentry_id_child and
        skutoprod.catentry_id_parent = prodtobund.catentry_id_child and
        prodtobund.catentry_id_parent = bundle.catentry_id);

END#

The 3 inserts in the trigger are matching the 3 possible objects that may have been affected by the inventory table record. The provided trigger is directly applicable to DB2 database, and with minimal changes it can be applied in Oracle DB as well. No further changes are needed, and the invalidations will start working once the trigger is in place.