This article reports another example of interaction between SOLR delta index and data load process.

In particular, If we have to introduce the delta index- in place of the full- and the data load is already set up, or we have to plan from scratch the data load process, we should keep in mind that the data load operations, the order they are executed and the delta updates recorded in the temporary delta tables (TI_DELTA_CATENTRY and TI_DELTA_CATGROUP) are strictly connected; we should not consider the data load a black box respect to the SOLR index, when we need to update the system through the delta mode. The SOLR index and the Data Load are unfortunately tied in a way we cannot think to build a loading process without knowing in which way they interact.

I report two specific cases: 

  1. delete bundles (reported in the previous article);
  2. delete sales categories;



Architects, administrators and developers working on data load and SOLR delta index


  • WebSphere Commerce v FEP5
  • WebSphere Application Server
  • DB2 9.7


Delete sales categories

Delete sales categories using Management Center

The management center allows to delete sales categories- and the underneath relationships with the products- using the "Catalog tool":


Once we select the wanted category we can delete it.


The delete action will delete the category and its relationships with the associated products.

As we already know, this is a basic operations in CMC. However, it's useful understand what happens under the cover when a delete operation is done. As soon as we delete the sales category, the temporary delta tables TI_DELTA_CATENTRY and TI_DELTA_CATGROUP  will be populated in the following way:



So for example, let's suppose I delete with CMC the sales category 15752 which has just one product associated- 10095- and this product has just one SKU: 10096.

The TI_DELTA_CATENTRY will contain the following data:

10001;10095;U;2014-04-10 17:28:14.994431
10001;10096;U;2014-04-10 17:28:14.996485

The TI_DELTA_CATGROUP will contain the following data:

10001;15752;D;2014-04-10 17:28:14.997778

Running the UpdateSearchIndex (in mode = 1, delta mode) the category will properly disappear from the front end, meaning the delta updates is working fine.

So far so good, since we are using the management center. The troubles could come if we use the data load to delete the categories, a really common scenario!


Delete sales categories using the Data Load

If we need to remove sales categories we can follow the Info Center indications (keep your attention on "Delete" paragraph):

To make this scenario running we need:

Just keep always in mind, if we need to make delta indexes we need to populate also the delta tables, so we need to use also mediators like CatalogGroupSearchIndexMediator during the data load.

You can find the sample files IBM provides in the Data Load sample folder. In particular, we should focus on the loader file; I attach an example (the IBM's one): wc-loader-catalog-group.xml.

You can also find it in your environment under the folder: /xml/config/

Once again, our aim is to delete the category; running the data load, using- for example- the loader file reported just above, we will see the TI_DELTA_CATENTRY get populated but in a different way than the Management Center does. In fact, this time it will contain just the following data:


10001;15752;D;2014-04-10 17:28:14.997778

and nothing in the TI_DELTA_CATENTRY.

You see? It's missing the "Update" action of the relationships between products/SKU and the deleted category. 


The problem

Despite the data load has followed our indication and deleted the category (in the DB the requested category will be marked as "mark for delete") and the UpdateSearchIndex scheduled job ran in delta mode without any apparent issue, the sales category is still visible in the front end because it's still in the SOLR index indeed, like if nothing happened!

Basically, that happens cause the mediator CatalogGroupSearchIndexMediator does not add the "update" actions of the involved products and SKUs. It had to work exactly like the Management Center but it does not.


The solution 

Once we understood they way the search mediators work, we know how we have to set up the dataload. In this specific case, the datalod was made by just a single step:

  1. delete the category

To overcome the mentioned problem, we need to add a further step in the loading process. In particular:

  1. delete the relationships between products and category
  2. delete the category

It means, we will need:

In this way the data load will process two steps: delete the relationships between products/SKUs and category and than delete the category. The temporary index delta table will be populated exactly as the Management center does, and the SOLR index will be updated properly.

By the way, I found a problem with the CatalogGroupRelationshipSearchIndexMediator; it throws an exception and it does not load the TI_DELTA_CATENTRY as it should. To overcome this problem you could install FEP6 or keep staying on FEP5 installing the iFix JR 47273.