Sensible defaults for data retention and cleanup for SAP Commerce, based on my CX Works article Data Maintenance and Cleanup
-
Download the latest release
-
Unpack to
hybris/bin/custom -
If possible, disable saved values / change history (ref. help.sap.com, further recommendations in my article)
-
Add extension to your
localextensions.xml<extension name="sanecleanup" />
-
🔴 Adapt the retention rules to your project requirements 🔴
(check the available properties inproject.properties) -
Build and deploy.
(The rules will be automatically imported during system update)If you get a build error regarding missing types like the example below:
- Open
sanecleanup-items.xml - Search for the type
- Comment-out the whole
<itemtype>tag
invalid index sanecleanup for type _TYPE_ on [...] declared at ((sanecleanup))::YIndex[sanecleanup-items.xml:...] due to missing enclosing type '_TYPE_' - Open
WARNING
The very first execution of the retention cron jobs will take a while, depending on how long your poject
is already live and if you have cleaned up anything in the past.
Consider performing a one-time cleanup before adding the extension / enabling the retention rules.
Especially the first run of cronJobLogCleanupCronJob will take a very long time, if you have never removed any cronjob log files (type LogFile).
Please consider importing and executing the script job defined in bulkdelete-cronjoblogs.impex before you set up the automated cleanup!
The job will remove all log files except the five most recent logs per CronJob.
(Disclaimer: the script was tested on MS SQL / Azure SQL and SAP HANA. It is not guaranteed to work for other databases)
If have never even thought about that topic - yes!
You can run the following scripts in the administration console to get a quick overview:
excessive-platform-types.groovy- Generates a report about "known troublemakers"check-audit.groovy- Check if you have too many audit logs
Here are some additional queries and "rules of thumb" that help you investigate further:
| Type(s) | Query | Notes |
|---|---|---|
| AbstractRule |
SELECT
COUNT({ar:pk}),
MIN({ar:modifiedtime}) AS "oldest",
MAX({ar:modifiedtime}) AS "newest"
FROM
{AbstractRule AS ar},
{RuleStatus AS rs}
WHERE
{ar:status} = {rs:pk}
AND {rs:code} = 'PUBLISHED'
AND {ar:enddate} IS NOT NULL
AND {ar:enddate} < getutcdate() |
Are there any outdated rules? i.e rules that aren't valid anymore because their enddate is in the past. Warning: change |
| BusinessProcess |
SELECT
{p:processDefinitionName},
{s:code} AS "status",
COUNT({p:pk}) AS "total",
MIN({p:modifiedTime}) AS "oldest",
MAX({p:modifiedTime}) AS "newest"
FROM
{BusinessProcess AS p
LEFT JOIN
ProcessState AS s
ON {p:state} = {s:pk} }
GROUP BY
{p:processDefinitionName},
{s:code}
ORDER BY
"total" DESC |
Are there too many (let's say > 1000) or very old BusinessProcess in your system? Also, if a lot of processes are stuck in "RUNNING" / "WAITING", you have to investigate what's wrong. (What is causing your processes to be stuck?) |
| Cart |
SELECT
{b:uid} AS "BaseSite",
{u:uid} AS "USER",
CASE
WHEN
{c:saveTime} IS NULL
THEN
'regular'
ELSE
'saved'
END
AS "cart type",
COUNT({c:pk}) AS "total",
MIN({c:modifiedtime}) AS "oldest",
MAX({c:modifiedtime}) AS "newest"
FROM
{ Cart AS c
LEFT JOIN
USER AS u
ON {c:user} = {u:pk}
LEFT JOIN
BaseSite AS b
ON {c:site} = {b:pk} }
GROUP BY
{b:uid}, {u:uid},
CASE
WHEN
{c:saveTime} IS NULL
THEN
'regular'
ELSE
'saved'
END
ORDER BY
"total" DESC |
|
| CronJob (auto-generated) |
SELECT
{t:code} AS "CronJob Type",
COUNT({c:pk}) AS "total",
MIN({c:modifiedtime}) AS "oldest",
MAX({c:modifiedtime}) AS "newest"
FROM
{CronJob AS c
JOIN
ComposedType AS t
ON {c:itemtype} = {t:pk}
LEFT JOIN
TRIGGER AS trg
ON {trg:cronjob} = {c:pk} }
WHERE
{trg:pk} IS NULL
AND {c:code} LIKE '00%'
AND {t:code} IN
(
'ImpExImportCronJob',
'CatalogVersionSyncCronJob',
'SolrIndexerCronJob'
)
GROUP BY
{t:code}
ORDER BY
"total" DESC |
Are there too many (>10) outdated, auto-geneated jobs in your system? |
| CronJobHistory |
SELECT
{cj:code},
COUNT({h:pk}) AS "total",
MIN({h:modifiedtime}) AS "oldest",
MAX({h:modifiedtime}) AS "newest"
FROM
{cronjobhistory AS h
JOIN
cronjob AS cj
ON {h:cronjob} = {cj:pk} }
GROUP BY
{cj:code}
ORDER BY
"total" DESC |
Is there any job with > 50 histories and/or histories older than an hour? This cleanup is enabled by default in recent SAP Commerce patch releases, so this query shouldn't find anything. |
| EmailMessage |
SELECT
{bp:processDefinitionName} AS "source",
{m:sent},
COUNT({m:pk}) AS "total",
MIN({m:modifiedtime}) AS "oldest",
MAX({m:modifiedtime}) AS "newest"
FROM
{EmailMessage AS m
LEFT JOIN
BusinessProcess AS bp
ON {m:process} = {bp:pk} }
GROUP BY
{bp:processDefinitionName},
{m:sent}
ORDER BY
"total" DESC |
|
| ImpExImportCronJob (distributed impex) |
SELECT
{s:code} AS "status",
COUNT({i:pk}) AS "total",
MIN({i:modifiedtime}) AS "oldest",
MAX({i:modifiedtime}) AS "newest"
FROM
{ImpExImportCronJob AS i
LEFT JOIN
CronJobStatus AS s
ON {i:status} = {s:pk} }
WHERE
{i:code} LIKE 'distributed-impex-%'
GROUP BY
{s:code} |
|
| ImpexMedia |
SELECT
COUNT(*)
FROM
{ImpexMedia AS i}
WHERE
(
{i:code} LIKE '0_______'
OR {i:code} LIKE
'generated impex media - %'
) |
Are there more than a handful (>100) of generated impex medias? |
| ImportBatchContent |
SELECT
COUNT({c:pk}) AS "total",
MIN({c:modifiedTime}) AS "oldest",
MAX({c:modifiedTime}) AS "newest"
FROM
{ImportBatchContent AS c
LEFT JOIN
ImportBatch AS b
ON {b:importContentCode} = {c:code} }
WHERE
{b:pk} IS NULL |
Are there any left-over distributed import batches? |
| LogFile |
SELECT
COALESCE({cj:code}, '<null>'),
COUNT({l:pk}) AS "total",
MIN({l:modifiedtime}) AS "oldest",
MAX({l:modifiedtime}) AS "newest"
FROM
{LogFile AS l
LEFT JOIN
CronJob AS cj
ON {l:owner} = {cj:pk} }
GROUP BY
{cj:code}
ORDER BY
"total" DESC |
Are there are cronjob with more than ~10 logs and/or logs older than 14 days? (those are default values for log file retention) |
| ProcessTaskLog |
-- Query tested with MS SQL
-- Adjust the date calculation for
-- other databases
SELECT
COUNT({l:pk}) AS "total",
MIN({l:modifiedtime}) AS "oldest",
MAX({l:modifiedtime}) AS "newest"
FROM
{ProcessTaskLog AS l}
WHERE
{l:creationTime} < DATEADD(
MONTH,
-2,
GETUTCDATE()
) |
We recommend customer to BusinessProcess cleanup, which will eventually take care of TaskLogs cleanup. There might be the few scenarios for ProcessTaskLog cleanup:
|
| SavedValues,SavedValueEntry |
-- total SavedValue / SavedValueEntry
SELECT
*
FROM
(
{{
SELECT
'SavedValues' AS "type",
COUNT({s:pk}) AS "total"
FROM
{savedvalues AS s} }}
UNION ALL
{{
SELECT
'SavedValueEntry' AS "type",
COUNT({e:pk}) AS "total"
FROM
{savedvalueentry AS e} }}
)
summary
-- SavedValues per item
SELECT
{s:modifiedItem} AS "item",
COUNT({s:pk}) AS "total",
MIN({s:modifiedtime}) AS "oldest",
MAX({s:modifiedtime}) AS "newest"
FROM
{SavedValues AS s }
GROUP BY
{s:modifiedItem}
ORDER BY
"total" DESC
-- orphaned SavedValueEntry
-- (there shouldn't be any)
SELECT
COUNT({e:pk}) AS "total",
MIN({e:modifiedtime}) AS "oldest",
MAX({e:modifiedtime}) AS "newest"
FROM
{SavedValueEntry AS e
LEFT JOIN
SavedValues AS s
ON {e:parent} = {s:pk} }
WHERE
{s:pk} IS NULL |
A lot of those items accumulated over the project lifetime.
If possible, disable storing saved values. ( |
| SolrIndexOperation |
SELECT
{i:qualifier},
COUNT({o:pk}) AS "total",
MIN({o:modifiedTime}) AS "oldest",
MAX({o:modifiedTime}) AS "newest"
FROM
{SolrIndexOperation AS o
LEFT JOIN
SolrIndex AS i
ON {o:index} = {i:pk} }
GROUP BY
{i:qualifier}
ORDER BY
"total" DESC |
Too many solr operations (more than ~100 per index)? |
| StoredHttpSession |
SELECT
COUNT({s:pk}) AS "total",
MIN({s:modifiedtime}) AS "oldest",
MAX({s:modifiedtime}) AS "newest"
FROM
{StoredHttpSession AS s} |
Excessive amount of session? This is hard to generalize as it highly depends on your site's traffic, but if you are near or over 5 digits, it's probably too much. Simarly, stale sessions (e.g older than a day) don't need to be retained. |
| TaskCondition |
SELECT
COUNT({tc:pk}),
MIN({tc:modifiedtime}) AS "oldest",
MAX({tc:modifiedtime}) AS "newest"
FROM
{TaskCondition AS tc }
WHERE
{tc:task} IS NULL |
Is there an excessive amount of "premature events"? Or very old (older than a a few weeks) events? |
Please open an issue describing your problem or your feature request.
Any and all pull requests are welcome.
Please describe your change and the motiviation behind it.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent