Debugging Email Issue : Mail Profile Parameter

One of my friend called me up this morning and said somehow emails stop working in Production Env. And when I asked about the issue in detail, he told me that any of the email via Workflow Policy is not going, he tried lot many times but none of the user has received any of the email but if use F9 functionality, email was firing properly.

hmmmmm !!! Okayy... not a problem, lets start the debugging and lets see where exactly the issue exist.

1. Since F9 functionality is working that means Siebel Server is successfully creating an SMTP request using the Communication Profile (that we generally used in Production Env).

2. Do some operation, so that Workflow Policy gets trigger.

3. Checked whether policy gets trigger or not, using the below SQL statement :

select * from s_escl_req

where rule_id = "Policy Row Id"

record was existing in the table and checked the BT_ROW_ID column has the rowid of the record for which policy triggered.

4. Lets wait for 1 min, (generally 1 min is the sleeping time of all monitor agents) and lets see whether it is getting processed by monitor agent or not.

5. Yes, after around 1 min, the record gets deleted from S_ESCL_REQ and a new record gets created in S_APSRVR_REQ table for sending an email to the user with status "Queued".

6. Uptill now everything seems working fine and now Email Manager should pick this request from S_APSRVR_REQ table and should send an email. Lets wait for 1 min more.

7. Ooops... record is still existing in S_APSRVR_REQ table with the status "Queued". That means Email Manager has some issue and it is not picking the request from this table. Okayy.. now lets see what Mr. Email Manager is doing right now [may be on vacation :) ]

8. Navigate to "Administration -> Server Management -> Enterprises". Query for "Email Manager" and found that it was there and the status was online. It seems working, drilldown on it and navigated to "Tasks" view and no error log was being generated for it. But since it is running then it should process the record in S_APSRVR_REQ table. And one more point is we are successfully sending emails via F9 functionality.

9. Okayy, now lets check which email communication profile this "Email Manager" is using.

10. Navigate to "Administration - Server Configuration -> Enterprises -> Component Definitions". Query for "Email Manager". Query for "Mail Profile" under Parameters list.

11. And yes, we found something strange. "Profile Name" was somehow blank. Don't know the reason but since it is not having Communication Profile mentioned to process the request, it is not doing its job perfectly.

12. Put the same communication profile name which we were using for F9 functionality. Restart the Siebel Server and found all emails which got stuck in "Queued" status in S_APSRVR_REQ table, has successfully processed and has the status "Success" now.

So finally, issue get resolved.

FirstRecord() in PreCanInvoke Event : Refresh Issue

Today I am going to tell you about an interesting issue that we faced in our Production Env and really worked hard to figure it out the root cause for it.

The issue was :

On Activity Form applet, we have "Comments" field, which is 2000 characters long and user to write a detail description in this field related to that activity record. Now few of the users faced the issue that "while typing some text in Comments field, suddenly the whole text written get deleted and Comments field become blank". User get frustrated as he need to type in the whole text again and also the next time not sure whether he will able to complete putting text in Comments field or not. For a temporary workaround they used to first type in all the text in a NotePad file and then copy paste the complete text at once in Comments field and save the record.

What we tried :

When we tried replicating this issue in our dedicated env, we were unable to replicate it. After lot much of effort our QA Team was able to replicate it sometimes on our thin client. But the problem was this is not always happening with them as well. On some of the machines we were not able to replicate it at all. Totally a random behaviour.

Now the problem was to find out the steps so that we can replicate it. We have one option to try i.e. by disabling all the Server Script and check if we can replicate the issue in thin client (since in dedicated client everything was working fine).

We changed the "EnableScripting" = False and restart the Siebel server, and found that the issue is not coming anymore. So we narrowed down for the root cause of the problem to Scripting.

And, here is what we finally found :

On PreCanInvoke method of the applet we had a script written, something like :

if (MethodName == "Test")

{

if(this.BusComp().FirstRecord())
CanInvoke = "True";
return (CancelOperation);
}

So, what was happening when user was typing some text in Comments field, it seems like IE web page get refreshed and due to this the code written on "PreCanInvoke" method was getting invoked and due to the "FirstRecord()" method used inside the code, the current record get refreshed and resulting in no text in Comments field.

Finally, we were able to find the root cause of the problem, now its the time to look for some workaround. So we decided not to enable/disable button using PreCavInvoke script, rather put a check message in PreInvoke method when there is no record displayed. So here below is final version of script we used :

PreCanInvoke Method

if (MethodName == "Test")
{

CanInvoke = "True";
return (CancelOperation);

}

PreInvoke Method :

if(MethodName == "Test")

{

if(this.BusComp().FirstRecord())

{

TheApplication().RaiseErrorText("This operation is not valid, when record is not displayed");

}

return (CancelOperation);

}

Recommendation:

Avoid writing scripting on PreCanInvoke Method of Applet, as this is the event which fires on each and every action we do on the applet + at the time of automatic refresh of web page.

Exporting records from List Applet in Siebel

Debugging an issue, which is completely related to a Vanilla process in Siebel, is really a difficult task. Moreover, I will say that, for this particular issue you won't find anything in Object Manager log, then you might start scratching you head in thinking how should I move further now to debug this issue.

One of our application user was trying taking an export of records from list applet and he was getting "The Page cannot be displayed" error and before this error Siebel screen blinks for sometime along with the export window and user has to forcefully kill the Siebel session. The record count was around 8K and most surprising fact about the issue was when I was trying taking export from my machine for the same exact set of records, it was working fine. I tried on some more machines and I was able to take the export successfully. The question came into my mind : "Is Export record functionality is machine specific??"............ frustration !!!!

==> Export on Dedicated Client was working fine.

==> Export on Thin client from my machine was working fine.

==> Export on Thin client from some more machine (where I tried) was working fine.

==> No clue in the Object Manager log.

==> No Siebel Error on the UI. The error what user was getting : "The Page cannot be displayed".

Now, its the time to do a Screen sharing session with the user and see what exactly is happening.

I asked the user to take the export for around 100 records and see whether export is at all working on his machine or not. And to the surprise, it worked. That means :

1. There is something wrong with the data thats why it is not getting exported.

OR

2. The data size being exported is so huge in size that Siebel cannot handle.

To investigate the first point mentioned above (From User's machine)

a) I prepared a query which will fetch exact records and columns from the database that user wants to export. Ran the query in SQL Developer and exported the data.

b) Scroll into the list applet uptill the last record to check if some junk is stopping the export process. Because if any junk data would have stopping the export process then it would certainly stop in scrolling as well. But what I observed is that, no issues in scrolling uptill the last record.

So, first point metioned above is not the culprit.

To investigate the Second point mentioned above (From User's machine)

While export process, Siebel pulls out data in chunks of 1000 records. Keep that chunk in some temporary space and then pulls the next 1000 records. This is the way it works. So it seems to be issue with less temporary space and its nothing but Internet Temporary File space.

So, to check for it, go to : "IE -> Tools Menu -> Internet Options -> General Tab -> Settings". Check for the size of "Temporary Internet Files Folder" and got surprised when I see that number was only 2 MB. The recommendation is 640 MB.

So, finally we found the culprit and corrected the temporary files folder size to 640 MB and tried taking the export.

And Everything worked fine........ hhhhoooo. Yes. We did it again !!!! Issue closed with resolution :)

.

Error retrieving next record from the database.

Let me tell you a story of one of the change request that we did in our Siebel application and became a learning lesson for us.

While navigating to "All Quotes View", system was giving the error : "Error retrieving next record from the database. (SBL-DBC-00104)". But when I tried navigating to "My Quotes View", no issues. Since the Applet, Business Component, Business Object, PDQs etc etc, being used in both the views were the same, the only thing which was different is "Data". That means there is something wrong with the data which is creating this problem.

So, as generally we do, I checked the Siebel Object Manager log and tried to find out what exactly was happening in the background. I queried for error code "(SBL-DBC-00104)" in the log file and found an Oracle code with error saying : "ORA-24345 Truncation or Null Fetch Error".

I checked for the possible reason due to which Oracle returns this error. Oracle says : "Please ensure that the buffer size is long enough to store the returned data.".

That means I was going into the right direction and data which is being displayed in "All Quotes View" was creating this problem. If I could relate this particular issue with Siebel, we have one Field User Property : "Text Length Override", which restricts the field to display some specific number of characters, no matter what is there in the database.

So, when I checked for all the fields in "Quote" business component, there was one field "Comments" which has been restricted to show only 500 charcters while its length was 1000. Now the only thing I need to confirm was if there is any record in S_DOC_QUOTE table having comments > 500 characters.

select row_id, comments

from S_DOC_QUOTE

where len(comments) > 500

And I was lucky to find one. So I just truncated it to 500 characters and again tried navigating to "All Quotes View" and eveything worked fine.

Learning Lesson: Before applying "Text Length Override" field user property, please make sure you should not have any data in the database which is voilating this constraint.

.

Dedicated client's spool file

Spool file generated by dedicated client is one of the unique feature which Siebel provides and help Siebel Developers while debugging many Performance issues in the application.
For every action we perform in Siebel, like navigate to view, create a new record, query for some data etc, Siebel generates a SQL query which runs at the database and data is being displayed on the UI.

Siebel spool file is a text file that contains each query executed at the database. So whenever you see any performance issue (like navigation on some view is taking time, query on some applet taking time), you can use this file to check for the query which is culprit and responsible for the delay.

We just need to perform the below mentioned steps to make the spool enable in the system :
a) Right click on the icon, which is being used to open the dedicated client.
b) Click on Properties, system will popup the icon's properties window.
c) Append the below mentioned line in "Target" section :
/s c:\spool.log
d) Click on Ok.
e) Open a new Siebel dedicated client session.

Now you will see a file gets generated with a name "spool.log" which contains all the query which is being executed at the database.

How I can use it for performance Issue

As soon as you see any performance issue in the application, take the same SRF that is being used in thin-client and open a Siebel dedicated client session with the same SRF after enabling the Spool.
Perform the same action resulting performance issue, in dedicated client. Once you see the data on the UI (lets say after 10-15 mins), open the spool file and check for the query which is taking such big amount of time. This way you will get to know which the culprit query is and run that query on the database directly to check if there is something wrong with the query.
If yes, you need to check for the configuration done last which is making query to perform slow. If No, then your DBAs might help you in improving the performance by creating any new index or regenerating the table/index statistics, if required.

A Story of a Dynamic Drilldown Issue

Don't worry I am not going to narrate any story out here like there was a king who's name was "drilldown" and there was a queen :) . There is really a nice and hidden fact that people rarely know when they are configuring Dynamic Drilldown in an applet. I came across this fact when we got stuck in one of the Production issue which was being faced by only one user and for rest of the users that dynamic drilldown was working fine.

So, the story goes this way : In our Siebel application, we had configure a dynamic drilldown on field "Opportunity Name" which exist on list applet "Quote List Applet" in My Quotes View. The drilldown was dynamic in nature as depending upon the "Business Type" of Opportunity, system can drilldowns to various views. For Eg:

Business Type ================= View to drilldown ===========

Manufacturing =================== Opportunity View - Manufacturing

Retailing ======================= Opportunity View - Retailing

Production ===================== Opportunity View - Production

Service ======================== Opportunity View - Services

and in-case no business type has been defined for an Opportunity, then system should drilldowns to "All Opportunities View" view. That means "All Opportunities View" was the default view for this dynamic drilldown.

Now, the issue faced by the user in Production env was, whenever he tries to drilldown on "Opportunity Name" field in "Quote List Applet", he was getting the error "You don't have priviledge to see this record" and seeing this error he got shocked as the record he wanted to see was created by him only. He can very well see that Opportunity record in "My Opportunities" view with no issues. But the only thing that was not working is the drilldown.

Now, after getting this issue, the first thing that will come into the mind is : "just go and check if "Opportunity View - Retailing" view" is available under user's responsibility or not and when we checked, we found it was there. We took a deeper dive and checked the drilldown for the same user for Business Type = "Manufacturing" (already confirmed "Opportunty View - Manufacturing" view was there under the responsibility), but again the same error "You don't have priviledge to see this record"........ So, no matter what the "Business Type" is, he was not able to drilldown on any of the Opportunities.

After lot much of head-scratching, we observed that there was a difference between the number of views available under the responsibility for the user who was able to do the drilldown and which one not. The difference was : "the default view of the dynamic drilldown was not available under the responsibility of the user". Somehow it got deleted or something, not sure how it was happened, but there on we come to know this basic learning while implementing dynamic drilldown, i.e.

"Don't forget to include the default view of the dynamic drilldown under the user's responsibility, otherwise it will not work".

.

.

cheers :)

try it out