query execution on database side

[goxy]

Hi Cognos experts,

I have hierarchical model created in FM. Some complex report takes too much time (over few hours). When I cancel report creation everything looks fine from Cognos side, but process is still running on database side (taking CPU and RAM).

Is there any setting that can cancel report and process on database in same time?

Governor setting didn't help.

Thanx in advance

Goxy

[AussiePete2011]

Hi there

No as the process on the Database only gets a cancel message which requires a rollback on the database.
Its a bit more complex than just pressing the cancel button.

Depending on what you are doing, you may need to look at the model and see if you can push the query back to the database as a view or materialized view and then create indexes.

Have a look at recommended practices in IBM Cognos Proven Practices
https://www.ibm.com/developerworks/data/library/cognos/cognosprovenpractices.html#modeling

Cheers
Peter B

[blom0344]

Depending on the RDBMS you may or may not use database governors to kill runaway queries. However, this is complicated stuff at best.

The main issue is the query execution time. Even with a proper Cognos model you may still generate a very inefficient query path. A standard approach is to start a database trace, capture the SQL and analyse this , begging your favorite DBA for some expertise.
Efficient SQL should be about seconds to minutes of CPU time, not hours..

For SQL server I use the following nice little tool :

http://www.datamanipulation.net/SQLQueryStress/

that will give you some relative measures to work with

[goxy]

Thanks guys.

Strange situation (to me) is that I even try to restart whole C8 service and process was still running on DB side. It was something like, Cognos sent query to DB and forget it...

Is there any detailed document describing what is really happening in background when you start report?

Regards

Goxy

[blom0344]

Onces cognos sends the query to the database the DB governor will effectively take over. Cognos simply waits for data to be returned. Restarting Cognos does not effect in any way what is happening on the database. As said , running a trace when starting the report will yield information on what is going on database side.

Cognos itself does use governor's settings to restrict the execution time of reports, but it does not govern database processes..

[goxy]

Thank you...

Regards

Gox