Siebel EAI >  SBL-EAI-04451 when there are 10000 or more records using Web Service

We faced this error during a busy session of EAI. There were a lot of timeout errors on TIBCO side.
There was a good article in Oracle support about this.

Applies to:

Siebel CRM - Version: 8.0 [20405] and later [Release: V8 and later ]
Siebel CRM - Version: 8.0 [20405] and later [Release: V8 and later]
Information in this document applies to any platform.
***Checked for relevance on 07-Dec-2011***
Symptoms

Whenever a service based on EAI Siebel Adapter or EAI UI Data Adapter business service run to
insert a child record when there are already 10000 records associated to the parent,
i.e. Contacts for an Accout, Usert to a Responsibility, an error message is received as the following:

"Method 'NextRecord' of business component 'User' (integration component 'User') for record
with search specification '[Login Name] = "FIRST.LAST@COMP.COM"' returned the following error:
"There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500)"(SBL-EAI-04451)"

"Method 'NextRecord' of business component 'Contact' (integration component 'Related Contact')
for record with search specification
'[Middle Name] = "Cont_MN" AND [Last Name] = "Cont_LN" AND [First Name] = "Cont_FN"'
returned the following error:"There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500)"(SBL-EAI-04451)"

 

Cause

This behavior is expected as the default 'ForwardBackward' execution mode is used to
limit fetched records from the Select statement needed in this kind of operations
(as explained in Document 478896.1 ? 'SBL-DAT-00500: There were more rows than could be returned.
Please refine your query to bring back fewer rows').

In the context of EAI Siebel Adapter and EAI UI Data Adapter business service, the
'ExecutionMode' is a hidden input argument for Insert methods, and included in
Bookshelf > Integration Platform Technolgies: Siebel Enterprise Application Integration >
EAI Siebel Adpter Business Service > EAI Siebel Adapter Business Service Method Arguments,
Table 23 and Table 24 with the following description:

"Used to set the direction of a query on a business component. Valid values are ForwardOnly and Bidirectional. The default is Bidirectional.

ForwardOnly is more efficient than Bidirectional, and should be used in cases
where you need to process a large number of records in the forward direction only (such as for report generation).

For more information on executing queries, see the topic on the ExecuteQuery business
component method in Siebel Object Interfaces Reference"

As opposite to 'ForwardBackward' value, 'ForwardOnly' does not restrict
the number of fetched records during the Select statement.

 

Solution

In order to implement the 'ForwardOnly' execution Mode in EAI Siebel Adapter
or EAI UI Data Adapter services, the argument 'ExecutionMode' has to be added in the method's arguments.

For instance, using 'Siebel Account' Business Service (based on EAI Siebel Adapter), InsertOrUpdate method to link Contacts to an Account:

a. From Siebel Tools, Business Services, select 'Siebel Account' and expand it.
b. From Business Service Method, select InsertOrUpdate.
c. Select Business Service Method Arg.
d. Add a new Argument with name 'ExecutionMode'.

If needed, publish again your service (Web Service, JCA Code Generated).

When invoking the service, set the value 'ForwardOnly' to argument 'ExecutionMode'.

Repeat the required call. This will enable the option not to limit fetched records and allow the Insert operation for the child record.


Note

The following situation was observed using this Mode.

Using ExecutionMode=ForwardOnly there was an issue trying to change the primary of a MVG ex. IsPrimaryMVG="Y" on a Position of an Account.

When siebel tried to update the parent record (Account) it failed because the cursor is
in ForwardOnly mode. So it could not get back to the parent record to update the primary position returning the error:

"An end of file error has occurred. Please continue or ask your systems administrator to
check your application configuration if the problem persists".

So, when including MVG, this mode should not be used.

The error message SBL-DAT-00500 is described in another document (ID = 478896.1)
These are the contents

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

Explanation

1. You performed a query or search which is trying to return more rows than is allowed in a result set.

2. MaxFetchArraySize or MaxCursorSize parameter values may be set too low.

3. Maximum Cursor Size property may be set too low for a specific business component.
Corrective Action

1. Modify your query or search specification to return fewer rows in the result set.
If not user initiated, check any running workflows, scripts, EAI, charts, or reports that may be executing the query.

2. Increase the rows allowed in your result set by modifying the value of MaxFetchArraySize or
MaxCursorSize. This will affect the entire Siebel application. For MaxFetchArraySize, values are:
1 - 9,999 (number of rows)
0 (10,000 rows)
-1 (unlimited rows)

For instructions on the MaxFetchArraySize parameter, refer to Siebel Bookshelf version 7.5.3 >
Siebel Object Interfaces Reference > Interfaces Reference > Business component Methods >
Execute Query and Document 477558.1. For more information on Named Subsystem Parameter,
please refer to Siebel Bookshelf version 7.8 > Siebel System Administration Guide >
Application Object Manager Administration > About Siebel Application Object Manager Parameters >
About AOM Named Subsystem Parameters and Siebel Bookshelf version 8.1 Siebel System Administration Guide >
Application Object Manager Administration > About Siebel Application Object Manager Parameters > About AOM Named Subsystem Parameters.

For instructions on the MaxCursorSize parameter, refer to Siebel Bookshelf version 7.5.3 >
Siebel Web Client Administration > Configuration Parameters > Data Source Parameters,
Siebel Bookshelf version 7.8 > Siebel System Administration Guide > Parameters in Configuration File >
Data Source Parameters and Siebel Bookshelf version 8.1 >
Siebel System Administration Guide> Siebel Application Configuration Parameters > Data Source Parameters.

Caution: Setting DSMaxFetchArraySize to -1 may cause large memory use, growth of the
Siebel Object Manager process, and the Siebel Object Manager to crash from memory exhaustion.

3. Increase the number of records requested for a specific business component by modifying the
values of Maximum Cursor Size property for the specific business component.
This will only affect operations performed on the specific business component.
For instructions on how to set the Maximum Cursor Size property in Siebel Tools,
refer to Siebel Bookshelf version 7.5.3 > Object Types Reference > Siebel Object Types >
Business Component, Siebel Bookshelf version 7.8 > Object Types Reference > Siebel Object Types >
Business Component and Siebel Bookshelf version 8.1 > Object Types Reference > Siebel Object Types > Business Component.

Maximum Cursor Size property is used only on DB2 UDB for up to version 8.0.
Starting with 8.1, Oracle DB is also supported. (refer to bookshelf section mentioned above).