COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Framework Manager => Topic started by: dougp on 05 Aug 2019 12:21:24 PM

Title: Helping the DBA identify the user
Post by: dougp on 05 Aug 2019 12:21:24 PM
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:

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?