Add Application Context to Dynamic SQL
Database server administrators can log and analyze the dynamic SQL workload generated by IBM Cognos 8.
As an IBM Cognos 8 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 8.
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 8 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 8 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 above 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 8.4. 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.
Steps for Using the CQEConfig.xml.sample File
Copy the c8_location/configuration/CQEConfig.xml.sample file to c8_location/bin and rename it CQEConfig.xml.
Note: If the CQEConfig.xml was used for other purposes, for example to disable session caching, it may already exist in the c8_location/bin directory. In this situation, use the existing CQEConfig.xml file to perform the remaining steps.
Open the c8_location/bin/CQEConfig.xml in an editor.
Ensure that your editor supports saving files in UTF-8 format.
Locate and uncomment the lines of code that begin with:
entry name="GenerateCommentInNativeSQL"...
entry name="GenerateCommentInCognosSQL"...
entry name="NativeCommentMacro"...
entry name="CognosCommentMacro"...
If you want, you can modify NativeCommentMacro and CognosCommentMacro by specifying the required parameter values and deleting the parameters that you do not need.
If you leave a parameter value empty, the parameter will not appear in the generated comment.
Save the CQEConfig.xml file.
Restart the IBM Cognos 8 service.
As an IBM Cognos 8 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 8.
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 8 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 8 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 above 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 8.4. 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.
Steps for Using the CQEConfig.xml.sample File
Copy the c8_location/configuration/CQEConfig.xml.sample file to c8_location/bin and rename it CQEConfig.xml.
Note: If the CQEConfig.xml was used for other purposes, for example to disable session caching, it may already exist in the c8_location/bin directory. In this situation, use the existing CQEConfig.xml file to perform the remaining steps.
Open the c8_location/bin/CQEConfig.xml in an editor.
Ensure that your editor supports saving files in UTF-8 format.
Locate and uncomment the lines of code that begin with:
entry name="GenerateCommentInNativeSQL"...
entry name="GenerateCommentInCognosSQL"...
entry name="NativeCommentMacro"...
entry name="CognosCommentMacro"...
If you want, you can modify NativeCommentMacro and CognosCommentMacro by specifying the required parameter values and deleting the parameters that you do not need.
If you leave a parameter value empty, the parameter will not appear in the generated comment.
Save the CQEConfig.xml file.
Restart the IBM Cognos 8 service.
Comments
Post a Comment