SBL-DAT-00500 and SBL-EAI-04451: There were more rows than could be returned. Please refine your query to bring back fewer rows.

Reason:

By default any business component query with ForwardBackward mode can fetch up to 10000 records at a time. If any query (generated by eScript or Workflow or any other way) returns more than 10000 records, Siebel throws this error ‘There were more rows than could be returned.’

How to fix:

Below we have mentioned 3 solutions to fix this error. You could choose one as per your requirement.

Solution 1: Use ExecuteQuery with ForwardOnly mode

This is the best solution if it fulfills your requirement. Oracle also suggests not to use ‘ForwardBackward’ mode with ExecuteQuery if it is not absolutely required. Instead of ‘ForwardBackword’, use execute query with ‘ForwardOnly’ mode that does not limit on fetching records.

Solution 2: Refine query

If you have to use ‘ForwardBackward’ mode to meet the requirement, try to add few more conditions so that Siebel fetches less than 10000 records at a time.

If there is an absolute need of fetching more than 10000 records using ‘ForwardBackward’ query mode, then only go for solution 3.

Solution 3: Change ‘DSMaxFetchArraySize’ parameter value

DSMaxFetchArraySize is a subsystem parameter that determines the number of records a business component query with ‘ForwardBackword’ mode can fetch at a time. By default, DSMaxFetchArraySize has value ‘0’ that means business component can return only 10000 records in ForwardBackward query mode. It does not restrict the number of records in ForwardOnly execution mode. If you want to get all rows from ForwardBackward query, set DSMaxFetchArraySize parameter value as -1 (negative 1). But you do it only when you don’t have any workaround because setting DSMaxFetchArraySize to -1 has impact on application performance due to large memory use. Even Siebel Object Manager could crash because of memory exhaustion.

To change this parameter value, follow below steps:

• Login into Siebel application as Administrator
• Navigate to Administration-Server Configuration > Enterprises
• Under ‘Profile Configuration’ tab, search for the profile ‘Server Datasource’
• In the Profile Parameters list, click on ‘Advanced’ button to get all advanced profile parameters
• Query on Alias field for DSMaxFetchArraySize and set the value to -1
• Restart Siebel Server services

Beside ‘DSMaxFetchArraySize’, you check ‘Maximum Cursor Size’ property at Business Component level also. This property also restricts the number of records that Siebel CRM can fetch from server. If ‘Maximum Cursor Size’ property contains any value, it overrides MaxCursorSize parameter, defined in the configuration file. This parameter is applicable only on Oracle or DB2 database.

– Set ‘Maximum Cursor Size’ property value to -1 if you want records until Siebel encounters an end of file
– Set it to ‘0’ to get 10000 records
– If you set any number greater than 0, Siebel fetches that many number of records

Same as DSMaxFetchArraySize, this property also has performance impact. So before setting this property value to -1, check all other possibilities to avoid it.

If your Siebel application is using IBM DB2, then you need to check another two parameters – DSPreFetchSize and DSMaxCursorSize.

Do you have any question? Please feel free to comment below.

Keep in touch, follow TechOneStop on Facebook / Twitter / LinkedIn / Goolge+.

<< The method IsPortableUIMode is not supported            Error has occurred executing a Sql >>

Here we have listed down few frequently asked Siebel EIM interview questions. If you want to add more Siebel interview questions, please feel free to comment below.

Siebel EIM Interview Questions :

1. What is EIM?
   – This question is pretty basic but let me tell you, we have seen many candidates who are not able to answer it properly. You prepare this answer in such a way that shows your work experience and in depth knowledge about Siebel EIM. Interviewers don’t look for full form of EIM or similar kind of answer.
2. Tell me a business scenario where you have used EIM.
3. What all entities have you worked on? what will be your approach to import data from a flat file to Siebel base table ?
   – Prepare this question well, you will face it almost in every Siebel EIM interview.
4. Have you faced any difficulties during EIM and how did you solve those ?
5. Have you seen any error during EIM process? How did you fix it?
6. Difference between EIM and EAI – when will you prefer to use EIM over EAI ?
7. What all operations you have handled in EIM ?
8. How to find out the best interface table to populate a base table in Siebel EIM?
9. What all steps do you follow in EIM data mapping ?
10. Is there any mandatory column/s for EIM table ?
11. How do you populate the EIM table by external data ?
12. Have you imported List of values using EIM ?
13. How to do primary (explicit/Implicit) mapping using EIM ?
14. How do you know that the EIM process has been executed properly ?
15. Tell me few parameters that you have used in IFB file.
16. Tell me few EIM best practices that you have followed.
17. Have you worked on PL/SQL? How did you validate input data file in EIM ?
18. Performance tuning in EIM

Share your feedback on Siebel EIM interview questions, please feel free to comment below.

Keep in touch, follow TechOneStop on Facebook / Twitter / LinkedIn / Goolge+.