Troubleshooting
Problem
Attempts to run getUserList does not fetch values from YFS_USER_GROUP_LIST table cache in IBM Sterling Order Managment .
Symptom
Steps to reproduce:
- Enable cache for table YFS_USER_GROUP_LIST.
- Call getUserList API (sample Input xml)
----------------------------
<User MaximumRecords="5000" UserKey="xxx"/>
----------------------------
With the following output template
----------------------------
<UserList>
<User Loginid="" Longdesc="" UserKey="" UsergroupKey="" Username="">
<UserGroupLists>
<UserGroupList/>
</UserGroupLists>
</User>
</UserList>
----------------------------
You will see the below query being fired:
SELECT /*YANTRA*/ YFS_USER_GROUP_LIST.CREATEPROGID, YFS_USER_GROUP_LIST.CREATEUSERID,YFS_USER_GROUP_LIST.MODIFYPROGID, YFS_USER_GROUP_LIST.MODIFYUSERID,YFS_USER_GROUP_LIST.CREATETS,YFS_USER_GROUP_LIST.MODIFYTS,YFS_USER_GROUP_LIST.USER_GROUP_LIST_KEY,YFS_USER_GROUP_LIST.LOCKID FROM YFS_USER_GROUP_LIST YFS_USER_GROUP_LIST WHERE YFS_USER_GROUP_LIST.USER_GROUP_LIST_KEY='xxx' AND YFS_USER_GROUP_LIST.LOCKID=0
Cause
Order optimization does not cache housekeeping fields.
Diagnosing The Problem
Issue is seen only when the output template has the following element:
<UserGroupLists>
<UserGroupList/>
</UserGroupLists>.
When user group list objects are fetched for the given userkey, this list of objects are fetched from the database cache. Due to an existing optimization feature, to reduce the memory footprint of the db cache, the housekeeping fields (createts, modifyts, createprogid, modifyprogid, createuserid, modifyuserid) of an order are not stored in the db cache.
Resolving The Problem
Based on the template provided in the API , when XML is created for each user group list, housekeeping fields are not loaded in the object, fresh SQL is fired to fetch these columns from database for this user group list.
If you want to avoid these SQLs, then you can turn off the above optimization so that the db cache stores all column values (including housekeeping columns) inside the object thus the above SQL would not be needed.
- To turn off optimization for table YFS_USER_GROUP_LIST set below property in customer_overrides.properties file
yfs.yfs.unset.dbhk.fields.YFS_USER_GROUP_LIST=false
- To turn this off for all tables, set below in customer_overrides.properties:
yfs.yfs.unset.dbhk.fields=false
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22002153