Thursday, August 27, 2009

Error while running blank query on an applet !!

Today while working on one of the issue in UAT env, when I navigated to Assets screen to see list of all assets, ran a blank query, I got the following error :

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

In the very first sight, it seems query is bringing a large number of records which Siebel can't handle. hmmm... okayy, lets try running a query which will bring less records so I put a query on "Type" field on the applet and ran the query and it worked fine. That means there is some limitation in Siebel while fetching the records and if the number of records goes beyond that limit it gives up. So, one might ask what is that upper limit??

Upper limit for number of records that Siebel can pull from a single query is equal to the value set for "MaxCursorSize" parameter available in your siebel.cfg file. Here below can be the values :

a) 0 : If this parameter set to 0 (zero), that means Siebel can fetch 10,000 records in a single query. This is the recommended value.

b) -1 : it indicates infinite number of records, results in low performance and not recommended by Siebel.

c) >0 : any number greater than 0 will fetch that many records in one query.

So, what happens is if you run a query on UI and you try to scroll the records and crosses the limit what has been set by "MaxCursorSize" parameter, you will get the above mentioned error. Moreover, the parameter is applied for each and every query that Siebel runs with the exception that it might override by "Maximum Cursor Override" property at the business component level.

I checked for the value of "MaxCursorSize" parameter in CFG and it was set to 0. Just to cross verify I also ran a query in the database and confirmed that number of records in S_ASSET records were more than 10,000 records. But, wait a minute, I didn't even scroll once on the Asset Screen and I just tried to navigate on the default view of the screen and still I received this error. This is something else is going on here, isn't it?

One more thing to notice here is that I realized that even I have more than 10,000 Accounts records in the application, but there is no issue when I navigate to All Accounts view. That means something special does exist with Asset business component which is causing this issue. And here below is the reason for it :

"Hierarchy Parent Field" property of business component was set to "Parent Asset Id" and due to this while running a blank query on the Assets UI, resulted in putting a "/*+ ALL_ROWS */" hint in the SQL that Siebel runs in the background. This is the difference I observed when I just removed the "Hierachy Parent Field" and compiled the SRF again and blank query worked fine this time. No issues. But this doesn't mean that this is the solution for the issue, you can't just remove this property to avoid this error because it get reflect in all the applet based on this business component. So here is the solution for this :

Use "Disable Buscomp Hierarchy", a user property available on the applet and set its value to True. Since it is applet based user property, only applies to the applet on which it is used.

Disable Buscomp Hierarchy = True

What it does is : it will just ignore the effect of "Hierarchy Parent Field" on the business component and run the query without "/*+ ALL_ROWS */" hint in the background to bring the records as per the normal process.

I put this user property on the All Assets List Applet and ran a blank query again, everything worked fine.

Hope it helps !!
.

12 comments:

  1. what version is this error? I am in 7.7 and setting this applet user prop was vanilla.

    ReplyDelete
  2. I am working on 8.1
    Not sure whether this user property is available in 7.7, you need to give it a shot if it works there as well.

    ReplyDelete
  3. Anyone got this going in Siebel 7.7?

    Cheers,
    KB

    ReplyDelete
  4. Also - does thsis BC user Prop only works if the Database platform is DB2?

    ReplyDelete
  5. @KB

    I have used it with Oracle.

    ReplyDelete
  6. On Reading this, I recollected another user property called "Dynamic Hierarchy Parent Field Id". When we had problem in Merge functionality of Accounts, we inactivated the BC User Propery "Dynamic Hierarchy Parent Field Id" of Account BC and Merge of Accounts worked fine.

    ReplyDelete
  7. Thanks for your comments Rekha !! Could you also please share with us the exact error/issue that you were facing while using this user property?

    ReplyDelete
  8. Gaurav,
    We did not get any error message, but merge of accounts didn't happen as expected.when we try to merge account A with account B, it gives a confirmation message that A will be merged with B and when we gave OK to the confirmation popup, it looks like account A merged, but nothing happens. when we refresh the view we saw account A still survives and there was no change with account B too.

    On looking into the Siebel Support web we found that for Merge of Accounts there has to be a Default Account Hierarchy in the Global Accounts Administration view. As a workaround siebel suggested to inactivate the user property "Dynamic Hierarchy Parent Field Id" in Account BC which actually makes it to search for the hierarchy under Global Admin view, we did teh same and later Merge of 2 accounts worked as expected.

    Reference SR: Cannot merge records in Accounts [ID 541335.1]
    Link: https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocID=541335.1&from=BOOKMARK&viewingMode=1143&bmDocType=PROBLEM&bmDocDsrc=DOCUMENT&bmDocTitle=Cannot%20merge%20records%20in%20Accounts))

    But this happened long back in 2007 in our Siebel version 7.8.2.3 not sure if its the same case in Siebel 8.

    ReplyDelete
  9. Thankyou for the information Rekha !! Really appreciated !! Hope this info will help some users here

    ReplyDelete
  10. Excellent explanation ... Solved my problem immediately ... Thanks Gaurav.

    ReplyDelete
  11. It works! Thank you for the information!

    ReplyDelete