Using application context in Dynamic SQL

Database server administrators can log and analyze the dynamic SQL workload generated by IBM Cognos software.

As an IBM® Cognos® administrator, you can define a custom string that includes application context that is added as a comment marker within SQL generated by the application. You can use literals, macros, and session variables, such as a user name, server name, qualified report path, and so on, to customize the comment generated by Cognos software.

The Database administrator should check to see if their database client strips comments from statements prior to sending to the server. This option is probably configurable, check with your database client provider.

By using the applicable session variables, you can configure the format of the string for specific tools and products that can extract comments from dynamic SQL. IBM Cognos software includes the comments within any dynamic SQL it generates to a Relational Database Management System (RDBMS) if the vendor supports this functionality.

Use the CQEConfig.xml.sample file included with the product to customize the string specifications. The macro in this file shows the default entries that IBM Cognos software uses for generating the comments. However, you can add other entries as well.

The following example shows kinds of session variables you can specify in the macro in the CQEConfig.xml.sample file:

<configuration company="Cognos" version="0.1" rendition="cer2">
	<component name="CQE">
		<section name="QueryEngine">
			<entry name="GenerateCommentInNativeSQL" value="1"/>
			<!-- ( default(off)=0, on=1) -->
			<entry name="GenerateCommentInCognosSQL" value="1"/>
			<!-- ( default(off)=0, on=1) -->
			<!-- The content of the comments is controlled with two entries, their
defaults are specified in the value attribute -->
			<entry name="NativeCommentMacro" value="# 'NC user=' + $account.defaultName
+ 'report=' + $report + 'start=' + $startTime + 'modelPath='  +
$modelPath + 'reportPath=' + $reportPath + ' queryName=' + $queryName
+ ' REMOTE_ADDR=' + $REMOTE_ADDR + 'HTTP_HOST=' + $HTTP_HOST + 'SERVER_NAME='
+ $SERVER_NAME +' requestID=' + $requestID + 'sessionID=' + $sessionID
#"/>
			<entry name="CognosCommentMacro" value="# 'CC user=' + $account.defaultName
+ 'report=' + $report + 'start=' + $startTime + 'modelPath='  +
$modelPath + 'reportPath=' + $reportPath + ' queryName=' + $queryName
+ ' REMOTE_ADDR=' + $REMOTE_ADDR + 'HTTP_HOST=' + $HTTP_HOST + 'SERVER_NAME='
+ $SERVER_NAME +' requestID=' + $requestID + 'sessionID=' + $sessionID
#"/>
		</section>
	</component>
</configuration>

At run time, the macro used in the previous example would add the following comment to the automatically-generated SQL, or native SQL:

/* CC user=Anonymous report=REPORT1 
start=2008-08-28T01:59:35.403Z modelPath=/content/package
[@name='New Package']/model[@name='model']
reportPath=/content/package[@name='New Package']/report[@name='REPORT1']
queryName=Query1 REMOTE_ADDR=127.0.0.1 HTTP_HOST=localhost 
SERVER_NAME=localhost
requestID=wq2lshM9jGhqdMj9h92MqlqvdMlhyMlGq9lyG9sq 
sessionID=010:0d159165-745a-11dd-ac9f-b741aeca4631:2789499633
*/
select distinct 
       ALL_TIME.CALENDAR_WEEKDAY  as  CALENDAR_WEEKDAY
 from 
       EAPPS..EAPPS.ALL_TIME ALL_TIME

Not all information in the generated comment is meaningful in all situations. The request and session ID information provides a link to the auditing facility, perfQFS performance information, and other traces in IBM Cognos. However, the name of a query in a report and the report itself may be meaningless, for example, when a user is performing an ad-hoc query or analysis as opposed to running a saved query, analysis or report.

By default, an anonymous user cannot see all session variables in the generated comments.

Adding application context for Dynamic Query Mode

To use comments in SQL for dynamic query mode you can configure the xqe.config.xml file, located in c10_location/configuration.

You edit the following elements in the <queryPlanning> element.

<generateCommentsInNativeSQL enabled="true"/>
<NativeCommentMacro value="#'user=' + $account.defaultName + ' reportPath='
 + $reportPath +' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR 
 + ' SERVER_NAME=' + $SERVER_NAME + ' requestID=' + $requestID#"/>