Find bad reports or packages with SQL

[therese1]

Hi we have sql that is taking up most of the memory, we can find the sql but do not know how to identify the owner of it. Is there a quick way in cognos to identify bad sql reports or packages and owners?

[cognostechie]

The idea is to pass the user-id (of the person running the report) in the SQL. If the package is created using Framework Manager then you can add a filter in every query subject in the Business Layer and insert this:

#sq($account.personalInfo.userName)#   = #sq($account.personalInfo.userName)#

This will add a line in the where clause of the SQL with the user-id used to login into Cognos.

 

[dougp]

Query the Audit database for reports runs that are ongoing.  Look for status = blank.  You may also want to look for duration (execution time?) > x minutes, but remember the value is in milliseconds.  Also, remember that localtimestamp = end time, so end time - duration = start time.  Except that localtimestamp = start time if it's still running.  You should be able to identify the report, user, etc.  Then open the report and inspect the SQL it generates.

Most of the memory...

Where?  On the database server?  On the Cognos server?  On the client?


CognosTechie:  Nice trick.  My DBAs will love it.

[the6campbells]

Comments in SQL feature been there since around 8.4

No need to go in and add fake predicates

Enable "comments in SQL" on query service in Admin portal and default template of text added to SQL

If the RDBMS vendors client s/w does not strip them out before xmitting to the server the server side "logs" will have context.

Hence, you can derive (who - if folks authenticate vs anon) and what ( report name and bi-query name in artifact) etc.

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_cra.doc/c_usedatabasecommands.html
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_cra.doc/c_asg_appcntxt_dynSQL.html