BCC - Project Struck - missing seq num ??

[KdM]

Hi Experts,

We have an issue with BCC Production.
Please provide some thoughts to this issue on how to fix it


15:59:29,921 ERROR [nucleusNamespace.atg.commerce.catalog.ProductCatalog-ver] (http-/0.0.0.0:20180-9) Error reading list or array index from the database.
Expected: "20", got "22". The following property was not read: "
{fixedChildProducts,pType=List,IDesc=[ItemDesc: category],table=dcs_cat_chldprd,cols=child_prd_id
  ,pBI=atg.beans.MergedDynamicBeanInfo@4e3cb5e3,pIDesc=null
  ,cType=interface atg.repository.RepositoryItem,cBI=[ItemDesc: product],cIDesc=[ItemDesc: product],colHandle=null}", for item id: "cat311xxx06:6".
 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).: java.lang.Exception
        at atg.adapter.gsa.ListTable.loadHelper(ListTable.java:191) [_DAS_slib_sclasses.jar:]


select * from  ATG_PUB.dcs_cat_chldprd
where CATEGORY_ID='cat311xx06'    -- 20000 rows


ref:https://sravankumarkema.blogspot.com/2015/05/how-to-find-and-fix-sequence-number.html

Warm Regards

KdM

[Wilco Boumans]

Hi,

Your best approach with such a large number of rows is to resequenced the rows in the database.

1. Back up what is needed to be saved - in case it goes wrong

2. If possible, stop BCC, so no updates take place

3. Run a re-sequencing script

For step 3, you have to write it yourself (or ask a DBA'er). Beware of the versioning, you may only have to fix a single version of the data.

When deleting an organisational role in our DB, we used to re-sequence the roles list for organisations with the script below, which may be used as a starting point:

MERGE INTO DPS_ROLE_REL_ORG T1

USING (SELECT RID, NEW_SEQNR

       FROM (SELECT ROWID                                                                   RID,

                    SEQUENCE_NUM                                                            SEQNR,

                    ROW_NUMBER() OVER (PARTITION BY ORGANIZATION ORDER BY SEQUENCE_NUM) - 1 NEW_SEQNR

             FROM DPS_ROLE_REL_ORG)

       WHERE SEQNR != NEW_SEQNR) T2

ON (T1.ROWID = T2.RID)

WHEN MATCHED THEN UPDATE SET SEQUENCE_NUM = NEW_SEQNR;

You could rewrite this for a versioned child product table, and try it on a small local table - manually corrupted to act as a test set.

Hope this helps

Wilco

--
--
You received this message because you are subscribed to the Google Groups "ATG_Tech" group.
To post to this group, send email to atg_...@googlegroups.com
To unsubscribe from this group, send email to atg_tech-u...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/atg_tech?hl=en
---
You received this message because you are subscribed to the Google Groups "ATG_Tech" group.
To unsubscribe from this group and stop receiving emails from it, send an email to atg_tech+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/atg_tech/CAMD9EGxE3SkpQFGxL8nO-MRztp60FzZigRjLKUXvb6QNPQJj2g%40mail.gmail.com.

[KdM]

thanks much, re-sequencing is the fix and  there is a below document in support which discusses the fix, we followed the RQL approach and it fixed the broken sequence number issue.

RQL seems the safest approach here + DB backup.

How to Find and Fix Sequence Number Corruption in the Versioned Category

and Child Product Relationship (Doc ID 1037671.1)

Thanks

KdM

To view this discussion on the web visit https://groups.google.com/d/msgid/atg_tech/1FEF919B-DE44-4F40-8622-BAD70175BE3E%40gmail.com.