How to apply Oracle hint in query via Siebel Configuration?

Our team was facing one performance issue in our application in which navigation to a view was taking hell lot of time. As every Siebel developer does, we also spooled the query which is running in the background and found the culprit query for the issue. SQL query seems to be very simple:

SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY,
T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID,
T1.ACCNT_TYPE_CD, T1.NAME, T1.OU_NUM, T1.DOM_ULT_DUNS_NUM, T1.BU_ID

FROM
SIEBEL.S_ORG_EXT T1

WHERE

(T1.ACCNT_TYPE_CD = 'Establishment')

But why this simple query is taking time?? This seems to be a very simple query which is resulting in performance issue. Ok, no probs, lets try running this query at the database and see how it behaves, so I followed the instructions I mentioned in my earlier post. i.e.

http://siebelmantra.blogspot.com/2009/01/alter-session-parameter-for-siebel.html

After setting the session parameters, I ran the query and it took 105 seconds. hmmm..... then I checked the execution plan and found that it was doing the Full Table scan. If you look at the query, you will easily point out that create a index on ACCNT_TYPE_CD and hopefully you are done, but this is not the case. We already have a index on this field but not sure why it is not being used in the query, then you might say that get the statistics regenerated for it your issue get resolved, but that we have already tried, still not able to figure out what the cause is. Finally, our DBA helped us out here. DBAs are just magician of databases, I don't know what kind of magic stick they just use and sql query itself come to them and tell them what needs to be done here. :)..... anyways, joke apart, our DBA analysed the query and suggested that we should use "ALL_ROWS" in the query, the resultset is coming in just 2 seconds. I was surprised after hearing this because I never came across a scenario where oracle hint ALL_ROWS actually solves the performance issue, and also Siebel itself uses the Optimizer Mode = FIRST_ROWS, for session it establish with the oracle database, before running any sql query. But to my surprise, after putting the hint into the query, it ran fine with just 2 seconds on the database.

Anyways, this is also the first time learning for me, but the challenge is how to put this Oracle Hint "ALL_ROWS" in the query from Siebel configuration? And after lot much of search I found something that is worth sharing here. But before applying this, you need to very careful and get the approval from DBAs or your solution architects, so that it should not impact anywhere else in the application. Now, here is below how you can force a Siebel query to use the Oracle Hint. Identify the business component and add the following User Property there :

Property Name : OracleCBOHint

Property Value : ALL_ROWS

Compile the SRF and check the spool. You will see the difference in the query this time.

Check it out !!!

.

Error in Inbound Integration !!

Generally whenever you create a new Inbound Integration in Siebel, its a recommended practice that you get a new Database login created for it and that you can share with the other system for which you have created this inbound integration. I did the same thing, requested for a new DB login from our DBA and got it created. Before handing over to other party for use, I tried testing this newly created Inbound Integration via SOAPUI (the best ever client I came across for testing integrations). Imported the wsdl and when hit Go, I received the message saying :

The user-context could not be established. Something is wrong with the assigned primary position or login related repository objects. See additional error messages for more information.(SBL-DAT-00546)

and yes my reaction was "Oops, I forget to create a record for the DB user, that I am using for this Inbound Integration, in Administration - User screen". Okayy, let me do that. I created the record under "Employees" view, assigned the necessary position and responsibilities.

Now, lets try again invoking the inbound integation again from SOAPUI, again hit Go, this time I received the different one:

The user ID or password that you entered is incorrect. Please check the spelling
and try again.(SBL-UIF-00272)

hmmm....... I don't think, the username and password I used in the SOAP header is incorrect, so just to confirm it, I went to Oracle client and tried logging into the Siebel Database and I was able to login successfully, no issues. That means the problem is somewhere else.

The best part while working with Siebel is, as soon as you receive any error, just verify the respective logs and you will get the clue. So, the next step towards resolving this issue is just to check the EAI Object Manager log. I went to log folder and found there was an error message in it saying:

SBL-DAT-00587: An error occurred setting the default locale for the session.

This is something to be notice here that the Login credentials I am using is getting problem with creating a new session with Siebel. I think, I know the resolution for this but before actually use that lets double check it. I opened the Siebel thin-client URL and tried login into application instead and again I received the same error that Username/password is not correct. Also in Object Manager log, I found the instance of the same error which I found in EAI Object Manager log.

So, this only happens when SSE_ROLE has not been assigned to the designated user. For creating a session, every Siebel user should have this role assigned otherwise it won't be able to login even. To check which all roles/priviledges are assigned to the user, just login into the database with that user's login credentials and run the following query :

select * from user_role_privs

It will list all the roles for you and keep in mind that SSE_ROLE is required if you want to create a user session on Siebel Server either via Object Manager or EAI Object Manager.

So, I just asked my DBA to do the needful and now I think I am good, and yes I was able to invoke Inbound integration sucessfully. Now its the time when I can share these login credentials along with the WSDL to other party.

.

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.

.