Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Administration and Security => Topic started by: Eag. E on 26 Dec 2018 07:23:46 AM

Title: Any way to monitor SQL in run time?
Post by: Eag. E on 26 Dec 2018 07:23:46 AM
Hi gurus,

Is there any way I can export the SQL excuted when end-user run with my report?
I know that I might get the SQL from Report (AKA ReportStudio), but I want to know the final sql with parameters.
Any Idea.

Thanks
Title: Re: Any way to monitor SQL in run time?
Post by: cognostechie on 26 Dec 2018 01:44:04 PM
Your DBA can get that SQL from the database.
Title: Re: Any way to monitor SQL in run time?
Post by: dougp on 26 Dec 2018 04:35:00 PM


You may also want to log parameters:  (https://www-01.ibm.com/support/docview.wss?uid=swg21706083)

Keep in mind that parameters are not logged until after the report successfully completes.  On failure, you don't get to see what failed.  (If you think this is bad, vote up my RFE (http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=107594).)
Title: Re: Any way to monitor SQL in run time?
Post by: CognosPaul on 26 Dec 2018 05:01:05 PM
Voted, and it will appear on my January RFE list.

In reply to Eag. E: Take a look at my post here: https://www.cognoise.com/index.php/topic,33071.msg108856.html If you follow the instructions I laid out you could create a new page that has the SQL in a list.
Title: Re: Any way to monitor SQL in run time?
Post by: Eag. E on 26 Dec 2018 07:30:06 PM
Quote from: dougp on 26 Dec 2018 04:35:00 PM

  • Open Cognos Administration
  • Status | System
  • Change All Servers to All Dispatchers
  • For http://servername.domain.com:9300/p2pd, Set properties
  • Settings
  • Category: Logging
  • Check the box next to Audit the native query for report service.


You may also want to log parameters:  (https://www-01.ibm.com/support/docview.wss?uid=swg21706083)

  • Open Cognos Administration.
  • Click on System.
  • Change the view from All Servers to Report.
  • On the Report Service, select Set properties.
  • Go to the Settings tab.
  • In Environment/Advanced settings, click on Edit...
  • Add a record for Parameter = RSVP.PARAMETERS.LOG and Value = true

Keep in mind that parameters are not logged until after the report successfully completes.  On failure, you don't get to see what failed.  (If you think this is bad, vote up my RFE (http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=107594).)


Thanks so much, let me try with CA11, and  ^voted.
Title: Re: Any way to monitor SQL in run time?
Post by: Eag. E on 27 Dec 2018 12:59:19 AM
Quote from: dougp on 26 Dec 2018 04:35:00 PM

  • Open Cognos Administration
  • Status | System
  • Change All Servers to All Dispatchers
  • For http://servername.domain.com:9300/p2pd, Set properties
  • Settings
  • Category: Logging
  • Check the box next to Audit the native query for report service.



Hi, for SQL, I did the same step, and set loging level as basic and request, but my COGIPF_NATIVEQUERY is still empty.
Whereas parameter is working fine.
Text only | Text with Images