Troubleshooting
Problem
Unable to acquire lock on YFS_TRANSACTION_LOCK table error after applying MCF 8.5 HF-39
Symptom
The error is because the data type of the
PURPOSE column before 8.5 HF-39 was CHAR(24) and in 8.5 HF-39 as part of the
YFS_TRANSACTION_LOCK fix, the PURPOSE column length and data type changed to
VARCHAR2(100).
The change in column
length pads the existing records in YFS_TRANSACTION_LOCK with white spaces to
match the changed column length.
The Sterling Application processes cannot
acquire locks on YFS_TRANSACTION_LOCK table as they fire an SQL to acquire a
lock with the exact PURPOSE (sans the white space padding). The database
PURPOSE column has extra white/blank space.
Example
SELECT /*YANTRA*/ YFS_TRANSACTION_LOCK.* FROM YFS_TRANSACTION_LOCK WHERE NODE_KEY = 'Node' AND PURPOSE = 'CONSOLIDATE_TO_SHIPMENT' AND ENTERPRISE_CODE = 'DEFAULT' FOR UPDATE;
SELECT /*YANTRA*/ YFS_TRANSACTION_LOCK.* FROM YFS_TRANSACTION_LOCK WHERE NODE_KEY = 'Node' AND PURPOSE = 'CONSOLIDATE_TO_SHIPMENT' AND ENTERPRISE_CODE = 'DEFAULT' FOR UPDATE;
Resolving The Problem
Use the following SQL to update the database to
remove the extra space from the PURPOSE column of the YFS_TRANSACTION_LOCK
table -
update YFS_TRANSACTION_LOCK set
PURPOSE = rtrim(PURPOSE), MODIFYPROGID = '85_TRANLOCK_UPDATE' WHERE
MODIFYPROGID != '85_TRANLOCK_UPDATE';
commit;
commit;
Please note above SQL will be provided with MCF 8.5 HF 42. Above SQL needs to
be executed only if applied HF is greater than HF 39 and lesser than HF 42, and
HF higher than 42 cannot be applied immediately.
[{"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Historical Number
NFX11097
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21556010