Saturday, April 30, 2011

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 !!

2 comments:

  1. When I first downloaded this was working, after changing the Browser security settings in IE 9 the script does not capture the SQL -
    I am Siebel 8.1.1.3 SIA(21219) IE 9.0.8112

    04/29/14,12:21:46,START,8.1.1.3 [21219] LANG_INDEPENDENT,SRAI,8328,1600
    04/29/14,12:21:47,COMMENT,start of tracing!
    04/29/14,12:22:05,STOP

    ReplyDelete
  2. Brilliant blog I visit this blog it's incredibly awesome. Curiously, in this blog content formed doubtlessly and sensible. The substance of information is helpful.
    Workday HCM Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete