Troubleshooting
Problem
As part of certain APIs like createOrder and importOrder, the below query is fired by the product-
SELECT YFS_PERSON_INFO.* FROM YFS_PERSON_INFO YFS_PERSON_INFO WHERE TITLE = ' ' AND FIRST_NAME = ' ' AND MIDDLE_NAME = ' ' AND LAST_NAME = ' ' AND SUFFIX = ' ' AND DEPARTMENT = ' ' AND COMPANY = ' ' AND JOB_TITLE = ' ' AND ADDRESS_LINE1 = ' ' AND ADDRESS_LINE2 = ' ' AND ADDRESS_LINE3 = ' ' AND ADDRESS_LINE4 = ' ' AND ADDRESS_LINE5 = ' ' AND ADDRESS_LINE6 = ' ' AND STATE = ' ' AND CITY = ' ' AND ZIP_CODE = ' ' AND PERSON_ID = ' ' AND COUNTRY = 'US' AND DAY_PHONE = ' ' AND EVENING_PHONE = ' ' AND MOBILE_PHONE = ' ' AND BEEPER = ' ' AND OTHER_PHONE = ' ' AND DAY_FAX_NO = ' ' AND EVENING_FAX_NO = ' ' AND EMAILID = ' ' AND ADDRESS_ID IS NULL AND ALTERNATE_EMAILID = ' '
This query can be expensive in certain cases and can impact the overall response time of the APIs
Cause
The DB does not have optimal indexes on yfs_person_info to help the query.
Diagnosing The Problem
The out of the box product has a default index on the column EMAILID.
Based on the common attributes being passed in the input, it is recommended to have a custom index created on specific columns.
Resolving The Problem
For instance, the below index will help if mobile number and day phone are frequently being passed in the input-
CREATE INDEX "OMDB"."EXTN_PERSON_INFO_IN2" ON "OMDB"."YFS_PERSON_INFO" ("ADDRESS_ID" ASC,"EMAILID" ASC,"DAY_PHONE" ASC, "MOBILE_PHONE" ASC, "COUNTRY" ASC, "ZIP_CODE" ASC, "LAST_NAME" ASC, "FIRST_NAME" ASC ) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS
Post index creation, please review with the DBA to ensure the query execution is picking the new index created.
For IBM Order Management on cloud (OMoC), please reach out to IBM Support if DBA assistance is needed to review the query performance
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS6PEW","label":"Sterling Order Management"},"ARM Category":[{"code":"a8m0z000000cy01AAA","label":"Performance"}],"ARM Case Number":"TS009068708","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
10 May 2022
UID
ibm16574023