Troubleshooting
Problem
YFS_ORDER_AUDIT_DETAIL growing exponentially in size
Symptom
Sterling HF61 includes a change that will allow selective updates to the
AUDIT tables for changeOrderStatus transactions. This will allow customerss to
turn off auditing for non-business critical transactions like Out-of-sequence
updates that constitute a large majority of audit transactions. (Sterling
support is investigating whether this particular change can be isolated and a
separate jar file with ONLY this change can be supplied to customer for
testing.)
Make a configuration change to stop the XML LONG column value
from being written to the history table when the Order gets purged from
transaction to history. This will stop the exponential growth of the history
tables and is a feature that is already available in Yantra 7.11. Re-org the
transaction tables once they reach steady-state. This will buy us around
500-600GB of disk space but will require production downtime to implement. NULL
all the existing XML LONG values in the history tables. This option needs to be
tested thoroughly to make sure the restoreOrder and changeOrder APIs do not
break.
Resolving The Problem
1> On sql script file add these sql commands.
DELETE FROM
YFS_ORDER_AUDIT_DETAIL d WHERE d.ORDER_AUDIT_KEY IN (SELECT v.ORDER_AUDIT_KEY
FROM YFS_ORDER_AUDIT_LEVEL v WHERE v.MODIFICATION_TYPE = 'CHANGE_STATUS');
COMMIT;
DELETE FROM YFS_ORDER_AUDIT d WHERE d.ORDER_AUDIT_KEY IN
(SELECT v.ORDER_AUDIT_KEY FROM YFS_ORDER_AUDIT_LEVEL v WHERE
v.MODIFICATION_TYPE = 'CHANGE_STATUS');
COMMIT;
DELETE FROM
YFS_ORDER_AUDIT_LEVEL WHERE MODIFICATION_TYPE = 'CHANGE_STATUS';
COMMIT;
DELETE FROM YFS_ORDER_AUDIT_DETAIL_H d WHERE d.ORDER_AUDIT_KEY
IN (SELECT v.ORDER_AUDIT_KEY FROM YFS_ORDER_AUDIT_LEVEL_H v WHERE
v.MODIFICATION_TYPE = 'CHANGE_STATUS');
COMMIT;
DELETE FROM
YFS_ORDER_AUDIT_H d WHERE d.ORDER_AUDIT_KEY IN (SELECT v.ORDER_AUDIT_KEY FROM
YFS_ORDER_AUDIT_LEVEL_H v WHERE v.MODIFICATION_TYPE = 'CHANGE_STATUS');
COMMIT;
DELETE FROM YFS_ORDER_AUDIT_LEVEL_H WHERE MODIFICATION_TYPE =
'CHANGE_STATUS' ;
COMMIT;
Historical Number
NFX6146
Product Synonym
[<p><b>]Severity[</b><p>];Normal;[<p><b>]Type[</b><p>];NormalFix
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21553196