SQL Spools in Siebel and Trace commands

We have all been there; at some point or other, Siebel starts throwing SQL errors, or the application slows down due to some long running SQL statements.

“An error has occurred executing a Sql statement. Please continue or ask your systems administrator to check your application configuration if the problem persists.(SBL-DBC-00105)”

The best way to figure out what went wrong is to spool out the actual SQL statement (could be query or insert/update or delete) which ran in the application.

You could turn on SQL spooling when the client starts using the /s option.

Or you could increase Log levels

Now both these options enables SQL spooling when the client itself starts; and the resulting file will contain every SQL statement which ran up until the error can be reproduced. Is there any way we can turn SQL logging on the fly, to spool up only the SQLs we want and ignore the rest ?

Turns out, there are script commands TraceOn and TraceOff, which can be used the spool out the SQL statements. Documentation can be found here.

We will have to start SQL tracing using script at the beginning of the even,and then stop it after the spooling is done. I found that these commands can also be used in COM controls.

So I have created a simple SQL instant spooler in Excel macros, which will do this for you, without the need for increasing log levels or writing scripts. I have tested it on Siebel 7.8 and Siebel 8.1 local and dedicated clients, and it works fine. All you need to do is

1) Open the Siebel local or dedicated client (does not work on the thin client)

2) Use the start and stop buttons on the sheet to enable SQL tracing.

3) The SQL which was run in Siebel between start and stop will be opened in notepad

You can download the SQL instant spooler here and try it out. You need to have Siebel installed properly and enable the macros on the excel when it is opened.

Cheerios !!

Fusion Event : Free Online Training

The Horses Mouth

via Antony Reynolds' Blog by antony.reynolds on 4/26/11

Oracle is providing a free online training session on the tools used to create Fusion Applications. 

Learn how Rich Enterprise Applications, powered by Oracle Application Development Framework (Oracle ADF), are developed, assembled and configured using Oracle Fusion Middleware technologies like Oracle JDeveloper, Oracle WebCenter, Oracle BPM Suite, and Oracle SOA Suite. These applications uniquely deliver:

• Desktop Quality, Multi-Channel User Experience
• Integrated Collaboration and Social Computing Services
• Enterprise Services like Content Management, BPM, Security and Entitlement
• Integration with Business Processes and Oracle Applications

Register Now!

 

Siebel 8.1 menu items not opening in IE 8.

Another issue I ran into in IE 8, regarding the Siebel 8.1 Mobile client. This issue was reproduceable on both debug and non-debug modes.

After the mobile client opened up fine, none of the menu items inside the UI framework would open. The menu items at each applet would not drop down, and even the Help->About View would not open up.

At first I thought the pop-up blockers were playing spoilsport again, but the problem persisted even after pop-up blockers were turned off.

Then , a colleague suggested making a change to the client CFG file. In my case, it was scomms.cfg.

1) Open the client CFG file.

2) Find this line :

               EnableFQDN               = FALSE

3) Change it to :

                EnableFQDN               = TRUE

And that fixed the problem !! I don't know what FQDN has to do with menu items, maybe its something wrong with the UI framework.

Siebel 8 debug mode problem with Internet Explorer 8 ?

The last few months have been some of the worse in my career so far. The hectic project schedule and huge amount of "last minute changes" left me very little time to relax. Hopefully, its all going to change soon.

I am working on Siebel 8.1, and had Internet explorer 7 running, which ran without any issues. After an upgrade to IE 8 (thanks to the IT guys), I found that the Siebel debug mode would no longer open. The IE window would open up and throw a  "page not found" error. But the Siebel client would open fine, when started via its own shortcut.

A little experimenting, and I found that inorder for the Siebel 8.1 debug mode to open up in IE, there should be no other instances of IE running. Just kill all other IE instances, and simulating the workflow should be fine.

Not sure if there is another way to fix this.

Differentiating New Record & Update Record

There are cases when a particular data validation or Business  Process invocation should only occur when  a new record is being written down to the database, but not when an existing record is being updated. Or it may be the other way around, but the Siebel developer has to figure out what operation is happening.  I have found many ways these can be achieved, here are my favourite three.

1.       Using a Boolean flag in script.  This is how I first implemented such a scenario.  I was new to Siebel and hadn't yet mastered all the scripting events .  Easiest way out seemed to declare a Boolean flag in the declarations section of the Buscomp server script, and giving it a default value of false. Then in NewRecord  event,  this flag should be turned to true. Finally, in the PreWriteRecord or WriteRecord event, the value of this flag could be checked, and new records can be differentiated fromo exisiting records.

2.       RunTime events. This is a No-Scripting approach to the same problem. In Runtime events, if you select the buscomp events supported, it can be seen that Siebel now provides three events for WriteRecord operation :

·         WriteRecord : Triggered everytime after records are committed.

·         WriteRecordNew : Triggered only when new records are committed.

·         WriteRecordUpdated: Triggered only when existing records are updated.

But these events only occur AFTER the records are committed. They can't be used to do validation/invocation before records are committed.

3.       IsNewRecordPending. This is a new specialised Busines Component Method, meant for EBCs, documented here.  But on trying the command out, I found that it works perfectly well in normal business components as well.  This method can be invoked from any other event in BC level, but it only makes sense to invoke it in the PreWriteRecord section.

var isNewRecord = this.InvokeMethod("IsNewRecordPending");

This way new records can be differentiated from old records before they are committed to the system.

Declarative Alternatives to Using Siebel Scripting (Doc ID 477842.1)

Maybe you already know this, but I there is a document on supportweb which documents many alternatives to scripting. Its Doc ID 477842.1 available here:

 

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=477842.1

 

It also points to another document which discusses the data validation manager . 478298.1

 

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=478298.1

 

 

Validating multiple Datamappers

We were facing these strange error during ADM import on target system:

Source component % does not exist.(SBL-EAI-04063)

This was coming when we tried to import the datamappers inot target system.We follow a continous deveopment model with numerous integrations, and the external IOs for them keep on changing. The problem occurred because the new WSDL imported did not contain some IC levels which were developed and mapped earlier. But the problem was complicated because now we did not know which datamapper to check.

Now there is a very useful button which could help us here. On the Datamap administration view, there is a validate button on the top applet. It checks the structure of the mapped IOs with the ones compiled into the SRF and threw up validation errors. For some time now I have been wondering the functionality behind this button.
I found this in the Siebel log files when the button was clicked:

Begin: Business Service 'EAI Data Transformation Engine' invoke method: 'Validate'

But if you check the definition of this BS in tools, you will not fin the Validate method. But after a little more tweaking, I was able to figure out the input parameters. One thing I found was that if the Datamap is valid, the BS does not return or throw any message, and if there is any validation error, it throws an exception. Hence if multiple datamps need to be validated, the try catch loop must be put inside a loop. I wrote a simple script at client side services which validates multiple datamaps in one go. The search spec can be modified according to your project requirements.


//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function Service_PreInvokeMethod (MethodName, Inputs, Outputs)
{
var oBCDataMap = "";
var sMessage= "";
var iCount = 0;
oBCDataMap = TheApplication().GetBusObject("EAI Data Map").GetBusComp("EAI Object Map");
oBCDataMap.ClearToQuery();
oBCDataMap.SetSearchSpec("Source Object Name","*");
oBCDataMap.ExecuteQuery(ForwardOnly);
var oBSDTE = TheApplication().GetService("EAI Data Transformation Engine");
var psInput = TheApplication().NewPropertySet();
var psOutput = TheApplication().NewPropertySet();
var bIsRecord = oBCDataMap.FirstRecord()
while(bIsRecord)
{
psInput.Reset();
psOutput.Reset();
try
{
psInput.SetProperty("MapName",oBCDataMap.GetFieldValue("Name"));
oBSDTE.InvokeMethod("Validate",psInput,psOutput);
}
catch(e)
{
sMessage = sMessage + oBCDataMap.GetFieldValue("Name") + ":" +e.toString() + "\r";
}
iCount = iCount + 1;
bIsRecord = oBCDataMap.NextRecord();
}

sMessage == "" ? TheApplication().RaiseErrorText(iCount + " Datamaps validated successfully.") : TheApplication().RaiseErrorText(sMessage);
return (CancelOperation);
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


When the code is execute, it validates all Datamaps using the EAI Data Transformation Engine BS's Validate method, and presents the errors in a message box.

You can hit Ctrl-C now to copy this message, and paste this in notepad.

AMSRefData_ServiceCenter:Parent component map 'q' not found.(SBL-EAI-04061)
AMSRefData_Service_Func:Parent component map 'q' not found.(SBL-EAI-04061)

What'ya think ?