So, not sure if this belongs in Administration, Framework Manager, or Reporting. Since my solution takes place in Framework Manager...
To help maintain server performance, the database administrator needs the ability to identify which user is running a query against the database server.
I'm using Compatible Query Mode.
I want to use Dynamic Query Mode. DQM appears to cause Cognos to write cleaner-looking SQL. It should also enable my users to use multiple packages in a single report.
The need: How can I identify the user
before the query causes other problems on the database server?
The problem:I have enabled logging native SQL. Cognos doesn't log the native SQL to the Audit database until
after the query completes successfully and the database server returns the data to Cognos. This means two things:
- If a query is runaway (long-running, wrong, etc.) there is no way to know who is running it while it is running. I have had queries run for 22 hours before the DBA killed them because they were blocking some important process. (Yes, I have set the timeout for the report service and the query service. Cognos frequently ignores this setting and allows a report to run indefinitely.)
- If the report fails, the native SQL is not stored, leaving nothing to troubleshoot.
I am not using pass-through permissions on data source connections. The database administrator doesn't want to maintain security settings for direct access to the databases for each of 7000 employees. I manage the permissions for packages, data sources, and reports in Cognos.
To help my database administrator identify which user is running the query through Cognos, I'm beginning to add this filter to all query subjects in all models
#sq($account.personalInfo.userName)# = #sq($account.personalInfo.userName)#This seemed like a good solution, since it adds the user name...
WHERE 'username' = 'username'...to the query that actually hits the database server. And it is fast because the database engine ignores it because it is comparing two, matching, literal strings.
...until I decided to employ DQM.
With DQM, it looks like Cognos is stripping out the part of the query that compares two, matching, literal strings.
So, with DQM, I'm stuck again.
How did you work around this shortcoming in Cognos's logging features?