IBM Support

Table YFS_USER_GROUP_LIST does not fetch value from Cache

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:

  1. Enable cache for table YFS_USER_GROUP_LIST.

  2. 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

[{"Product":{"code":"SS6PEW","label":"Sterling Order Management"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Performance","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.3","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
16 June 2018

UID

swg22002153

Page Feedback