SAP Commerce Cloud and Props table

[joris_quenee]

Introduction

This article is going to detail a technical table evoked in Data Maintenance and Cleanup of a SAP Commerce Cloud Project

Props table can grow up fast and need to be cleanup to avoid performance issue.

Props

Purpose of props table is to store large Item String attribute outside of main Item table. It is legacy used for those following Items

Extensions	Item
core	SavedValues
core	SavedValueEntry
core	Language
processing	CronJob
catalog	CatalogVersionSyncCronJob
processing	CompositeCronJob
impex	ImpExExportCronJob
impex	ImpExImportCronJob
basecommerce	GeocodeAddressesCronJob
outboundsync	OutboundSyncCronJob
ruleengineservices	RuleEngineCronJob
solrfacetsearch	SolrIndexerCronJob
solrcloudbackup	SolrCloudBackupCronJob
ticketsystem	SessionEventsRemovalCronJob
acceleratorservices	ExportDataCronJob
workflow	WorkflowActionTemplate
workflow	AutomatedWorkflowActionTemplate
cms2	CMSParagraphComponent
acceleratorcms	CMSTabParagraphComponent

When you're creating a new Item, you can add a new rows in Props table by declaring dontOptimize="true" in modifiers tag as following example

<itemtype code="CustomItem" extends="GenericItem" autocreate="true" generate="true">
    <deployment table="custoitem" typecode="..."/>
    <attributes>
        <attribute qualifier="name" generate="true" autocreate="true" type="java.lang.String">
            <persistence type="property"/>
            <modifiers optional="false" unique="false" dontOptimize="true"/>
        </attribute>
    </attributes>
</itemtype>

It is strongly not recommended to do that. If you still need to use dontOptimize="true", it is better to create a dedicated Props table for by adding propertytable as following

<itemtype code="CustomItem" extends="GenericItem" autocreate="true" generate="true">
    <deployment table="custoitem" typecode="..." propertytable="custoprops"/>
    <attributes>
        <attribute qualifier="name" generate="true" autocreate="true" type="java.lang.String">
            <persistence type="property"/>
            <modifiers optional="false" unique="false" dontOptimize="true"/>
        </attribute>
    </attributes>
</itemtype>

Cleanup

When we're doing data maintenance and cleanup is not rare to see lot of entries in Props table. It is often appear at top 10.

SELECT top 10 sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM
    sys.tables ta
INNER JOIN sys.partitions pa
    ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
    ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Normally when we cleanup an Item that depends on Props table the entries are removed too. 

We can investigate Props content and see what're the most impacting Items by running this following SQL query

SELECT top 10 ct.internalcode, p.name, p.itemtypepk, count(p.itemtypepk) FROM props p
JOIN composedtypes ct ON ct.pk = p.itemtypepk
GROUP by p.itemtypepk, ct.internalcode, p.name ORDER BY count(p.itemtypepk) desc

Often Cronjob, SavedValues, and SavedValueEntry Items are the most impacting. All Cronjobs with no trigger and end successfully can be removed (solr indexation, catalog sync, and so on). You can setup this following cleanup Cronjob for

Cleaning Up Cronjob

# CleanupCronJob / cleanupCronJobsPerformable
INSERT_UPDATE CronJob;code[unique=true];job(code);sessionLanguage(isoCode)[default=en]
; cronjobCleanUpCronjob; cleanupCronJobsPerformable;

INSERT_UPDATE Trigger; cronJob(code)[unique = true];cronExpression
# every night at 1AM
; cronjobCleanUpCronjob ; 0 1 * * * ?

SavedValues/SavedValueEntry Items are used to track BackOffice user action/change. It can be removed based by using cleanupSavedValuesPerformable

For more detail, see SavedValues Cleanup

# cleanupSavedValuesPerformable
INSERT_UPDATE CronJob;code[unique=true];job(code);sessionLanguage(isoCode)[default=en]
; cronjobCleanUpSavedValues; cleanupSavedValuesPerformable;

INSERT_UPDATE Trigger; cronJob(code)[unique = true];cronExpression
# every night at 1AM
; cronjobCleanUpSavedValues ; 0 1 * * * ?

When you're running for first time those cleanup Cronjobs, it could be interesting to disable temporary Unlocking Cronjob feature. Otherwise, Cronjobs could be aborted automatically by system because it is over passing default time limit execution.

cronjob.unlocker.active=false

Conclusion

Touching technical tables can be tricky and risky. This article shows you how to deal with Props table cleaning.

If you have any doubt, you should no try to write directly to any internal technical tables. Please ask SAP Expert Services to assist you when it is required to do this kind of action.