Set up a SQL Trace?

[kado]

Cognoise,

We have some extremely slow prompts from one of our packages and I would like to set up a SQL trace to see what is going on? [by the way we are on 8.4 sourcing Oracle] I went into 'IBM Congos Administration' and turned on Logging for 'Audit the native query for batch report service' and then set a 'Trace' for 'Audit logging level for the dispatcher' and 'Audit logging level for report data service' and 'Audit logging level for report service'. (I also noticed a check box for 'Audit the native query for report service' but left it blank).

I ran my slow report then tried to look at the 'cogserver.log' file but nothing had been updated at all. Am I looking at the wrong log file? Should I check the 'native query for report service' instead? The documentation was a little unclear here.

any thoughts?

thanks,
mcaton

[MFGF]

If you tried to run the report live in your browser, it will have used the report service not the batch report service.  Either enable auditing for the report service, or else use the "run with options" link and select the option to save the report so it runs via the batch report service.

MF.

[prikala]

You probably want native query auditing.

As you are using oracle, you can also use oracle sql_trace if you want to find out what happens in the database provided you are familiar with oracle's trace settings and tkprof utility.

You can set oracle trace using datasource connection "Open session commands". To enable trace use something like this:
<commandBlock>
<commands>
<sqlCommand>
<sql>alter session set sql_trace=true tracefile_identifier=whatever timed_statistics=true</sql>
</sqlCommand>
</commands>
</commandBlock>

Just remember, this will trace everything that goes through this datasource connection. Your dba will not be happy if you use this on a production environment.