Troubleshooting
Problem
Attempts to run syncLoadedInventory API at the rate of 20,000 calls per hour lead to High DB CPU in IBM Sterling Order Management.
Symptom
The following query took close to 20 seconds for execution:
SELECT /* YANTRA */ YFS_INVENTORY_SUPPLY_TEMP.QUANTITY AS
DIFF_QUANTITY, 0
AS ACTUAL_QUANTITY, YFS_INVENTORY_SUPPLY_TEMP.QUANTITY AS
EXPECTED_QUANTITY, YFS_INVENTORY_SUPPLY_TEMP.*
FROM YFS_INVENTORY_SUPPLY_TEMP YFS_INVENTORY_SUPPLY_TEMP WHERE NOT
EXISTS ( SELECT 1 FROM
YFS_INVENTORY_SUPPLY YFS_INVENTORY_SUPPLY WHERE YFS_INVENTORY_SUPPLY.
INVENTORY_ITEM_KEY = YFS_INVENTORY_SUPPLY_TEMP.INVENTORY_ITEM_KEY
AND YFS_INVENTORY_SUPPLY.INVENTORY_TAG_KEY = YFS_INVENTORY_SUPPLY_TEMP.
INVENTORY_TAG_KEY
AND YFS_INVENTORY_SUPPLY.TAG_NUMBER = YFS_INVENTORY_SUPPLY_TEMP.
TAG_NUMBER
AND YFS_INVENTORY_SUPPLY.SHIPNODE_KEY = YFS_INVENTORY_SUPPLY_TEMP.
SHIPNODE_KEY
AND YFS_INVENTORY_SUPPLY.SUPPLY_TYPE = YFS_INVENTORY_SUPPLY_TEMP.
SUPPLY_TYPE
AND YFS_INVENTORY_SUPPLY.SEGMENT = YFS_INVENTORY_SUPPLY_TEMP.SEGMENT
AND YFS_INVENTORY_SUPPLY.SEGMENT_TYPE = YFS_INVENTORY_SUPPLY_TEMP.
SEGMENT_TYPE
AND YFS_INVENTORY_SUPPLY.ETA = YFS_INVENTORY_SUPPLY_TEMP.ETA
AND YFS_INVENTORY_SUPPLY. SHIP_BY_DATE = YFS_INVENTORY_SUPPLY_TEMP.
SHIP_BY_DATE
AND YFS_INVENTORY_SUPPLY.SUPPLY_REFERENCE_TYPE =
YFS_INVENTORY_SUPPLY_TEMP.SUPPLY_REFERENCE_TYPE
AND YFS_INVENTORY_SUPPLY.SUPPLY_REFERENCE = YFS_INVENTORY_SUPPLY_TEMP.
SUPPLY_REFERENCE
AND YFS_INVENTORY_SUPPLY.SUPPLY_LINE_REFERENCE =
YFS_INVENTORY_SUPPLY_TEMP.SUPPLY_LINE_REFERENCE
AND YFS_INVENTORY_SUPPLY.AVAILABILITY_TYPE = YFS_INVENTORY_SUPPLY_TEMP.
AVAILABILITY_TYPE
AND YFS_INVENTORY_SUPPLY. SHIPNODE_KEY = 'Node1' ) AND
YFS_INVENTORY_SUPPLY_TEMP. SHIPNODE_KEY = 'Node1'
AND YFS_INVENTORY_SUPPLY_TEMP.SUPPLY_TYPE IN ( SELECT STYPE.SUPPLY_TYPE
FROM YFS_INVENTORY_SUPPLY_TYPE STYPE
WHERE STYPE.ONHAND_SUPPLY='Y' ) AND YFS_INVENTORY_SUPPLY_TEMP.
YANTRA_MESSAGE_GROUP_ID = '123';
Cause
The number of records in YFS_INVENTORY_SUPPLY_TEMP was very high and ran into millions.
Resolving The Problem
To resolve, run purge agent to remove old YFS_INVENTORY_SUPPLY_TEMP records. This would automatically reduce the time taken by the above query.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21979953