Use GetAssocBusComp () with caution!!

Sometimes we write script and never realize it might result in error due to record set on which it is getting operated and it becomes issue in live environment. Similar kind of scenario I observed while using "GetAssocBusComp()". Siebel geeks who have used this method before must be aware that this is being used to get the Association business component and it's "Associate" method is being used to add a record in the MVG.

So, I got a business service being called inside a workflow asynchronously where below small piece of code was failing for some set of records while it was working fine for other records:

var SRId = Inputs.GetProperty("SRId");
var SRBOBusObject = TheApplication().GetBusObject("Service Request");
var SRBC:BusComp = boinactive.GetBusComp("Service Request");
var SerialMVGBC:BusComp;
var SerialAssocBC:BusComp;
with(SRBC)
{

ClearToQuery();
SetViewMode(AllView);
SetSearchSpec("Id", SRId);
ExecuteQuery();
if(FirstRecord())
{

SerialMVGBC = SRBC.GetMVGBusComp("Serial Number");
SerialAssocBC = SerialMBVGBC.GetAssocBusComp();
// While debugging I found that code was failing
// for some records at above step
................
................

}

}

When I debug the issue and checked for the logs, the error I saw was:

No association list is available in this applet.(SBL-DAT-00276)

Strange part was, for some of the records this code was working fine and failing for some records.

While debugging I realize that the record on which this code was failing, was Read-Only. This is the due to the fact that when Service Request status change to "Closed" it gets read-only and the error was quite confusing in the way when it said "No association list is available".

Might be helpful for you in future if you face this error, please check record should not be read-only for any reason, not even due to "BC Read Only Field" user property on the business component.

Note: this is the reason you don't see the "Associate Applet" inside the MVG Shuttle applet configured on any MVF on the UI, if the record is Read-only.

Workaround

1. If you are operating on Service Request business component, then you can make use of "Always Enabled: <_MVL Dest BusCompName>" user property and then GetAssocBusComp() method would work even on read-only records. Drawback of this workaround would be, on the UI user can open the MVG applet and can associate a record in it even if the Service Request record has status = Closed.

2. Another better way is to operate on business component based on the inter-table. And just do a NewRecord().

.

Query performance issue : Dedicated Client Vs Thin Client

Very strange behaviour I observed today while working on a performance issue where whenever user do a query in Purchase Order Pick applet, query was taking more than 1 minute to execute in thin client but the same query behaves perfectly fine in dedicated client.

So while working on any performance issue, you always want to see the performance on dedicated client as well, so I did the same.

1. Took the same SRF running in thin client, and connect it to the Dedicated client.
2. Followed the same steps: open the pick applet and queried the Purchase Order#

To my surprise, on dedicated client the PO# query took only 6 seconds which is quite acceptable performance. So, this is the issue I faced first time when the query is running slow in thin client but not in dedicated. Now, lets try to find out what exactly is going on behind the scene.

I referred the spool file and took out the culprit query. Execution time mentioned in the query was same around 6 seconds. I ran the "Alter Sessions" commands as mentioned in my earlier post and ran the query. It again took only 6 seconds.

***** SQL Statement Execute Time: 6.014 seconds *****

hmmm..... now it looks like everything seems working fine from configuration perspective but why is there a difference in the query execution when run in Dedicated client Vs Thin client???

I was looking into the issue more and found that when I did a query in PO# field with value "1234" and it returned multiple records with PO# like: 1234, 12345, 1234a etc. So, it is quite clear that "AutomaticTrailingWildCards" parameter in the CFG was not set to False. But, anyways the query should perform similar in both the dedicated and thin client, no matter this parameter is set to False or not. Though, this thought is quite logical but even then I wanted to give a try by disabling it. So, I thought of disabling it only on the "PO#" field on the pick applet, so that whenever user do a query on PO# field, system should not put the "like" keyword in the SQL generated at the backend.

I put the following User Property on the business component:

Name : Disable Automatic Trailing Wildcard Field List
Value : Purchase Order Number

I compiled the SRF, tested it in dedicated client, it worked fine. Now it is the time to test it in thin client as well. At this moment I was not sure how it would behave. I put the same SRF on thin client and performed the same steps for querying the Purchase Order#, I got the result in around 6 seconds. A big smile and relief too, but again I don't have the correct explanation yet for this problem why is it happening, but this user property came as the life saver :)

If you ever faced this kind of performance issue and know the exact reason please comment.

Debugging Siebel eMail Response !!

In our Siebel application, we are using OOB functionality for creating Service Requests via emails, and yes I think you guessed it right we are using Siebel eMail Response workflows which are available in Siebel Tools and you just need to configure them as per your need and these works really well.

(For people who are new to this subject, to find these workflows you need to query in Siebel tools as "eMail Response*", and if you want to learn configure these, just follow the simple steps given in Communications Server Administration Guide)

I really like this OOB feature of Siebel as it works like a magic: user sends an email to Siebel email box whichever you have configured, Response Group keeps monitoring that and whenever see a new email just picks it up, delete it from the email box and invoke the Siebel Workflow. Now from here you can do anything what any workflow in Siebel is capable of doing, design your workflow as per the requirement and you are done.

Pretty simple isn't it, but sometimes it tough to debug it if you land-up in some issue here. I was working on the similar kind of requirement where everything seems working fine but the only problem was: "Response Group was not able to pick the email from Siebel email box".

Response Group only job is to just pick the email and create a MIME format file in "<_siebsrvr>/bin/incoming" folder and passes it as an input argument to eMail Response workflow, but since Response Group was not able to pick the email, so I thought of confirming whether it is reaching to the Siebel emailbox or not, so I logged into webmail client and found all emails keep lying there only.

Since I was able to see all the emails and I have also verified the username/password being used for the email profile in Siebel was correct, that means there is some problem with the POP3 setting of the emailbox. So to check it what you can do is :

a) Get the POP3 server name / username / password for the emailbox which is being monitored by reponse group. (You can find it in "Administration - Communications -> Communications Drivers and Profiles" view by querying for the communication drivers as "Internet SMTP/POP3 Server" and check the name in "Profile Parameter overrides" applet).

b) Click Start -> Run -> type "telnet <_servername> pop3", hit enter.

c) In Telnet window, type "USER <_username>" (without quotes)
if you see +OK, that means username is correct.

d) Type "PASS <_password>" (without quotes)
if you see +OK, that means password is correct.

But I recieved an error message saying "Unknown username or bad password"

So this is something we can't control from Siebel, I checked with the Server administration team who handles all the email servers and they fixed the issue for POP3 settings on the email box and tried the above steps again and this time no error.
[Server admin people really likes to resolve the issue when you go to them with the exact root cause ;) ]

One more reason I can think of for such a behaviour when you have not clicked on "Submit Response Group Changes" for the Response group monitoring the emailbox.

.

Error while Siebel Local Database Initialization

Today, I tried intializing the Siebel local database for my Siebel Tools, but everytime getting the error :

Cannot open connection to <_siebelserverhostname>::5047. The Synch Manager component on the server is most likely unavailable.
The connection was refused by server . No component is listening on port 5047.

It seems to happen due to the following reason :

a) Either "Synchronization Manager" component is not running.
b) Siebel Tool's CFG file doesn't contain the correct Sync Port Number.

Alright, lets check it out where the problem lies.

Go to "Administration - Server Management -> Enterprises" and select the Siebel Server on which "Synchronization Manager" component is enabled. And I found that the component was running fine.

Now the next step is to check for the Sync Port Number. As it is already mentioned in the error that there is no listener on port 5047, that means Siebel Tool's CFG is requesting on port 5047 for synchronization. To confirm whether it is correct or not, follow the below steps :

a) Go to "Administration - Server Configuration -> Enterprises -> Component Definitions" view and query for "Synchronization Manager" component.

b) Under the Component Parameters applet, query for "Static Port Number" and check for the value.

To my surprise, I found that the port number was 5048. I think I got the resolution of the problem.

To correct the sync port number in Siebel Tool's CFG, go to "[Local]" section in the CFG and change the value for "DockConnString" parameter with the updated sync port number.

Old Parameter
DockConnString = <_siebelserverhostname>::5047

New Parameter
DockConnString = <_siebelserverhostname>::5048

Again tried initializing the local database and everything worked fine.

Hope this helps !!

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.

Display in Page/Site Map : A Screen View Property

I was investigating a simple issue with my friend yesterday in which the newly created view was not visible under the screen. Sounds very simple right, that you have created a view and we just need to add it under the screen and also under the user's responsibility. Once you do that, the view starts appearing on the UI, but here this was not the case. My friend has also followed the same steps but still not able to see the view on the UI.

To debug this, I also cross-checked that the view was present under the responsibility and properly added under the screen as well. But its a surpise for anyone that still the view is not visible.

Well, let me tell you what I tried and then able to figured it out the root cause.

I created one more copy of the view with a different name and added the new view under the same screen where the original was added. Added it under the user's responsibility as well and compiled the SRF. And as per the expectation, this copied view was visibile on the UI with no issues. That means there was something wrong in the configuration with the original view. I queried for both the view under the Screen in Siebel Tools (Screen View) and compared the various properties and found that "Display in Page" & "Display in Site Map" property was not checked for the original one and this was the reason behind this issue.

Sounds very strange, but not sure how come these both properties was unchecked. And I was able to found out the reason because when I done new record in "Screen View" objects under Screen, to add the copied view under the same screen, "Display in Site" & "Display in Page Map" property get checked by-default. So finally we got the know the reason for the issue.

Sometimes we never know, a simple check box can do wonders in Siebel :)

Check it out !!!!!!

.

Alter Session Parameter for Siebel Query

I got surprised by the result of the execution time of one of the query, on which I was working on, as we were facing a performance issue in Production env while navigating one view.

As per the general process, I checked the Spool and Siebel Object Manager log to check for the query, which one is the culprit. I found the query which was taking around 70 seconds (as per the spool file). So that means I have won half of the battle, rest is to analyse the query and I will be thru. But now when I checked that query how it is behaving at the database, I ran the query on our Siebel Database (Oracle 9i) and got surprised when query returned the result in 200 seconds. This is something strange, how could this happened that if database is returning the result in 200 seconds then getting this result on UI is taking just 70 seconds.

In later investigation, I found that this is due to fact that whenever Siebel run any query on Oracle (Cost Based Optimizer), it sets few session variables for better executions of SQLs. So while verifying the SQL performance of a Siebel Client that is running on Oracle Cost-based optimizer mode, it is important to run the following alter session statements on the database :

For Oracle 9i

alter session set optimizer_mode = first_rows_10

alter session set hash_join_enabled = false

alter session set "_optimizer_sortmerge_join_enabled" = false

alter session set "_optimizer_join_sel_sanity_check" = true

For Oracle 10g

alter session set optimizer_mode = first_rows_10
alter session set "_optimizer_sortmerge_join_enabled" = false
alter session set "_optimizer_join_sel_sanity_check" = true

After setting these parameters, query started behaving the same way as it was behaving on the UI. So, now the only thing we need to check is what is the execution plan is being generated by Oracle, and I found that there was a "Full Scan" for one of the where clause, despite of the fact an index already exists on that column. The Explain Plan query being used was :

Explain Plan for

"Query"

Select * from Plan_table

Now, our investigation was narrowed down to the point that since index was not being used by the query, that's why it is resulting in performance issue. We asked our DBA to regenerate the statistics for that particular index and finally performance issue was resolved.