How to find and fix Sequence Number Corruption in the Category and Child Product Relationship tables in versioned schema - BCC

Issue: how to find and fix sequence number corruption between categories and their child products in the versioned schema. 

 

Sequence number corruption may be identified in the following ways:

• When you try to add a new child product and saving does not preserve the new child product.
• Try changing a category's child products and saving results all child products to vanish.
• Reverting changes to a category's child products causes inconsistencies in the child product list
• Merge, revert, or deploy operations fail with errors referring to DCS_CAT_CHLDPRD in BCC

Sample Error:  ERROR [ProductCatalog-ver] Error reading list or array index from the database. Expected: "112", got "113". The following property was not read: "

{fixedChildProducts,pType=List,IDesc=[ItemDesc: category],table=dcs_cat_chldprd,cols=child_prd_id
Type=interface atg.repository.RepositoryItem,cBI=[ItemDesc: product],cIDesc=[ItemDesc: product],colHandle=null}", for item id: "cat10019:210". This means the database table holding this property does not have sequential integers starting with 0 in its multi-column. This should only happen if the database table was modified directly (outside of Dynamo).

The above error is due to  corruption of the sequence numbers in the DCS_CAT_CHLDPRD table. There can be duplicate , out of order, or missing sequences numbers. There can be gap in sequence numbers as well. All these leads to corruption in sequence numbers

Finding corrupted assets: 

To resolve assets corruption, need to find which assets are corrupted by looking at the DCS_CAT_CHLDPRD table.  Basically, if you look at the records for a single category_id and asset_version and sort by SEQUENCE_NUM, any gaps or skipped sequence numbers are the main issue you are looking for.  If there are too many instances of corruption to deal with manually, not all of them are known, or you want to search the data and be sure none were missed, below queries helps to find corrupted assets.

To find duplicate SEQUENCE_NUMs, run the below query:

select category_id, asset_version, sequence_num, count (*), max(child_prd_id), min(child_prd_id)
from dcs_cat_chldprd
group by category_id,asset_version, SEQUENCE_NUM 

Example output:

CATEGORY_ID	ASSET_VERSION	SEQUENCE_NUM	COUNT(*)	MAX(CHILD_PRD_ID)	MIN(CHILD_PRD_ID)
cat10019	24	16	2	prod244	prod046
cat10019	24	17	2	prod417	prod324

This shows that category cat10019 contains two sequence numbers (16 , 17) with duplicate data that refer  to different products prod046 and prod324.

An example record that contains the duplication:

SEC_ASSET_VERSION	ASSET_VERSION	CATEGORY_ID	SEQUENCE_NUM	CHILD_PRD_ID
12	24	cat10019	55	prod046
45	24	cat10019	55	prod244

This shows that there are 2 records with sequence number 55 for category with ID cat1001.

Below steps to fix corrupted assets: 

1. Find out correct data should be , Consult Business users to determine which product(s) 

are duplicates belong to the category and need to be retained.

If business users are not available to verify the correct data, review the data for the corrupted 

assets as they exist in the unversioned or production DCS_CAT_CHLDPRD table. This data is what is 

currently displayed to end-users and can be used as a basis for correcting the versioned data.

2. Correct the data as different approaches to correct the data:

A. Using the BCC (preferred option)

If the current environment allows for deployments , create projects to edit the affected category assets. 

For each one, correct the child product list ensuring that the right data (determined in step 1 above) is 

present. This will create a new version of the category and associate the correct list of child products with 

that version (ignoring the prior values and sequence of the child products).

Deploy the new projects to completion and checkin the assets. The duplication should be resolved in the 

latest checked in version of the category assets.

B. Direct SQL updates

As a last option, it is possible to delete one of the duplicate records from the database.

 Afterwards, the BCC users should review the list of child products 

and correct any data issues that may have been introduced - namely, that the deleted products are no 

longer appearing as child products of the category.

You must be sure that the product(s) being deleted are really not part of that category. 

This direct database data manipulation is not ideal option but it is quickest way to resolve the

 issue.

To resolve missing sequence numbers:

Note: Take your database backup before performing these changes.

1. Find the latest or max version of the asset on the main branch whose list property is corrupted 

(e.g. category whose ID is "cat123", latest asset_version on main is "1")

2. Query the asset's corrupted list (e.g. category whose fixedChildProducts list is corrupted); filling in the 

[asset-id] and [max-asset-version] based on step #1:

select sequence_num,child_prd_id
from dcs_cat_chldprd
where category_id = '[asset-id]' and asset_version=[max-asset-version]
order by sequence_num

Note that there will be at least one break in the sequence_num. This fix attempts to take the child products

 from this query, and re-organize them to the category's fixedChildProducts list.

3. Add comma separated list for returned product id's

4. Create a project in the BCC. Add the category to the project. Note that in the log,there could be some  

error shown ,but it can be ignored.

5. To Find the category asset version for this  project. You can do this by clicking 

"Go to Project" for the project in the BCC tasks list and then click the "Assets" tab.  It will show 

which version of each asset is being edited in that project.

6. Then go to the component browser to the "/atg/commerce/catalog/ProductCatalog_ver" repository 

component, and execute the following query, substituting the details for the current issue:

<update-item item-descriptor="category" id="cat123:3">
<set-property name="fixedChildProducts"><![CDATA[prod1111, prod1112,
prod1113,prod1114,prod1115,prod1116,prod1117,etc...]]></set-property>
</update-item>

The products with the mentioned  product IDs are added to the fixedChildProducts of category list, adding new 

sequence numbers.This will happen with reference to the project created in step 4.

Note: Please make sure the correct asset version is used in step 6.

Step:7 Deploy the project (step:4)